ConvertFrom-Json to CSV

PowerShell Help
1

Hello having an issue trying to convert a json file to csv. Any help would be appreciated.

Thanks in advance

I have a json file in the following format:

{
        "public_cluster": {
                "columns": ["ID", "NAME", "CLUSTER_NAME", "V_NAME", "DATACENTER_NAME", "DATE_PULL", "TIME_PULL"],
                "records": [
                        ["ID12345", "cluser1", "pretty_cluster", "server1", "city123", "06\/14\/2017", "15:34:15"],
                        ["ID12345678", "cluster2", "cluser_big", "server4", "cit3", "06\/14\/2017", "12:58:32"]
                ]
        }
}

I’ve tried various things like

(get-content -path c:\temp\file.json -Raw | convertfrom-json) | convertto-csv -notypeinformation | select-object | set-content c:\temp\out.csv

also tried looking at the rows
$json = convertfrom-json (get-content c:\temp\file.json -raw)
$json.DataFeed.Rows | select *

2

It would help to understand what the “issue” is for you.

In your first example, I don’t understand why you’re using Select-Object. It would appear to be selecting nothing. I’m perhaps unclear why you didn’t just use Export-CSV? “Get-Content | ConvertFrom-JSON | Export-CSV” ?

In your second example, I don’t understand why DataFeed is used. I don’t see that element in your JSON at all.

But more broadly, I’m not sure you can do what I think you’re trying to do. Each line of a CSV is a single object, and its properties are the CSV columns. Your JSON, however, isn’t lending itself to that. You’re creating one object with a public_cluster property, which has a columns property, which is an array of values. This JSON is not going to be interpreted in a way that can be converted to a CSV with those columns values as the CSV column names. There isn’t even any straightforward way to rejigger than JSON into something that looks like a CSV. You’d basically have to ingest the JSON and then do a ton of manual programming to format the data into the individual, row-by-row objects that would be able to be a CSV.

3

You can probably guess, but I’m not getting output I would expect. Just would see System.Object in the outfile. I’m essentially just trying to turn the json into a flat file, record by record so it could be imported elsewhere. High level I just want to get the records area of the file into a csv.

“ID12345”, “cluser1”, “pretty_cluster”, “server1”, “city123”, “06/14/2017”, “15:34:15”
“ID12345678”, “cluster2”, “cluser_big”, “server4”, “cit3”, “06/14/2017”, “12:58:32”

4

Your JSON is producing a single object; because it’s a generic object, its text rendering is just “System.Object,” which is why you get that in your file.

There’s no easy conversion here due to the way the JSON is formatted. You’re going to need to write code to enumerate the columns, enumerate each row, and produce an output object for each row.

[pscustomobject]@{'Column1'='Value1';'Column2'='Value2'}

That’s essentially what you need to output for each row of the CSV. Send all that to Export-CSV and it’ll do what you want. But there’s no simple command to go from your JSON to that state - it’ll be manually coding it up.

5

Hi,
Here is one of the ways to do what you want:

$json.public_cluster.records | ForEach-Object {
    $_ -join ',' | ConvertFrom-Csv -Header $output.public_cluster.Columns | 
        Export-Csv -Path C:\test.csv -NoTypeInformation
}
6

Thanks Dirk that did it, just had to add the --Append on the Export-CSv so it would do all the records vs just one.

Code that did the trick for me.

$file = "C:\temp\public_cluster.json"
$json = ConvertFrom-JSON (Get-Content $file -Raw)
$json.public_cluster.records | ForEach-Object {
   $_ -join ',' | ConvertFrom-CSV -Header $json.public_cluster.columns | Export-CSV -path C:\temp\public_cluster.csv -NoTypeInformation -Append
}