Convert a JSON representation of CSV data to actual CSV data

Question

This self-answered question is about transforming a JSON representation of CSV data into actual CSV data.^[1]

The following JSON contains separate properties that describe the headers (column names) (columns) and arrays of corresponding row values (rows), respectively:

{
  "columns": [
      {
          "name": "ColumnName1",
          "type": "Number"
      },
      {
          "name": "ColumnName2",
          "type": "String"
      },
      {
          "name": "ColumnName3",
          "type": "String"
      }
  ],
  "rows":    [
      [
          11111,
          "ResourceType1",
          "String1"
      ],
      [
          22222,
          "ResourceType2",
          "String2"
      ],
      [
          33333,
          "ResourceType3",
          "String3"
      ]
  ]
}

How can I convert this JSON input to the CSV data it represents?

^{[1] The question duplicates this closed question, which was closed presumably due to lack of effort, even though what it asks for is reasonably well-defined.}

user_45375 · Answer

Note that CSV files have no concept of data types - all values are strings, so the data-type information (from the .column.type properties) is lost, unless you choose to incorporate it in some way as a convention that the consumer of the CSV would have to be aware of (the code below does not do that).

Assume that the JSON in the question is saved in file file.json, which can be parsed into a ([pscustomobject]) object graph with ConvertFrom-Json, via reading the file as text with Get-Content:

# Convert the JSON text into a [pscustomobject] object graph.
$fromJson = ConvertFrom-Json (Get-Content -Raw file.json)

# Process the array of column names and the arrays of row values by
# enclosing the array elements in "..." and joining them with ","
(, $fromJson.Columns.Name + $fromJson.Rows).ForEach({
  $_.ForEach({ '"{0}"' -f ($_ -replace '"', '""') }) -join ','
})

Note that the above encloses the column names and values in "..." so as to also support names and values with embedded , characters; additionally, any embedded " characters are properly escaped by doubling them.

If you know that the input data neither contains values with embedded , nor ", you can simply omit the inner .ForEach() array method call above, which will result in unquoted values.

The above outputs:

"ColumnName1","ColumnName2","ColumnName3"
"11111","ResourceType1","String1"
"22222","ResourceType2","String2"
"33333","ResourceType3","String3"

To convert the above in-memory to ([pscustomobject]) objects representing the CSV data, use ConvertFrom-Csv (... represents the command above):
```
... | ConvertFrom-Csv
```
To save the above to a CSV file, use Set-Content:
```
... | Set-Content -Encoding utf8 out.csv
```

Convert a JSON representation of CSV data to actual CSV data

1 Answers