How to Convert JSON to CSV in PowerShell?

Today, I have an important requirement regarding converting JSON to CSV in PowerShell. There are various methods to do this. In this tutorial, I will show you how to convert JSON to CSV in PowerShell using different methods with examples.

To convert a JSON variable to CSV in PowerShell, first load the JSON data using Get-Content and convert it to a PowerShell object with ConvertFrom-Json. Then, export the data to a CSV file using Export-Csv. For example, if you have a JSON file data.json, you can use the following commands:

$jsonData = Get-Content -Path "C:\MyFolder\data.json" -Raw | ConvertFrom-Json
$jsonData | Export-Csv -Path "C:\MyFolder\data.csv" -NoTypeInformation

This will create a CSV file with the JSON data, making it easy to manipulate and analyze.

Table of Contents

Convert JSON to CSV in PowerShell

JSON (JavaScript Object Notation) is a popular format for data exchange, especially in web applications. However, sometimes, you might need to convert JSON data to CSV (Comma-Separated Values) format for easier analysis or reporting.

There are different methods to convert a JSON variable to CSV in PowerShell, including a few examples.

For all the examples, I will take the below example.

Let’s take a custom JSON file data.json with the following content:

[
    {
        "Name": "John Doe",
        "Age": 30,
        "City": "New York"
    },
    {
        "Name": "Jane Smith",
        "Age": 25,
        "City": "Los Angeles"
    }
]

Method 1: Basic Conversion Using ConvertFrom-Json and Export-Csv

Now, let us see how to convert JSON to CSV files using ConvertFrom-Json and Export-Csv.

# Load JSON data
$jsonData = Get-Content -Path "C:\MyFolder\data.json" -Raw | ConvertFrom-Json

# Convert to CSV
$jsonData | Export-Csv -Path "C:\MyFolder\data.csv" -NoTypeInformation

Load JSON Data: First, load your JSON data into a PowerShell variable.
Convert JSON to PowerShell Object: Use the ConvertFrom-Json cmdlet.
Export to CSV: Use the Export-Csv cmdlet to save the data in CSV format.

Once you execute the above PowerShell script, then it will create a CSV file data.csv with the following content:

"Name","Age","City"
"John Doe","30","New York"
"Jane Smith","25","Los Angeles"

I executed the above PowerShell script, and you can see the output in the screenshot below:

Check out How to Convert String to JSON in PowerShell?

Method 2: Convert Nested JSON to CSV in PowerShell

Nested JSON structures can be more complex to convert. In such cases, you may need to flatten the JSON data before converting it to CSV.

Consider the following nested JSON data:

[
    {
        "Name": "John Doe",
        "Details": {
            "Age": 30,
            "City": "New York"
        }
    },
    {
        "Name": "Jane Smith",
        "Details": {
            "Age": 25,
            "City": "Los Angeles"
        }
    }
]

To handle this, you can use the Select-Object cmdlet to flatten the structure:

# Load JSON data
$jsonData = Get-Content -Path "C:\MyFolder\nested_data.json" -Raw | ConvertFrom-Json

# Flatten and convert to CSV
$jsonData | Select-Object Name, @{Name='Age';Expression={$_.Details.Age}}, @{Name='City';Expression={$_.Details.City}} | Export-Csv -Path "C:\MyFolder\nested_data.csv" -NoTypeInformation

This will produce a CSV file nested_data.csv with the following content:

"Name","Age","City"
"John Doe","30","New York"
"Jane Smith","25","Los Angeles"

Here is the output in the screenshot below:

How to Convert JSON to CSV in PowerShell

Check out How to Write JSON to File in PowerShell?

Method 3: Using Custom PowerShell Functions

For more complex scenarios, you might want to create a custom PowerShell function to handle the conversion. This is especially useful if you need to perform the conversion frequently or handle various JSON structures.

Here is an example.

Here’s a custom function to convert JSON to CSV:

function Convert-JsonToCsv {
    param (
        [string]$jsonPath,
        [string]$csvPath
    )

    # Load JSON data
    $jsonData = Get-Content -Path $jsonPath -Raw | ConvertFrom-Json

    # Check if JSON data is an array
    if ($jsonData -is [System.Collections.IEnumerable]) {
        # Flatten nested structures if needed
        $flattenedData = $jsonData | ForEach-Object {
            [PSCustomObject]@{
                Name = $_.Name
                Age = $_.Details.Age
                City = $_.Details.City
            }
        }

        # Export to CSV
        $flattenedData | Export-Csv -Path $csvPath -NoTypeInformation
    } else {
        Write-Error "JSON data is not an array."
    }
}

# Usage
Convert-JsonToCsv -jsonPath "C:\MyFolder\nested_data.json" -csvPath "C:\MyFolder\output.csv"

This function takes the path to a JSON file and the path where you want to save the CSV file. It handles nested structures by flattening them before exporting to CSV.

Conclusion

In this tutorial, I have explained how to convert JSON to CSV in PowerShell using ConvertFrom-Json and Export-Csv. Also, I have explained how to convert nested json to CSV in PowerShell.

Convert JSON to CSV in PowerShell

Method 1: Basic Conversion Using ConvertFrom-Json and Export-Csv

Method 2: Convert Nested JSON to CSV in PowerShell

Method 3: Using Custom PowerShell Functions

Conclusion

100 POWERSHELL CMDLETS E-BOOK