xml - 使用Powershell将嵌套的xml转换为csv时遇到问题

Question

我有一个嵌套的XML，我需要使用Powershell将其转换为CSV。
不幸的是，我在网上找到了更多的初学者，并且无法使用现有线程解决此问题。

我尝试了将XML文件读入Powershell并创建一个新对象的尝试，但是我导出到csv甚至不包含该不足的结果... :(

我拥有的XML文件如下所示:

<?xml version="1.0" encoding="ISO-8859-1"?>
<Data source="Jhonny" datetime="2019-04-23T10:07:50+02:00" timezone="Europe">
    <dealerships>
        <location name="Germany">
            <series parameter="Sold Cars" unit="car">
                <value datetime="2019-04-22T00:00:00+02:00" value="7.3"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="7.8"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="7.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="6.0"/>
            </series>
            <series parameter="Sold Cars" unit="Auto">
                <value datetime="2019-04-22T00:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="4.0"/>
            </series>
        </location>
        <location name="USA">
            <series parameter="Sold Cars" unit="car">
                <value datetime="2019-04-22T00:00:00+02:00" value="5.1"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="4.1"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="3.6"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="3.1"/>
            </series>
            <series parameter="Sold Cars" unit="Auto">
                <value datetime="2019-04-22T00:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="3.0"/>
            </series>
        </location>
    </dealerships>
</Data>

我想要的结果看起来像这样:

Location;Date/Time;Sold Cars car;Sold Cars AutoGermany; 2019-04-22T00:00:00+02:00; 7.3;4.0Germany; 2019-04-22T00:00:00+02:00; 7.8;5.0Germany; 2019-04-22T00:00:00+02:00; 7.0;3.0Germany; 2019-04-22T00:00:00+02:00; 6.0;4.0USA; 2019-04-22T00:00:00+02:00; 5.1;3.0USA; 2019-04-22T00:00:00+02:00; 4.1;6.0USA; 2019-04-22T00:00:00+02:00; 3.6;1.0USA; 2019-04-22T00:00:00+02:00; 3.1;8.0

As I haven't really gotten anywhere, I don't think my code helps, but here ishow I tried to solve it, but failed:

$xml = "C:\Users\[me]\Convert_XML_to_CSV\cars.xml"
$obj = New-Object System.XML.XMLDocument
$obj.Load("$xml")

foreach ($i in $_.Data.dealerships.location) {
    $o = New-Object Object
    Add-Member -InputObject $o -MemberType NoteProperty -Name location -Value $obj.Data.dealerships.Location $i $o
} | Export-Csv "result.csv" -Delimiter "," -NoType -Encoding UTF8

Answer 1

也许与您期望的输出所显示的不完全相同，但这可能会有所帮助。

注意:我在这里使用xml字符串。在您的情况下，请使用

[xml]$xml = Get-Content "C:\Users\[me]\Convert_XML_to_CSV\cars.xml"

编码:

[xml]$xml = @'
<?xml version="1.0" encoding="ISO-8859-1"?>
<Data source="Jhonny" datetime="2019-04-23T10:07:50+02:00" timezone="Europe">
    <dealerships>
        <location name="Germany">
            <series parameter="Sold Cars" unit="car">
                <value datetime="2019-04-22T00:00:00+02:00" value="7.3"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="7.8"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="7.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="6.0"/>
            </series>
            <series parameter="Sold Cars" unit="Auto">
                <value datetime="2019-04-22T00:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="4.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="4.0"/>
            </series>
        </location>
        <location name="USA">
            <series parameter="Sold Cars" unit="car">
                <value datetime="2019-04-22T00:00:00+02:00" value="5.1"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="4.1"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="3.6"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="3.1"/>
            </series>
            <series parameter="Sold Cars" unit="Auto">
                <value datetime="2019-04-22T00:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T01:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T02:00:00+02:00" value="3.0"/>
                <value datetime="2019-04-22T03:00:00+02:00" value="3.0"/>
            </series>
        </location>
    </dealerships>
</Data>
'@ 

$result = foreach ($item in $xml.Data.dealerships.location) {
    $location = $item.Name

    # get the different column names
    $units = $item.series | ForEach-Object { '{0} {1}' -f $_.parameter, $_.unit}

    # loop through the series
    foreach ($series in $item.series) {
        # and the values
        foreach ($value in $series.value) {
            # since you are using PowerShell 2.0, create the output object like this
            $objOut = New-Object -TypeName PSObject
            $objOut | Add-Member -MemberType NoteProperty -Name 'Location' -Value $location
            $objOut | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value $value.datetime

            $thisUnit = '{0} {1}' -f $series.parameter, $series.unit
            # add the different units as property.
            foreach ($unit in $units) { 
                $val = if ($unit -eq $thisUnit) { $value.value } else { '' }
                $objOut | Add-Member -MemberType NoteProperty -Name $unit -Value $val 
            }

            # output the object
            $objOut
        }
    }
}

# output on screen
$result | Format-Table -AutoSize
# output to CSV file
$result | Export-Csv -Path 'D:\test.csv' -Encoding UTF8 -NoTypeInformation

结果:

Location DateTime                  Sold Cars car Sold Cars Auto
-------- --------                  ------------- --------------
Germany  2019-04-22T00:00:00+02:00 7.3                         
Germany  2019-04-22T01:00:00+02:00 7.8                         
Germany  2019-04-22T02:00:00+02:00 7.0                         
Germany  2019-04-22T03:00:00+02:00 6.0                         
Germany  2019-04-22T00:00:00+02:00               4.0           
Germany  2019-04-22T01:00:00+02:00               4.0           
Germany  2019-04-22T02:00:00+02:00               4.0           
Germany  2019-04-22T03:00:00+02:00               4.0           
USA      2019-04-22T00:00:00+02:00 5.1                         
USA      2019-04-22T01:00:00+02:00 4.1                         
USA      2019-04-22T02:00:00+02:00 3.6                         
USA      2019-04-22T03:00:00+02:00 3.1                         
USA      2019-04-22T00:00:00+02:00               3.0           
USA      2019-04-22T01:00:00+02:00               3.0           
USA      2019-04-22T02:00:00+02:00               3.0           
USA      2019-04-22T03:00:00+02:00               3.0

更新

根据您的评论中的要求，您可以像上面的代码一样进一步组合/分组 $result数组:

$combined = $result | Group-Object -Property DateTime, Location | ForEach-Object {
    foreach ($location in ($_.Group | Group-Object Location)) {
        # create an output object and put in the Location property here
        $objOut = New-Object -TypeName PSObject
        $objOut | Add-Member -MemberType NoteProperty -Name 'Location' -Value ($location.Name)
        foreach ($date in ($location.Group | Group-Object DateTime)) {
            # add the DateTime property
            $objOut | Add-Member -MemberType NoteProperty -Name 'DateTime' -Value ($date.Name)
            foreach ($unit in $_.Group) {
                # join the other two properties to the $objOut object:
                # I do not want to hard-code the property names here, 
                # so use Select-Object to get the remaining props.
                $sold = $unit | Select-Object * -ExcludeProperty Location, DateTime
                foreach ($thing in $sold.psobject.properties | Where-Object { ($_.Value) }) {
                    # if you want the numbers as floating-point numbers, do this:
                    # $objOut | Add-Member -MemberType NoteProperty -Name $($thing.Name) -Value ([double]$thing.Value)
                    # like below, these values will be output as string
                    $objOut | Add-Member -MemberType NoteProperty -Name $($thing.Name) -Value ($thing.Value)
                }
            }
        }
        $objOut
    }
}

# output on screen
$combined | Format-Table -AutoSize
# output to CSV file
$combined | Export-Csv -Path 'D:\test_Grouped.csv' -Encoding UTF8 -NoTypeInformation

这将导致:

Location DateTime                  Sold Cars car Sold Cars Auto
-------- --------                  ------------- --------------
Germany  2019-04-22T00:00:00+02:00 7.3           4.0           
Germany  2019-04-22T01:00:00+02:00 7.8           4.0           
Germany  2019-04-22T02:00:00+02:00 7.0           4.0           
Germany  2019-04-22T03:00:00+02:00 6.0           4.0           
USA      2019-04-22T00:00:00+02:00 5.1           3.0           
USA      2019-04-22T01:00:00+02:00 4.1           3.0           
USA      2019-04-22T02:00:00+02:00 3.6           3.0           
USA      2019-04-22T03:00:00+02:00 3.1           3.0