使用Powershell将JSON转换为CSV文件

Question

使用Powershell将JSON转换为CSV文件

3

我已经搜索了许多例子，试图使用Powershell将复杂的JSON（嵌套数组）转换为CSV文件。目标是将JSON数据导入MSAccess数据库。MSAccess没有提供内在函数来完成此操作。我对Powershell和JSON都不熟悉，但我发现了ConvertFrom-JSON cmdlet，这引起了我的兴趣。我找到的最好的信息是由iRon编写的Flatten-Object函数，它是针对以下文章的回应： PowerShell convert nested JSON array into separate columns in CSV file 虽然该函数可以创建单个csv文件，但如果json中有嵌套的数组，我希望创建多个csv文件。想法是为每个级别的数据创建一个csv文件。第二级及以下需要使用链接字段（id/name）作为级别1中的主键，并作为级别2中的外键。在级别2处的PK字段将作为外键包含在级别3中，以此类推。由于Access可以将csv数据导入表格，因此将数据转换为“规范化”的csv文件可能是将json数据导入MSAccess数据库的可重复方法。

因此，关于我的目标和Flatten-Object函数，我正在寻求以下方面的建议/指导：

是否可以调整/使用该函数来识别json文件中的级别，
为每个级别创建一个csv，并选择一个PK字段以规范化地关联csv数据文件以导入MSAccess？？

我知道每个json文件都需要一些人工干预。因此，我正在寻找一种简化工作并可重复的方法。我已经创建了一个简单的脚本，用于将简单的JSON文件（没有嵌套数组）转换为CSV。我在vba中使用Shell命令执行PS脚本。

    <#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

提前感谢。

根据iRon的要求/评论，我已调整此帖子。

这是一个包含Squad、SquadMember和SquadMemberPower等级的样本json文件。我想要得到一个包含Squad信息的Squad.csv文件，以及一个包含Squad名称和每个成员详细信息的SquadMember.csv文件，还有一个包含SquadName和成员姓名的SquadmemberPower.csv文件，以识别该能力属于哪个成员。实际上，这三个csv文件将被加载到MSAccess中作为三个规范化表。这是我的测试案例，但如果可能的话，我想要一个更通用、可重用的方法。这是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"]
    }]
}]

预期输出：3个csv文件。 1）Squad.csv，字段为“squadName”，“homeTown”，“formed”，“secretBase”，“active”。 2）SquadMembers.csv，字段为“squadName”，“name”，“age”，“secretIdentity”。 3）SquadMemberPowers.csv，字段为“Name”，“powers”。

- Orange

你好，Orange，你能给一个输入文件和期望输出文件的例子吗？ - iRon

谢谢您的回复。我一直在使用作为测试用例的样本是什么？我尝试发布一个JSON示例，但字符太多了。我不确定如何在这里附加文件。 - Orange

3个回答

1

具体解决方案

假设$JSON包含您的JSON对象：

$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

通用解决方案

关于通用（可重复使用）的解决方案，我认为你的请求并不够通用: 在成员级别，你有一个包含哈希表的数组需要枚举，在权限级别，你想要转置该数组，然后从父级中选择一些不常见的属性（squadname与name）。您可能需要参考第一个属性，但是PowerShell中的哈希表不总保持有序，详见：Powershell Hashtables Key Order)。
换句话说，对于通用解决方案，您需要提供这么多的参数，以至于与上述特定脚本相比，将没有太多的附加价值，并更改其函数和变量。

- iRon

我觉得我有点词盲，对我来说脚本比完整的描述更容易阅读...无论如何，我已经相应地改变了答案。 - iRon

我希望我能熟练掌握PS脚本。谢谢你的回答 - 非常有帮助。我试图通过用简单的英语陈述来识别您在脚本中所做的事情来学习术语 - 希望能够认识到您采取的方法（思考过程）。我喜欢 Flatten-Object，并希望能够获得某种函数/脚本，您可以将文件特定值插入其中以获得所需的CSV。这是一种可在各种情况下使用的例行程序。用户必须知道要向参数提供哪些值，但例行程序可以重复使用。这是可能的吗？ - Orange

