Powershell：JSON到Excel

siv3szwd 于 2023-01-14 发布在 Shell

关注(0)|答案(2)|浏览(183)

几天前我就遇到了一个问题，现在在这里报告。我想将几个JSON文件导出到Excel电子表格中。JSON键应该形成标题，值应该列在标题下。不幸的是，我对Powershell一无所知，无法在其他线程的帮助下取得任何进展，因为我也不明白为什么有些东西会以它的方式工作或不工作。
json文件如下所示

{"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 = 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

这确实给予了我一个包含JSON信息的CSV，但是它是一个单元格一个单元格地传输的，我不知道如何创建标题。此外，这只在我删除JSON的第一行（在本例中为{“DataCollection”：）时才起作用，否则在Excel表中只会写入以下内容：@{ttDebugTage=系统对象[]}
我的目标是这样的：Excel:
这是我第一次使用Powershell，不幸的是我完全缺乏理解，所以我将感谢任何帮助。

excel

来源：https://stackoverflow.com/questions/75080519/powershell-json-to-excel

2条答案

按热度按时间

ej83mcc01#

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

赞(0）回复(0）举报 2023-01-14

zazmityj2#

################## PFADE ###################
$jsonDirectory = 'DIR\TO\JSON\FILES'
$csvFile = 'DIR\TO\OUTPUT\FILE.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

赞(0）回复(0）举报 2023-01-14

我来回答

Powershell：JSON到Excel

2条答案

相关问题

热门标签

最新问答