Issue implementing PowerShell XML to CSV solution

Question

I found a solution to converting XML to CSV using PowerShell I believe is workable for my needs in this question.

The solution example code looks like this:

$xml = [xml](Get-Content .\test.xml)

$xml.root.item | Select-Object @(
@{l="short_version";e={$_.short_version."#text"}},
@{l="long_name";e={$_.long_name."#text"}},
@{l="api_name";e={$_.api_name."#text"}},
@{l="long_version";e={$_.long_version."#text"}},
@{l="latest_stable_version";e={$_.latest_stable_version."#text"}},
@{l="automation_backend";e={$_.automation_backend."#text"}},
@{l="os";e={$_.os."#text"}}) |
Export-Csv test.csv -NoTypeInformation

However, after making the appropriate substitutions for my source XML, I'm merely generating a CSV with a row of headers followed by rows of commas without values.

My source XML looks like this:

<Report>
    <Columns>
        <Col_Name>TimeStamp</Col_Name>
        <Col_Name>MessageText</Col_Name>
        <Col_Name>EventNotificationId</Col_Name>
        <Col_Name>OperatorText</Col_Name>
        <Col_Name>TimeOfAck</Col_Name>
        <Col_Name>InitDeviceId</Col_Name>
    </Columns>
    <Record>
        <TimeStamp><![CDATA[10/1/2018 1:34:06.99 PM]]></TimeStamp>
        <MessageText><![CDATA[OnLine]]></MessageText>
        <EventNotificationId><![CDATA[Path\Text]]></EventNotificationId>
        <OperatorText><![CDATA[]]></OperatorText>
        <TimeOfAck><![CDATA[1/1/1989]]></TimeOfAck>
        <InitDeviceId><![CDATA[Path\Text]]></InitDeviceId>
    </Record>
    <Record>
        ~
    </Record>

(I have substituted Path\Text in a couple places for discretion.)

And my trial code with substitutions looks like this:

$xml = [xml](Get-Content C:\Test\test.xml)

$xml.Report.Record | Select-Object @(
@{l="TimeStamp";e={$_.TimeStamp."#text"}},
@{l="MessageText";e={$_.MessageText."#text"}},
@{l="EventNotificationId";e={$_.EventNotificationId."#text"}},
@{l="OperatorText";e={$_.OperatorText."#text"}},
@{l="TimeOfAck";e={$_.TimeOfAck."#text"}},
@{l="InitDeviceId";e={$_.InitDeviceId."#text"}}) |
Export-Csv C:\Test\test.csv -NoTypeInformation -Force

What do I need to change to make this solution work for me?

A final thought that occurs to me now is the proprietary data type CDATA may be unreadable using my current code. If that is responsible, I'm not sure how to parse the plain text string from it. Also FWIW, I could optionally get the source data as an HTML document, but from my quick research it appears easier to script the conversion to CSV from a source XML file.

Thank you.

zdan · Accepted Answer · 2018-10-31 17:39:53Z

1

The xml parser will put your CDATA sections in a member called #cdata-section, not #text. So your fix is simple:

$xml.Report.Record | Select-Object @(
    @{l="TimeStamp";e={$_.TimeStamp."#cdata-section"}},
    @{l="MessageText";e={$_.MessageText."#cdata-section"}},
    @{l="EventNotificationId";e={$_.EventNotificationId."#cdata-section"}},
    @{l="OperatorText";e={$_.OperatorText."#cdata-section"}},
    @{l="TimeOfAck";e={$_.TimeOfAck."#cdata-section"}},
    @{l="InitDeviceId";e={$_.InitDeviceId."#cdata-section"}}) |
Export-Csv C:\Test\test.csv -NoTypeInformation -Force

edited Oct 31, 2018 at 17:39

answered Oct 31, 2018 at 17:24

zdan

29.6k7 gold badges62 silver badges73 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Wilson Over a year ago

I changed my code up to @{l="TimeStamp";e={$_.TimeStamp.![CDATA["#text"]]}} and it appears to be running so far without errors. The source is probably thousands of lines so I'm not sure yet if it's actually working. Do you think this won't work and I should just cancel and try your method?

Wilson Over a year ago

Disregard my above comment. I went ahead and tried your suggested changes and it worked flawlessly. Thank you!

Collectives™ on Stack Overflow

Issue implementing PowerShell XML to CSV solution

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related