iRon——忘了说你的最新脚本对所有3个级别都产生了期望的输出。如果你知道哪些网站或文章可以通过示例帮助新的PS用户熟悉概念和语法，那将不胜感激。再次感谢。 - Orange

我尝试提高投票，但由于我是新手（声望低[3]），我的投票似乎被隐藏/不是永久性的？在stackoverflow周围似乎有很多“细节”对于新参与者来说并不明显。 - Orange

谢谢，虽然点赞本身不可见，但我确实获得了额外的+15分。 - iRon

显示剩余3条评论

0

以下命令可用于使用分隔符“，”将 CSV 数据分列。

例如： Import-Csv "C:\Result.csv" -delimiter "," | Sort-Object _from -Unique | Export-csv "C:\FINAL_REPORT.csv"

- sameervsarma

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Mark Toman · Accepted Answer

1

首先将json转换为对象：

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

你至少有两种方式可以选择你想要的项目。

简单选择：

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

复杂选择：

$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

- Mark Toman

谢谢mtman。我尝试了你的代码，它可以工作，但是在SquadMembers.csv中，我得到了一个逗号分隔的powers列表。我真的想要一个单独的SquadMemberPowers.csv，显示成员名称和个人记录的power。我尝试调整脚本，但好像在forEach循环中迷失了方向。我得到了一个空的csv（标题是正确的）。#提取power信息在这里需要当前小队成员的powers $powers = $obj | foreach { $Name = $.name $.powers | foreach { [pscustomobject]@{ name = $name power =$_.power } } } - Orange

对于mtman和iRon（以及其他人）：是否可以通过使用属性等来获得类似的结果，而无需在脚本中使用显式名称？也就是说，我是否可以解析一个json文件并获取涉及的级别数量；这些级别的名称以及字段名称和值。如果这不是小队和成员等，而是订单和详细信息或任何其他内容 - 是否可以设置此项而不知道特定文件的显式名称。我正在尝试查看是否有通用解决方案来解析一些json文件。提前致谢。 - Orange

@Orange 您可以递归迭代 $obj，并使用 .GetType() 确定当前对象是数组还是 PSCustomObject。如果它是一个数组，您可以迭代它并为每个项调用递归函数。如果它是一个 PSCO，则可以通过 .psobject.properties 获取其成员。 - Mark Toman

谢谢mtman。正如您所看到的，我是Powershell的新手 - 仍在努力理解命令和语法。我正在尝试找到/获取/创建一个通用的json解析器（需要一些手动工作/组件）。我的示例文件包含嵌入式数组，这是一个障碍。我正在努力弄清楚如何查看json并确定涉及多少级别，然后查看是否可以将每个级别的字段和值分别放入单独的csv中，而不知道/使用json中使用的名称。PS知道名称和值，因此必须有一种方法。非常感谢您的帮助，但愿我更熟悉术语。 - Orange

@Orange，你可以按照我在上一条评论中的提示，在不知道JSON属性名称的情况下遍历它，更难的部分是如何确定哪些属性应该放到单独的文件中。在你的情况下，你可以说父级的任何带有“name”名称的属性都将添加到每个子级中。这样的步骤使其成为非通用转换器，顺便说一句。如果答案解决了你最初的问题，请点击“接受”。如果你有一个新问题，请单独发布。 - Mark Toman

谢谢mtman。我同意，用户必须知道/确定哪些级别的字段会使规范化表，并设置适当的关系。也许“通用解决方案”不是正确的术语。用户必须为参数识别正确的值，然后调用代码。我刚刚发现检查标记是“接受”，并已经这样做了。如果您有教程或网站可以帮助“新手”通过示例学习PS，我将非常感激。我不是系统/网络人员，我的背景是数据库。我将审查GetType()和属性。您能否将一个问题标识为“与另一个相关”？ - Orange