Powershell: JSON to Excel

26elyesa
January 11, 2023
214 views
0 votes
2 Answers

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.

Answers

Chosen as BEST ANSWER

################## PFADE ###################
$jsonDirectory = 'DIRTOJSONFILES'
$csvFile = 'DIRTOOUTPUTFILE.CSV'
################ Variablen #################
$excel = New-Object -ComObject Excel.Application
############################################

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

#FILTER
$unwantedKeys = @("Example1", "Example2", "Example3")

#ARRAY ZWISCHENSPEICHER
$jsonData = @()

foreach ($jsonFile in $jsonFiles) {
    #Lädt JSON File
    $json = Get-Content $jsonFile.FullName | ConvertFrom-Json
    $firstKey = ($json.PSObject.Properties.Name)

    #Filter
    $json = $json.$firstKey | Select-Object * -ExcludeProperty $unwantedKeys

    #Content ins Array
    $jsonData += $json
}

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

#Anpassen von Spaltenbreite auf Valuelänge
$Workbook = $excel.Workbooks.Open($csvFile)
$Worksheet = $Workbook.Sheets.Item(1)
$range = $worksheet.UsedRange
$range.EntireColumn.AutoFit()
$excel.Visible = $True

(Edit)

$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"

Please signup or login to give your own answer.

Click here to cancel reply.