SteadyLatch

Guides · Data for reports

PowerShell JSON to CSV Without Losing Columns

Published · Tested with PowerShell 7.6.6 on Windows 11 Pro, regional settings Spanish (Spain)

ConvertFrom-Json | Export-Csv looks like the whole job. It works until one record has a field the first one does not: that column is not written, and nothing tells you. This guide shows why, two more ways the same one-liner changes your data, and a short script that keeps every column and checks the file before it says OK.

What the one-liner loses

Four invented orders. The second one has a discount; the third has tags and an address; the fourth has an address without a country:

[
  { "id": 1001, "customer": "Ana Example", "total": 120.5, "paid": true },
  { "id": 1002, "customer": "Ben Example", "total": 0, "paid": false, "discount": 12.75 },
  { "id": 1003, "customer": "Cleo Example", "total": null, "paid": true, "tags": ["priority", "gift"],
    "address": { "city": "Lyon", "country": "FR" } },
  { "id": 1004, "customer": "", "total": 89.9, "paid": true, "address": { "city": "Porto" } }
]

The usual conversion, run in a PowerShell 7 session with Spanish regional settings:

PS> Get-Content orders.json -Raw | ConvertFrom-Json | Export-Csv naive.csv
PS> Get-Content naive.csv
"id","customer","total","paid"
"1001","Ana Example","120,5","True"
"1002","Ben Example","0","False"
"1003","Cleo Example",,"True"
"1004","","89,9","True"

Three things went wrong, and the command reported none of them:

Why it happens

It is documented behaviour. From the notes of Export-Csv: “When you submit multiple objects to Export-Csv, Export-Csv organizes the file based on the properties of the first object that you submit. […] If the remaining objects have additional properties, those property values are not included in the file.” The same page says the property values “are converted to strings using the ToString() method”, which uses your regional settings for decimals (Microsoft Learn, Export-Csv).

JSON from an API is exactly the case where records differ: optional fields are left out instead of sent as null. So the fix is to decide the columns from all records before writing, and to format values yourself.

The script

Save it as ConvertTo-CompleteCsv.ps1. It reads a JSON file with one record or an array of records, and writes a CSV with every column. Nested objects are flattened one level (address.city), arrays of plain values are joined with ; , numbers use a dot, and anything nested deeper stops the run so you can decide how to map it.

#Requires -Version 7
<#
  JSON records to CSV without losing columns. Export-Csv takes its columns from the first record
  only; this script takes every property of every record, flattens one level of nested objects
  (address.city), joins arrays of plain values with "; ", writes numbers with a dot whatever the
  regional settings, and checks the result before it reports success.
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory)] [string] $JsonPath,
    [Parameter(Mandatory)] [string] $CsvPath
)
$ErrorActionPreference = 'Stop'
$inv = [cultureinfo]::InvariantCulture

function Get-FlatRecord {
    <# One record as an ordered list of column → value; nested objects one level deep, no deeper. #>
    param($Record, [int] $Index)
    $flat = [ordered]@{}
    foreach ($p in $Record.PSObject.Properties) {
        $v = $p.Value
        if ($v -is [System.Management.Automation.PSCustomObject]) {
            foreach ($child in $v.PSObject.Properties) {
                if ($child.Value -is [System.Management.Automation.PSCustomObject] -or ($child.Value -is [array] -and @($child.Value | Where-Object { $_ -is [System.Management.Automation.PSCustomObject] }).Count)) {
                    throw "record $Index, $($p.Name).$($child.Name): nested deeper than one level; map it explicitly"
                }
                $flat["$($p.Name).$($child.Name)"] = $child.Value
            }
        }
        elseif ($v -is [array] -and @($v | Where-Object { $_ -is [System.Management.Automation.PSCustomObject] }).Count) {
            throw "record $Index, $($p.Name): array of objects; map it explicitly (one row per item?)"
        }
        else { $flat[$p.Name] = $v }
    }
    return $flat
}

function Format-CsvValue {
    <# Plain value → text: null stays null (empty cell), numbers with a dot, dates as ISO 8601, arrays joined. #>
    param($Value)
    if ($null -eq $Value) { return $null }
    if ($Value -is [array]) { return (($Value | ForEach-Object { Format-CsvValue $_ }) -join '; ') }
    if ($Value -is [datetime]) { return $Value.ToString('o', $inv) }
    if ($Value -is [IFormattable]) { return $Value.ToString($null, $inv) }
    return [string] $Value
}

