question

StevenCilia-5892 avatar image
0 Votes"
StevenCilia-5892 asked RichMatheisen-8856 edited

Parse XML to CSV Help

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"

 <?xml version="1.0"?>
 <Metrics SiteId="0530" Sitename="ABC" DeviceId="0x01" Devicename="ShoppingArea">
     <Properties>
         <Version>6</Version>
         <TransmitTime>1628084632</TransmitTime>
         <Timezone>1</Timezone>
         <TimezoneName>(GMT 01:00) Amsterdam, Berlin, Bern, Rome, Stockholm, Vienna</TimezoneName>
         <DST>1</DST>
         <HwPlatform>2510</HwPlatform>
         <SerialNumber>20488802</SerialNumber>
         <DeviceType>0</DeviceType>
         <SwRelease>6.0.5507.770</SwRelease>
     </Properties>
     <ReportData Interval="60">
         <Report Date="2021-08-03">
             <Object Id="0" DeviceId="0x01" Devicename="ShoppingArea" ObjectType="0" Name="PC_ShoppingArea">
                 <Count StartTime="00:00:00" EndTime="01:00:00" UnixStartTime="1630706400" Enters="0" Exits="0" Status="5"/>
                 <Count StartTime="01:00:00" EndTime="02:00:00" UnixStartTime="1630710000" Enters="0" Exits="0" Status="4"/>
                 <Count StartTime="02:00:00" EndTime="03:00:00" UnixStartTime="1630713600" Enters="0" Exits="0" Status="4"/>
                 <Count StartTime="03:00:00" EndTime="04:00:00" UnixStartTime="1630717200" Enters="0" Exits="0" Status="4"/>
                 <Count StartTime="04:00:00" EndTime="05:00:00" UnixStartTime="1630720800" Enters="0" Exits="0" Status="4"/>
                 <Count StartTime="05:00:00" EndTime="06:00:00" UnixStartTime="1630724400" Enters="0" Exits="0" Status="5"/>
                 <Count StartTime="06:00:00" EndTime="07:00:00" UnixStartTime="1630728000" Enters="15" Exits="10" Status="5"/>
                 <Count StartTime="07:00:00" EndTime="08:00:00" UnixStartTime="1630731600" Enters="192" Exits="10" Status="5"/>
                 <Count StartTime="08:00:00" EndTime="09:00:00" UnixStartTime="1630735200" Enters="237" Exits="34" Status="5"/>
                 <Count StartTime="09:00:00" EndTime="10:00:00" UnixStartTime="1630738800" Enters="336" Exits="29" Status="5"/>
                 <Count StartTime="10:00:00" EndTime="11:00:00" UnixStartTime="1630742400" Enters="411" Exits="27" Status="5"/>
                 <Count StartTime="11:00:00" EndTime="12:00:00" UnixStartTime="1630746000" Enters="483" Exits="45" Status="5"/>
                 <Count StartTime="12:00:00" EndTime="13:00:00" UnixStartTime="1630749600" Enters="436" Exits="30" Status="5"/>
                 <Count StartTime="13:00:00" EndTime="14:00:00" UnixStartTime="1630753200" Enters="478" Exits="42" Status="5"/>
                 <Count StartTime="14:00:00" EndTime="15:00:00" UnixStartTime="1630756800" Enters="498" Exits="44" Status="5"/>
                 <Count StartTime="15:00:00" EndTime="16:00:00" UnixStartTime="1630760400" Enters="418" Exits="31" Status="5"/>
                 <Count StartTime="16:00:00" EndTime="17:00:00" UnixStartTime="1630764000" Enters="410" Exits="37" Status="5"/>
                 <Count StartTime="17:00:00" EndTime="18:00:00" UnixStartTime="1630767600" Enters="495" Exits="36" Status="5"/>
                 <Count StartTime="18:00:00" EndTime="19:00:00" UnixStartTime="1630771200" Enters="397" Exits="18" Status="5"/>
                 <Count StartTime="19:00:00" EndTime="20:00:00" UnixStartTime="1630774800" Enters="190" Exits="17" Status="5"/>
                 <Count StartTime="20:00:00" EndTime="21:00:00" UnixStartTime="1630778400" Enters="0" Exits="0" Status="5"/>
                 <Count StartTime="21:00:00" EndTime="22:00:00" UnixStartTime="1630782000" Enters="0" Exits="0" Status="5"/>
                 <Count StartTime="22:00:00" EndTime="23:00:00" UnixStartTime="1630785600" Enters="0" Exits="0" Status="5"/>
                 <Count StartTime="23:00:00" EndTime="00:00:00" UnixStartTime="1630789200" Enters="0" Exits="0" Status="5"/>
             </Object>
         </Report>
     </ReportData>
 </Metrics>

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



windows-server-powershell
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RichMatheisen-8856 avatar image
2 Votes"
RichMatheisen-8856 answered RichMatheisen-8856 edited

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
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, @RichMatheisen-8856, It's precisely what I needed. Now for my sake of learning, I'll do some reverse engineering of your script.

Thanks, Much appreciated.
Steve

0 Votes 0 ·

The part of the script that may present a problem is the conversion of the time to the Unix time.

I don't know if the time in your data are LOCAL or UTC.

Unix time is a representation of the time SPAN from 1 Jan 1970 to the present.

If the time in your data are LOCAL time it's an easy thing to do. If the time in your data are not UTC and they were collected using the local time in a timezone different you the one in which you're in, then it becomes a real PITA. If you don't know how the dates are represented it becomes a real crapshoot.

Just something to keep in mind.

0 Votes 0 ·
LimitlessTechnology-2700 avatar image
0 Votes"
LimitlessTechnology-2700 answered

Hello @StevenCilia-5892,

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 : )

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.