Powershell: JSON to Excel-CodePudding

I have had a problem for days and am now reporting here. I want to export several JSON files to an Excel spreadsheet. The JSON keys should form the headers and the values should be listed under the headers. Unfortunately, I have zero understanding of Powershell and can't get any further with the help of other threads, as I also don't understand why something works or doesn't work the way it does.

The json files look something like this

{"dataCollection": [
  {
    "objectID": 000001,
    "randomID": 123,
    "desc": "The sky is blue",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": true,
    "caseTwo": false,
    "caseThree": null
  },
  {
    "objectID": 333222,
    "randomID": 456,
    "desc": "example",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": false,
    "caseTwo": true,
    "caseThree": null
  },
  {
    "objectID": 111111,
    "randomID": 789,
    "desc": "Mo-Fr 60% 20-24",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": false,
    "caseTwo": false,
    "caseThree": null
  }
]}

My current code looks like this

$contentJson = Get-Content -Raw -Path $jsonInput | ConvertFrom-Json
$obj_list = $contentJson | Select-Object @{Name='Name';Expression={$_}}
$obj_list | Export-Csv $csvOutput -NoType -Delimiter "`t" -Encoding Unicode
(Get-Content -Path $csvOutput -Raw).replace('"','') | Set-Content -Path $csvOutput

This does give me a CSV with the information from the json, however it is transferred cell by cell and I have no idea how to create headers. Further this works at all only, as soon as I remove in the first line of the JSON (in this case {"DataCollection":), otherwise in the Excel table only the following is written: @{ttDebugTage=System.Object[]}

My goal is something looking like this: Excel:

This is the first time I'm working with Powershell and unfortunately I'm completely lacking in understanding, so I would appreciate any help.

CodePudding user response：

$contentJson = @'
{"dataCollection": [
  {
    "objectID": 000001,
    "randomID": 123,
    "desc": "The sky is blue",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": true,
    "caseTwo": false,
    "caseThree": null
  },
  {
    "objectID": 333222,
    "randomID": 456,
    "desc": "example",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": false,
    "caseTwo": true,
    "caseThree": null
  },
  {
    "objectID": 111111,
    "randomID": 789,
    "desc": "Mo-Fr 60% 20-24",
    "startTime": "2022-03-15T11:31:56.510",
    "endTime": "2022-03-15T11:31:56.511",
    "caseOne": false,
    "caseTwo": false,
    "caseThree": null
  }
]}
'@

($contentJson | ConvertFrom-Json).dataCollection |
    Select-Object -Property objectID, randomID, desc |ConvertTo-Csv -Delimiter "`t"
"objectID"      "randomID"      "desc"
"1"     "123"   "The sky is blue"
"333222"        "456"   "example"
"111111"        "789"   "Mo-Fr 60% 20-24"

CodePudding user response：

################## PFADE ###################
$jsonDirectory = 'C:\Users\Ely\Desktop\jsonScript'
$csvFile = 'C:\Users\Ely\Desktop\jsonScript\test.csv'
################ Variablen #################
$excel = New-Object -ComObject Excel.Application
############################################

# ALLE JSON FILES
$jsonFiles = Get-ChildItem -Path $jsonDirectory -Filter *.json

# FILTER
$unwantedKeys = @("endTime", "caseOne", "caseTwo", "caseThree")

# ARRAY ZWISCHENSPEICHER
$jsonData = @()

foreach ($jsonFile in $jsonFiles) {
    $json = Get-Content $jsonFile.FullName | ConvertFrom-Json

    $json = $json.dataCollection | Select-Object * -ExcludeProperty $unwantedKeys

    $jsonData  = $json
}

# Erstellt CSV und Importiert JSON Content
$jsonData | Export-Csv $csvFile -NoTypeInformation -Delimiter "`t" -Encoding Unicode

$Workbook = $excel.Workbooks.Open($csvFile)
$Worksheet = $Workbook.Sheets.Item(1)
$range = $worksheet.UsedRange
$range.EntireColumn.AutoFit()
$excel.Visible = $True

The script now searches all JSON files in the folder and exports the content to a CSV file. The JSON keys are set as headers and the values are sorted underneath. In the script is the variable $unwantedKeys, which allows to filter out unwanted keys, in my example (endTime, caseOne, caseTwo and CaseThree).