Convert Json with columns and rows to csv using powershell

Question

Please help me to convert my below json file to csv.

{
    "count":  12,
    "name":  "Daily Ticket",
    "columnNames":  [
                        "User",
                        "Channel",
                        "Date",
                        "# of Closed Incidents",
                        "Open",
                        "Response",
                        "Remark",
                        "Closed"
                    ],
    "rows":  [
                    [
                     "abc",
                     "Service Web",
                     "\u00272020-06-13 00:00:00\u0027",
                     "1",
                     "0",
                     "0",
                     "0",
                     "1"
                 ],
                 [
                     "xyz",
                     "Email",
                     "\u00272020-06-13 00:00:00\u0027",
                     "21",
                     "1",
                     "0",
                     "10",
                     "7"
                 ]
             ]
}

I want column names as header and rows as rows separated with comma in csv.

The expected output is like below:

User,Channel,Date,# of Closed Incidents,Open,Response,Remark,Closed
abc,Service Web,\u00272020-06-13 00:00:00\u0027,1,0,0,0,1
xyz,Email,\u00272020-06-13 00:00:00\u0027,1,0,0,0,1

Instead of ConvertTo-JSON, use ConvertTo-CSV and you get CSV report directly. — sushanth
– sushanth, Commented Jun 14, 2020 at 14:32

JosefZ · Accepted Answer · 2020-06-14 18:44:16Z

1

I'd offer the simplest approach I know:

$jsonText = @'
{"count":12,"name":"Daily Ticket","columnNames":["User","Channel","Date","# of Closed Incidents","Open","Response","Remark","Closed"],"rows":[["abc","Service Web","\u00272020-06-13 00:00:00\u0027","1","0","0","0","1"],["xyz","Email","\u00272020-06-13 00:00:00\u0027","21","1","0","10","7"]]}
'@
$json = $jsonText | ConvertFrom-Json

$jsonCsvLines = [System.Collections.ArrayList]::new()
[void]$jsonCsvLines.Add( $json.columnNames -join ',')
foreach ( $jsonCsvRow in $json.rows ) {
    [void]$jsonCsvLines.Add( $jsonCsvRow -join ',')
}

$jsonCsvLines

$jsonCsv = $jsonCsvLines |
    ConvertFrom-Csv -Delimiter ',' |
    ConvertTo-Csv -Delimiter ',' -NoTypeInformation

$jsonCsvNoQuotes = $jsonCsv -replace [regex]::Escape('"')

Here

the here-string $jsonText is a compressed version of your example;
the $jsonCsvLines is simple collection (no CSV);
the $jsonCsv is a genuine csv where all fields are enclosed in double quotes, while the $jsonCsvNoQuotes is a csv where no field is enclosed in double quotes.

answered Jun 14, 2020 at 18:44

JosefZ

30.5k6 gold badges52 silver badges96 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tessy Over a year ago

Thank you for the solution. Coming back again to this thread, how can I select only few columns from the same json and add it to csv? For e.g., I need user,channel,response in the csv and not the other columns. How can I achieve this?

JosefZ Over a year ago

Please ask another question instead. Don't forget to include a minimal reproducible example.

Tessy Over a year ago

Done. It is added here.stackoverflow.com/questions/64203089/…

Steven · Accepted Answer · 2020-06-14 18:07:08Z

0

So far as I can tell (and I'm happy to be corrected) the format of the resulting PSCustomObjects from ConvertTo-JSON isn't suitable for direct consumption by ConvertTo-CSV

You need to relate the elements in row array with the column names. Create an array of objects with the correct property names and values. The way I solved this was to use the array index to associate each row array element with a column name:

$JSON = Get-Content 'C:\Temp\sample.json' | ConvertFrom-Json 

    $Rows = 
    ForEach($Row in $JSON.Rows )
    {
        $TmpHash = [Ordered]@{}
        For($i = 0; $i -lt $Row.Length; ++$i )
        {
            $TmpHash.Add( $JSON.columnNames[$i], $Row[$i] )
        }
        [PSCustomObject]$TmpHash
    }

    $Rows | ConvertTo-Csv -NoTypeInformation

Obviously change the file name or whatnot.

On my workstation this results are like:

"User","Channel","Date","# of Closed Incidents","Open","Response","Remark","Closed"
"abc","Service Web","'2020-06-13 00:00:00'","1","0","0","0","1"
"xyz","Email","'2020-06-13 00:00:00'","21","1","0","10","7"

There are definitely different code patterns that can be employed here, but the theme should work.

One important difference is you had a unicode character for apostrophe \u0027 in the source JSON in my output that's properly interpreted. Just pointing out because it's one thing that differs from your sample.

I think this is pretty close to what you needed. Let me know if anything. Thanks.

edited Jun 14, 2020 at 18:07

answered Jun 14, 2020 at 18:01

Steven

7,1271 gold badge17 silver badges16 bronze badges

1 Comment

Tessy Over a year ago

Thank you for the solution. Coming back again to this thread, how can I select only few columns from the same json and add it to csv? For e.g., I need user,channel,response in the csv and not the other columns. How can I achieve this?

Steven · Accepted Answer · 2020-06-15 15:10:39Z

Sorry to keep this going, but I'm intrigued by JosefZ's answer . This is not to override or critique, it's just for conversation's sake.

Considering ConvertFrom-Json returns PSCustomObjects my first answer went directly to using the same as input for ConvertTo-Csv. Even though creating Csv strings is common in the field it didn't occur to me at the time. I also didn't notice your sample output was unquoted, apologies for that.

At any rate here's my interpretation of JosefZ's answer

Note: In any of these samples -join would work just as well. I just invoking static methods; however I don't know advantages / disadvantages between -join & [String]::Join().

$JSON = Get-Content 'C:\Temp\sample.json' | ConvertFrom-Json 

$Lines = .{
    [String]::Join( ',', $JSON.columnNames )
    $JSON.rows | ForEach-Object{ [String]::Join( ',', $_ ) }        
}

# $Lines is already in unquoted CSV format.  If you want to quote it
$Lines | ConvertFrom-Csv | ConvertTo-Csv -NoTypeInformation

If you only need the quoted or unquoted you can drop the $Lines assignment and pipe all the way through.

Quoted:

$JSON = Get-Content 'C:\Temp\sample.json' | ConvertFrom-Json 

.{
    [String]::Join( ',', $JSON.columnNames )
    $JSON.rows | ForEach-Object{ [String]::Join( ',', $_ ) }
} | ConvertFrom-Csv | ConvertTo-Csv -NoTypeInformation

Un-Quoted:

$JSON = Get-Content 'C:\Temp\sample.json' | ConvertFrom-Json 

.{
    [String]::Join( ',', $JSON.columnNames )
    $JSON.rows | ForEach-Object{ [String]::Join( ',', $_ ) }
}

This approach is a more concise as it can be slimmed to only a few lines. Concise doesn't always mean better or more readable, so again this isn't to override any other approach.

Of course, I don't know what you intend to do with the data after it's properly formatted. If you need to write to a file a simple | Out-File... can be added to the end of any of the above.

Thank you for the solution. Coming back again to this thread, how can I select only few columns from the same json and add it to csv? For e.g., I need user,channel,response in the csv and not the other columns. How can I achieve this?

Collectives™ on Stack Overflow

Convert Json with columns and rows to csv using powershell

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related