Parse XML to CSV Help

Question

Hi, I am absolutely green at PowerShell and I need to read an XML I get from a people counting device, modify it and export to CSV.
Any help would be greatly appreciated.

" This is what I have"



    
        6
        1628084632
        1
        (GMT 01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna
        1
        2510
        20488802
        0
        6.0.5507.770

"And this is the output I would like"

"Sitename","Date","StartTime","EndTime","UnixStartTime","Enters","Exits","Status"
"ABC","2021-08-03","00:00:00","01:00:00","1630706400","0","0","5"
"ABC","2021-08-03","01:00:00","02:00:00","1630710000","0","0","4"
"ABC","2021-08-03","02:00:00","03:00:00","1630713600","0","0","4"
"ABC","2021-08-03","03:00:00","04:00:00","1630717200","0","0","4"
"ABC","2021-08-03","04:00:00","05:00:00","1630720800","0","0","4"
"ABC","2021-08-03","05:00:00","06:00:00","1630724400","0","0","5"
"ABC","2021-08-03","06:00:00","07:00:00","1630728000","15","10","5"
"ABC","2021-08-03","07:00:00","08:00:00","1630731600","192","10","5"
"ABC","2021-08-03","08:00:00","09:00:00","1630735200","237","34","5"
"ABC","2021-08-03","09:00:00","10:00:00","1630738800","336","29","5"
"ABC","2021-08-03","10:00:00","11:00:00","1630742400","411","27","5"
"ABC","2021-08-03","11:00:00","12:00:00","1630746000","483","45","5"
"ABC","2021-08-03","12:00:00","13:00:00","1630749600","436","30","5"
"ABC","2021-08-03","13:00:00","14:00:00","1630753200","478","42","5"
"ABC","2021-08-03","14:00:00","15:00:00","1630756800","498","44","5"
"ABC","2021-08-03","15:00:00","16:00:00","1630760400","418","31","5"
"ABC","2021-08-03","16:00:00","17:00:00","1630764000","410","37","5"
"ABC","2021-08-03","17:00:00","18:00:00","1630767600","495","36","5"
"ABC","2021-08-03","18:00:00","19:00:00","1630771200","397","18","5"
"ABC","2021-08-03","19:00:00","20:00:00","1630774800","190","17","5"
"ABC","2021-08-03","20:00:00","21:00:00","1630778400","0","0","5"
"ABC","2021-08-03","21:00:00","22:00:00","1630782000","0","0","5"
"ABC","2021-08-03","22:00:00","23:00:00","1630785600","0","0","5"
"ABC","2021-08-03","23:00:00","00:00:00","1630789200","0","0","5"

Accepted Answer

There's no need to manually parse the XML.

Try this:

[xml]$x = Get-Content C:\junk\Metrics.xml

$sitename = $x.Metrics.SiteName
$date = $x.metrics.ReportData.Report.Date
$x.metrics.ReportData.Report.Object.Count |
    ForEach-Object{
        [PSCustomObject]@{
            SiteName = $sitename
            Date = $date
            StartTime = $_.StartTime
            EndTime = $_.EndTime
            UnixStartTime = Get-Date ("{0} {1}" -f $date, $_.StartTime) -Uformat %s
            Enters = $_.Enters
            Exits = $_.Exits
            Status = $_.Status
        }
    } | Export-Csv c:\junk\Metrics.csv -NoTypeInformation

Answer

Hello @Steven Cilia ,

Firstly, If you previously created an XML Map, you can use it to import XML data into cells that are mapped, but there also are several methods and commands for importing XML data without an XML Map. use the below link to import XML

https://support.microsoft.com/en-us/office/import-xml-data-6eca3906-d6c9-4f0d-b911-c736da817fa4

The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is a row that includes a comma-separated list of the object's property values. You can use the Export-CSV cmdlet to create spreadsheets and share data with programs that accept CSV files as input.

Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties. To export only selected properties of an object, use the Select-Object cmdlet.

To Get to know better about the export of CSV files do follow up the below link

https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

Hope this answers all your queries, if not please do repost back.
If an Answer is helpful, please click "Accept Answer" and upvote it : )

Share via

Parse XML to CSV Help

1 additional answer

Your answer