CODEWITHSUNDEEP
jsonpowershellcsv
swhat
swhat

Reputation: 37

Windows PowerShell JSON to CSV "System.Object[]" issues

I've been struggling for 3 days trying to convert a JSON file to a CSV. Unfortunately, I am not too familiar with powershell and any help would be appreciated. Here is the JSON String:

{"inputFile": [["Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"], ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"], ["K", "L", "M", "N", "O", "P", "Q", "R", "S", "T"]]}

Command lines that I've tried:

ConvertFrom-JSON (Get-Content 'path.json' -Raw) |
    ConvertTo-Csv -NoTypeInformation |
    Select-Object -Skip 1 |
    Set-Content 'path.csv'

Get-Content 'path.json' -Raw |
    ConvertFrom-Json |
    Select -Expand inputFile |
    ForEach {$_.inputFile = $_.inputFile -join ' '$_} |
    Export-Csv 'path.csv' -NoTypeInformation

Nothing...

I am hoping to get a CSV file with:

Column1, Column2, Column3, etc.
A,B,C,etc.

What combination of commands can I use to do this?

Upvotes: 1

Views: 2756

Answers (2)

Jacob Colvin
Jacob Colvin

Reputation: 2835

So this is about as convoluted as it gets, and I hope someone else answers. But this worked for me. Without changing the data in your JSON I'm not sure what else to tell you.

$y = ''
$x = '{"inputFile": [["Column1", "Column2", "Column3", "Column4", "Column5", "Column6", "Column7", "Column8", "Column9", "Column10"], ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J"], ["K", "L", "M", "N", "O", "P", "Q", "R", "S", "T"]]}'
($x | ConvertFrom-JSON).inputfile | % {$_ | %{$y += ($_ + "`t")}; $y+="`n"}
$y | out-file x.csv

It's basically just text manipulation. I created it as tab delimited since that was default with excel, but you can change it to anything else.

Upvotes: -1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200503

Export-Csv expects a list of objects as input. The properties of the objects (identified by the properties of the first object) are exported as the columns of the CSV. For the cmdlet to work the way you expect your input data would have to look like this:

{
    "inputFile": [
        {
            "Column1": "A",
            "Column2": "B",
            ...
        },
        {
            "Column1": "K",
            "Column2": "L",
            ...
        }
    ]
}

In your case you have each row in a separate array, so it's easier to just join those arrays and then write the output as a text file:

Get-Content 'path.json' -Raw |
    ConvertFrom-Json |
    Select-Object -Expand inputFile |
    ForEach-Object { $_ -join ',' } |
    Set-Content 'path.csv'

Upvotes: 3

Related Questions