CODEWITHSUNDEEP
jsonpowershellcsv
Tessy
Tessy

Reputation: 35

Convert Json with columns and rows to csv using powershell

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

Upvotes: 0

Views: 2093

Answers (3)

Steven
Steven

Reputation: 7057

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.

Upvotes: 0

JosefZ
JosefZ

Reputation: 30113

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.

Upvotes: 1

Steven
Steven

Reputation: 7057

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.

Upvotes: 0

Related Questions