PowerShell System.Object[] array Export-CSV

Question

$path = "\\path\to\folder"
$ts = (Get-Date -format yyyyMMdd_HHmmss)
Invoke-WebRequest -Uri "https://fsrm.experiant.ca/api/v1/combined" -UseBasicParsing -OutFile ($path + $ts + ".json")
$json = Get-Content -Raw ($path + $ts + ".json")
$x = $json | ConvertFrom-Json
$x | Select filters | Export-Csv -NoTypeInformation -Path ($path + $ts + ".csv")

The above gives me an output of:

filters
System.Object[]

Changing the last line of the script to:

$x | Select-Object -ExpandProperty filters | Export-Csv -NoTypeInformation -Path ($path + $ts + ".csv")

Gives me something along the lines of the below, which is the length count of each item:

Length
6
6
6
6
7
...

I don't want the length, I want the actual values, as you can see from the .json linke it's invoking a web request from it should be a list of file types; to import into FSRM to fight ransomware.

I see nothing in the Select-Object page about choosing what you can return, so what am I missing?

https://technet.microsoft.com/en-us/library/hh849895.aspx

Select-Object @{Name="Filters";Expression={$_.Filters -join ';'}} — Mathias R. Jessen
– Mathias R. Jessen, Commented Nov 1, 2016 at 16:26
@MathiasR.Jessen I have unwanted quotation marks in the text when viewing in notepad, eg: "Filters"\n"*.ext\n*.ext2\n*.ext3" (I replied the -join character with ``n`) — codaamok
– codaamok, Commented Nov 1, 2016 at 19:23
@adampski what does it mean for it to be in CSV format if it's just one list of file extensions and there are no other columns? What do you mean "unwanted quotation marks" - the quotes are part of the CSV format, not part of the data, that's like saying "I want my image as a JPEG ... but now it's full of unwanted JPEG compression information". If you just want one per line $x.filters | set-content whatever.csv — TessellatingHeckler
– TessellatingHeckler, Commented Nov 1, 2016 at 19:49
@TessellatingHeckler if you pay close attention to the quotation marks in my comment, you'll understand what I mean by undesired. — codaamok
– codaamok, Commented Nov 1, 2016 at 22:13

TessellatingHeckler · Accepted Answer · 2016-11-01 23:13:30Z

Invoke-RestMethod includes converting the response from JSON, where Invoke-WebRequest does not, so it's a useful cmdlet to use here.

.filters in that site's JSON output is a list of strings. You can put them in a file, one per line, with:

$filters = (Invoke-RestMethod -Uri https://fsrm.experiant.ca/api/v1/combined).filters
$filters | Set-Content "d:\path\$(get-date -Format u).csv"

If it does need a column header line:

$Path = "d:\path\$(get-date -Format u).csv"

$filters = (Invoke-RestMethod -Uri https://fsrm.experiant.ca/api/v1/combined).filters
'Filter' | Set-Content $Path
$filters | Add-Content $Path

And if it does need to go through CSV, which will quote each line and have a header line, then:

$path = "d:\temp"
$ts = (Get-Date -format yyyyMMdd_HHmmss)

$json = Invoke-RestMethod -Uri "https://fsrm.experiant.ca/api/v1/combined"
$json.filters | select @{N="Value";E={$_}} | Export-Csv -NoTypeInformation -Path "$path\$ts.csv"

There's no need to |%{$_.Filters} because there's only one object converted from JSON and filters is a property of it, so access it once:

{
    filters: [
       ]
}

And there's no need to E={$_.ToString()} because the filter values are already strings.

The way Import-CSV and Export-CSV work is:

A row is an object
Columns map to properties of objects
The CSV maps to an array of objects

Select allows you to start with some objects coming in through the pipeline, and send them on down the pipeline with only some properties of your choosing.

Calculated properties allow you to add in new properties on the fly, to the objects coming through the pipeline.

In this case:

$json.filters |

what's coming through is strings.

$json.filters | select @{Name="Value";Expression={$_}}

is taking one string

"*.vbs"

and turning it into a PSCustomObject with one property:

@{Value="*.vbs"}

That now maps to a CSV row, with one column ('Value') and the column content *.vbs.

The pipeline becomes

@{Value="*.vbs"}
@{Value="*.exe"}
@{Value="*.js"}

and Export-CSV processes them into a CSV

Esperento57 · Accepted Answer · 2016-11-01 21:16:35Z

1

try to modify like this

  $path = "c:\temp\"
  $ts = (Get-Date -format yyyyMMdd_HHmmss)
  Invoke-WebRequest -Uri "https://fsrm.experiant.ca/api/v1/combined" -UseBasicParsing -OutFile ($path + $ts + ".json")
  $json = Get-Content -Raw ($path + $ts + ".json")
  $x = $json | ConvertFrom-Json
  $x |%{ $_.filters}  | select @{ N="Value";E={$_.ToString()}} | Export-Csv -Delimiter ";" -NoTypeInformation -Path ($path + $ts + ".csv")

answered Nov 1, 2016 at 21:16

Esperento57

17.6k6 gold badges42 silver badges47 bronze badges

2 Comments

codaamok Over a year ago

That gave me my desired output, thank you. If I were to do further reading to understand everything about @{ N="Value";E={$_.ToString()}}, what keywords relevant to PowerShell do I need to Google?

TessellatingHeckler Over a year ago

"Calculated Properties"

Collectives™ on Stack Overflow

PowerShell System.Object[] array Export-CSV

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related