try {
    $records = @(Get-Content -LiteralPath $JsonPath -Raw | ConvertFrom-Json -NoEnumerate | ForEach-Object { $_ })
    if ($records.Count -eq 0) { Write-Output "EMPTY: $JsonPath has no records. Nothing written."; exit 1 }

    $flat = for ($i = 0; $i -lt $records.Count; $i++) { , (Get-FlatRecord $records[$i] ($i + 1)) }
    # Every column of every record, in the order they first appear.
    $columns = [System.Collections.Generic.List[string]]::new()
    foreach ($r in $flat) { foreach ($k in $r.Keys) { if (-not $columns.Contains($k)) { $columns.Add($k) } } }

    $rows = foreach ($r in $flat) {
        $row = [ordered]@{}
        foreach ($c in $columns) { $row[$c] = if ($r.Contains($c)) { Format-CsvValue $r[$c] } else { $null } }
        [pscustomobject] $row
    }
    $tmp = "$CsvPath.tmp"
    $rows | Export-Csv -LiteralPath $tmp -NoTypeInformation -Encoding utf8BOM

    # Check before success: same columns, same number of rows.
    $back = @(Import-Csv -LiteralPath $tmp)
    $header = @($back[0].PSObject.Properties.Name)
    if ($back.Count -ne $records.Count -or (Compare-Object $header @($columns) -SyncWindow 0)) {
        throw "check failed: wrote $($back.Count) rows and $($header.Count) columns, expected $($records.Count) and $($columns.Count)"
    }
    Move-Item -LiteralPath $tmp -Destination $CsvPath -Force

    $firstOnly = @($flat[0].Keys)
    $missed = @($columns | Where-Object { $_ -notin $firstOnly })
    Write-Output ('{0} records, {1} columns' -f $records.Count, $columns.Count)
    if ($missed.Count) { Write-Output ('not in the first record (Export-Csv alone would drop them): {0}' -f ($missed -join ', ')) }
    Write-Output "OK: $CsvPath"
    exit 0
}
catch {
    if (Test-Path -LiteralPath "$CsvPath.tmp") { Remove-Item -LiteralPath "$CsvPath.tmp" }
    Write-Output "FAILED: $($_.Exception.Message). Nothing written."
    exit 2
}

Two details matter more than they look. The columns are collected in the order they first appear, so the file is stable from run to run. And the CSV is written to a temporary file, read back and compared with the expected columns and row count before it replaces the real one: a run that fails leaves no half-written file behind.

Run it

The same four orders:

PS> ./ConvertTo-CompleteCsv.ps1 -JsonPath orders.json -CsvPath orders.csv
4 records, 8 columns
not in the first record (Export-Csv alone would drop them): discount, tags, address.city, address.country
OK: orders.csv
PS> Get-Content orders.csv
"id","customer","total","paid","discount","tags","address.city","address.country"
"1001","Ana Example","120.5","True",,,,
"1002","Ben Example","0","False","12.75",,,
"1003","Cleo Example",,"True",,"priority; gift","Lyon","FR"
"1004","","89.9","True",,,"Porto",

Every column is there, numbers use a dot, and the run lists the columns that the one-liner would have dropped. A missing value stays an empty cell, and an empty string stays a quoted empty string (""), so the two remain different in the file even though Excel shows both as blank.

A record nested two levels deep is not guessed at:

PS> Get-Content deep.json
[ { "id": 1, "customer": { "name": "Ana", "billing": { "city": "Lyon" } } } ]
PS> ./ConvertTo-CompleteCsv.ps1 -JsonPath deep.json -CsvPath deep.csv
FAILED: record 1, customer.billing: nested deeper than one level; map it explicitly. Nothing written.
0
CSV written and checked.
1
The JSON has no records; nothing written.
2
Nothing written: unreadable JSON, nesting the script will not guess, or the check after writing failed.

Limits

Just want the scripts? The Scripts Pack · €12 has the code of this guide and the other guides ready to run, with examples and tests. The code on this page stays free.

The code on this page was written for this guide and tested on the version above. The orders are invented.