Powershell ConvertFrom-JSON to csv file(s)

Question

I have searched looking for examples of converting complex json (embedded arrays] into csv file(s) using Powershell. The goal is to accept json data into a MSAccess database. MSAccess does not provide an intrinsic function to do this. I am new to Powershell and json, but I did discover the ConvertFrom-JSON cmdlet that got my interest. The best info I have found is the

Flatten-Object function by iRon

in response to this article

PowerShell convert nested JSON array into separate columns in CSV file

While this function works to create a single csv, I am interested in creating multiple csv files if there are embedded arrays in the json. The idea is to create a csv file for data at each level. Level 2 and lower will require a link field (id/name) to be used as a primary key in level1, and as foreign key in level2. A PK field at the level2, would be included as a foreign key at level3 and so on. Since Access can import csv data to a table, my feeling is that getting the data into "normalized" csv files would be a repeatable method to get json data into an MSAccess database.

So with respect to my objective and the Flatten-Object function, I am looking for advice/direction on the following:

• Could the function be adjusted/used to identify
• the levels in the json file,
• to create a csv for each of those levels with a selectable PK field(s) to relate
• the csv data files in a normalized manner for import to MSAccess??

I do realize that some human intervention will be required for each json file. So I'm looking for an approach that simplifies the effort and is repeatable.

I have created a simple script to take a simple json file (no embedded array) and convert it to CSV. I have used the Shell command in vba to execute the PS script.

    <#CarsBasic.ps1
.DESCRIPTION
This script takes the cars.json file and reads it into memory
Converts it from Json, then selects id,manufacturer,year from the result
and exports the data to C:\Programs\CarsJack.csv as a csv file with header
#>
 (Get-Content C:\Programs\MendipDataSystems\JSONParser\Files\Cars.json -Raw | 
 ConvertFrom-Json)  |Select id,manufacturer,year | 
  Export-CSV  c:\programs\CarsJack.csv -NoTypeInformation

Thanks in advance.

I have adjusted this post based on request/comment by iRon.

Sample json file that has Squad, SquadMember and SquadMemberPower levels. I would like to get a Squad.csv that has Squad info, and a SquadMember.csv that has the Squadname and each of the Member details, and a SquadmemberPower csv that has the SquadName and the Member Name identifying to whom that Power belongs. in effect, these 3 csv files would be loaded into MSAccess as 3 normalized tables. This is my test case, but I'd like a more general, reusable approach--if possible. Here is the MultiSquad.json

[{
    "squadName": "Super hero squad Alpha",
    "homeTown": "Metro City",
    "formed": 2016,
    "secretBase": "Large tent in the forest",
    "active": "True",
    "members": [{
        "name": "Molecule Man",
        "age": 29,
        "secretIdentity": "Dan Jukes",
        "powers": ["Radiation resistance",
        "Turning tiny",
        "Radiation blast"]
    },
    {
        "name": "Madame Uppercut",
        "age": 39,
        "secretIdentity": "Jane Wilson",
        "powers": ["Million tonne punch",
        "Damage resistance",
        "Superhuman reflexes"]
    },
    {
        "name": "Eternal Flame",
        "age": 1000000,
        "secretIdentity": "Unknown",
        "powers": ["Immortality",
        "Heat Immunity",
        "Inferno",
        "Teleportation",
        "Interdimensional travel"]
    }]
},
{
    "squadName": "Second squad Baker",
    "homeTown": "Metro Toronto",
    "formed": 2017,
    "secretBase": "CN tower",
    "active": "True",
    "members": [{
        "name": "Kathleen Wynne",
        "age": 49,
        "secretIdentity": "Cyan Arrah",
        "powers": ["XRay vision",
        "Invisibility",
        "Radiation blast"]
    },
    {
        "name": "Madame Butterfly",
        "age": 27,
        "secretIdentity": "Iman Angel",
        "powers": ["Magical hearing",
        "Fantastic ideas"]
    },
    {
        "name": "Gassy Misty Cloud",
        "age": 1000,
        "secretIdentity": "Puff of Smoke",
        "powers": ["Immortality",
        "Heat and Flame Immunity",
        "Impeccable hearing",
        "Xray Vision",
        "Able to jump tall buildings",
        "Teleportation",
        "Intergalactic travel"]
    }]
}]

Expected Output: 3 csv files

1) Squad.csv with fields "squadName","homeTown","formed","secretBase","active"

2) SquadMembers.csv with fields "squadName","name","age","secretIdentity"

3)SquadMemberPowers.csv with fields "Name","powers"

Answer 1

First get the json to an object:

$obj = Get-Content C:/input.json | ConvertFrom-Json

Then you have at least two ways how to select the items you want.

Simple selection:

$obj | select squadName, homeTown, formed, secretBase, active | Convertto-csv > c:\squads.csv

Complex selection:

$members = $obj | foreach {

    $squadName = $_.squadName

    $_.members | foreach {
        [pscustomobject]@{
            squadName = $squadName
            name = $_.name
            age = $_.age
            secretIdentity = $_.secretIdentity
        }
    }   
}
$members | ConvertTo-Csv > c:\members.csv

$powers = $obj.members | foreach {
    $memberName = $_.name
    $_.powers | foreach {
        [pscustomobject]@{
            name = $memberName
            power = $_
        }
    }
}
$powers | ConvertTo-Csv > c:\powers.csv

Answer 2

Specific solution

Assuming that $JSON contains your JSON object:

$Squads = @(); $SquadMembers = @(); $SquadMemberPowers = @()

ForEach ($Squad In $JSON) {
    $Squads += New-Object PSObject ($Squad | Select squadName, homeTown, formed, secretBase, active)
    ForEach ($member In $Squad.members) {
        $SquadMembers += New-Object PSObject ($member | Select @{label = "squadName" ;expression = {$Squad.squadName}}, name, age, secretIdentity)
        ForEach ($power In $member.powers) {
            $SquadMemberPowers += New-Object PSObject ($member | Select @{label = "name" ;expression = {$member.name}}, @{label = "powers" ;expression = {$power}})
        }
    }
}

$Squads | Export-CSV ".\Squad.csv" -NoTypeInformation
$SquadMembers | Export-CSV ".\SquadMembers.csv" -NoTypeInformation
$SquadMemberPowers | Export-CSV ".\SquadMemberPowers.csv" -NoTypeInformation

General solution

With regards to a general (reusable) solution, I don't think that your request is general enough for that: at the members level you have an array with hashtables that you want to enumerate, at the powers level you like to transpose the array and than you want to pickup some properties from the parent that are not common (squadname vs name. You might consider here to refer to the first property but hashtables in PowerShell do not always stay in order, see: Powershell Hashtables Key Order).
In other words, for a general solution you will need to supply so many arguments that there will not much of an added value in comparison specific script as purposed above and changing it's adjusting it's functions and variables.

Answer 3

The below command can be used for separating the csv data into columns with the delimiter ",".

For Example: Import-Csv "C:\Result.csv" -delimiter "," | Sort-Object _from -Unique | Export-csv "C:\FINAL_REPORT.csv"