Convert to csv from json in powershell

Question

this is my csv:

"name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0" | ConvertFrom-Csv | ConvertTo-Json

Gives me:

[
{
    "name":  "Play",
    "data":  "http://{gho}.domain.com/"
},
{
    "name":  "BDomain",
    "data":  "domain.com"
},
{
    "name":  "Charts",
    "data":  "2"
},
{
    "name":  "Compress",
    "data":  "0"
},
{
    "name":  "CompressJ",
    "data":  "0"
}
]

I would now like to get that csv back from my json, however the chains of | ConvertFrom-Json | ConvertTo-CSV are not working, and I am curious why not?

this is a duplicate refer to : https://stackoverflow.com/questions/43594860/convert-json-to-csv-using-powershell — tawfikboujeh, Sep 22 '20 at 16:41
the file in that question doesnt exist so i was uncertain of the format, but will look into it, thank you — Papi Abi, Sep 22 '20 at 16:45
well, when trying to get the 'results' property ps throws that results cannot be found, if doing just standard |convertfrom-json | convertto-csv it returns some metadata formatted as csv — Papi Abi, Sep 22 '20 at 16:48
@tawfikboujeh, the issue here is the pre-v7 non-enumerating behavior of `ConvertFrom-Json`, which is - incidentally - _bypassed_ in the solution to the linked question, where accessing a _property_ implicitly causes enumeration. — mklement0, Sep 24 '20 at 22:36

Steven · Answer 1 · 2020-09-25T15:34:47.060

The output of ConvertFrom-Json is single array being passed down the pipeline as such. This is a consequence of how ConvertFrom-Json is writing it's output. It's probably collecting everything and outputting the array in one Write-Output command (implicit or otherwise) instead of streaming each object down the pipeline as they are created. It's probably a consequence of how the cmdlet is written and may have been necessary.

Your current code is likely generating a csv like:

"Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"
"5","5","5","1","System.Object[]","False","True","False"

These are the properties of the array not the objects in it. One way to get around it just park the data in a variable:

$Json = "name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0" | 
ConvertFrom-Csv | 
ConvertTo-Json

# Now you can convert back to csv without issue:
$Json = $Json | ConvertFrom-Json
$Json | ConvertTo-Csv -NoTypeInformation

Note: if you send $Json directly like $Json | ConvertFrom-Json | ConvertTo-Csv... you may have the same issue.

Iterating over the Json objects also seems to work:

$Json = "name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0" | 
ConvertFrom-Csv | 
ConvertTo-Json

$Json | 
ConvertFrom-Json |
ForEach-Object{ $_ } | 
ConvertTo-Csv -NoTypeInformation

Thanks to @notjustme , you can shorten this using (...) like:

($json | ConvertFrom-Json) | ConvertTo-Csv

Note: Corrected per mklement0's answer.

All of these options are essentially placing the array in-front of the pipe to bypass the internal behavior of ConvertFrom-Json

Again not 100% sure why ConvertFrom-Json behaves this way. I'll update with additional information when I find a more formal explanation.

`$($json | ConvertFrom-Json) | ConvertTo-Csv` should work though. — notjustme, Sep 22 '20 at 17:34
+1; re _why_: please see my answer. As for `$(...)`: `(...)` will do, and is generally preferable - see [this answer](https://stackoverflow.com/a/58248195/45375). — mklement0, Sep 24 '20 at 22:46
Thanks as always. Regrettably I didn't check 7+ in this case. I agree I rarely use `$(...)` outside of expanding strings. I missed it as I was copying from @notjustme's comment. — Steven, Sep 25 '20 at 11:41
Oh yeah, muscle memory... Sooo many strings expanded on a daily basis. :) — notjustme, Sep 25 '20 at 13:14

score 1 · Accepted Answer · answered Sep 24 '20 at 22:11

To complement Steven's helpful answer with a succinct summary and a cross-edition perspective:

In PowerShell [Core] v7+, no extra effort is needed: appending | ConvertFrom-Json | ConvertTo-Csv to your code works as-is.
In Windows PowerShell and PowerShell [Core] v6.x, you need to force enumeration of ConvertFrom-Json's output in order for ConvertTo-Csv to work correctly, because, against PowerShell's usual behavior, ConvertFrom-Json outputs a JSON array as a whole, as a single object to the pipeline.
- It is the fact that this behavior is unusual that prompted the v7+ change - see this answer for background information.
- The simplest way to force enumeration - i.e. to send an output-as-a-whole array's / collection's elements one by one to the pipeline is to use (...), the grouping operator.

# Works in all PowerShell versions, but in v7+ the (...) is no longer necessary.
(
"name,data
Play,http://{gho}.domain.com/
BDomain,domain.com
Charts,2
Compress,0
CompressJ,0" | ConvertFrom-Csv | ConvertTo-Json |
  ConvertFrom-Json
) | ConvertTo-Csv -NoTypeInformation

This yields:

"name","data"
"Play","http://{gho}.domain.com/"
"BDomain","domain.com"
"Charts","2"
"Compress","0"
"CompressJ","0"

Convert to csv from json in powershell

2 Answers2

Linked