JSON to CSV, formatting problem

PowerShell Help
1

I am converting some JSONs into CSV and its works pretty well. The script I am using is pretty simple:

get-content $json | convertfrom-json | export-csv $filepath

My problem is that some of the values in the JSON are contained within { }. And those values appear as System.Object[] is the CSV. Otherwise it works great. Any recommendations? I am pretty new to powershell so I hope its an easy one!

Edit:

Raw JSON data

 "domains": ["madeup.com"]

Data After Convertfrom-JSON

domains   : {madeup.com}

I did not realize that the raw JSON did not have { }. It looks like it adds the curly brackets after converting it.

2

Welcome back. Good news, it is quite easy. However, you have to decide how to format the data. What do you want to end up with. I tend to concatenate the values with a semicolon. It should be different than your csv delimiter, which is a comma by default. Do you have some sample data you can add to your post to make it easier for us to help you? You can edit your original post. While you’re doing that, be sure to format your code and the sample JSON data with the preformatted option. It’s the </> symbol. If you don’t see it, check the settings wheel - it will be there.

3

I edited my original post and hopefully got the information you were asking for.

4

Please test your code/samples. What you provided is not valid JSON. However, adding the appropriate opening and closing curly braces makes it valid. I’ve also expanded your example to make it clearer what we are doing

$tempfile = New-TemporaryFile

@'
{
"domains": ["madeup.com"],
"Property": "Property",
"Properties": [
    "Property1",
    "Property2"
]
}
'@ | Set-Content $tempfile

Ok now that I have so sample data to use, let’s break it down. The [ ] brackets in json indicates an array of data. Your example was an array of one item. You can see the difference in the output after conversion

Get-Content $tempfile|ConvertFrom-Json

domains      Property Properties
-------      -------- ----------
{madeup.com} Property {Property1, Property2}

Notice the single property does indeed not have the curly braces. We could check each property to see if it’s an array. For this, we can use the the under-the-hood psobject. First we’ll get all the property names, then use those to check the values of each

$jsondata = Get-Content $tempfile | Convertfrom-Json

$propertynames = $jsondata.psobject.properties.name

foreach($property in $propertynames){
    $jsondata.$property -is [array]
}

True
False
True

For each that is an array, -join them and assign back to each property

$jsondata = Get-Content $tempfile | Convertfrom-Json

$propertynames = $jsondata.psobject.properties.name

foreach($property in $propertynames){
    if($jsondata.$property -is [array]){
        $jsondata.$property = $jsondata.$property -join '; '
    }
}

$jsondata

domains    Property Properties
-------    -------- ----------
madeup.com Property Property1; Property2

You can see the curly braces are gone. However, for this example we don’t need to check if it’s an array. We can just -join each property and if it’s a single value, it won’t change anything.

$jsondata = Get-Content $tempfile | Convertfrom-Json

$propertynames = $jsondata.psobject.properties.name

foreach($property in $propertynames){
    $jsondata.$property = $jsondata.$property -join '; '
}

$jsondata

domains    Property Properties
-------    -------- ----------
madeup.com Property Property1; Property2

Now you can export $jsondata to csv and it will be the values as seen there with the semicolon

2 Likes