Converting a CSV file into appropriate XML format

Question

I'm trying to get some tab-delimited CSV files into an XML via PowerShell, and I'm struggling to make sense of it all. I'm 100% sure it's something really simple I'm missing, so help would be massively appreciated. I'm very close but just can't get over the final hurdle.

I essentially want this script to iterate through a tab-delimited CSV it's pointed at ($path), take each line off of the CSV and put the data into an XML sheet ($xmlpath).

CSV example:

"Name"  "Item"  "Purchase Price"
"Jimmy Smits"   "Egg"   "£40"
"Edward Price"  "KumQuat"   "£6000"
"Timmy Mallet"  "Bug"   "£2"
"Edgar Allen Poe"   "Weird" "0"
"2Pac Shakur"   "Eggnog"    ""

XML output template example:

<registration>
    <Individual>
        <Name></Name>
        <Item></Item>
        <Purchase Price></Purchase Price>
    </Individual>
</registration>

And this is what I've been poking about with

$data = Import-Csv -Path $path -Delimiter "`t"

$xmlnames = $data | Select-Object "Name"
$xmlitems = $data | Select-Object "Item"
$xmlprice = $data | Select-Object "Purchase Price"

$entryTemplate = @'
<individual>
    <Name>$($xmlnames.Name)</Name>
    <Item>$($xmlitems.Item)</Item>
    <Purchase Price>$($xmlprice."Purchase Price")</Purchase Price>
</individual>
'@

$xml = $data | ForEach-Object {
    $ExecutionContext.InvokeCommand.ExpandString($entrytemplate)
}
$xml | Out-File $xmlpath

Which then puts out;

<Individual>
    <Name>Jimmy Smits Edward Price Timmy Mallet Edgar Allen Poe 2Pac Shakur</Name>
    <Item>System.Object IList.Item(int index) {get;set;}</Item>
    <Purchase Price>£40 £6000 £2 0 </Purchase Price>
</Individual>

So somewhere I'm messing up stupidly on two things;

The $data | Select-Object 'thing' is giving me all of the list of 'things' rather than iterating through each of the 'things' and listing them one per XML entry. Yet is giving me the correct number of entries?
My 'Items' are being listed as system objects (not values), despite being treated identically to everything else.

when you type $data[0] ...it should output the first row of the csv...if not, the csv is not well-formed... make sure it is actually a csv file. Then proceed with other steps. — Kiran Reddy
– Kiran Reddy, Commented Mar 27, 2019 at 18:03

Ansgar Wiechers · Accepted Answer · 2019-03-27 18:11:08Z

The three statements

$xmlnames = $data | Select-Object "Name"
$xmlitems = $data | Select-Object "Item"
$xmlprice = $data | Select-Object "Purchase Price"

give you lists of custom objects with just one property. Using these lists in your template inserts all values into a tag with each iteration. What you want is just the value from the current iteration, hence creating the lists before doesn't do you any good. What you actually want is the current object during each iteration of the loop.

In case of $xmlitems you're also running into a PowerShell gotcha, because the variables $xmlnames, $xmlitems, and $xmlprice are arrays. When using dot-access on an array PowerShell is doing something that is called member enumeration. Essentially $xmlnames.Name won't give you the value of the property Name of the array object, but the value of the property Name of all array elements.

Member enumeration only works if the array object itself doesn't have a member of that name, though. Which brings us back to $xmlitems. For that variable you're trying to get the value of the property Item of the array elements. However, the array object actually has a method Item(), so $xmlitems.Item tries to invoke that method instead of the Item property of the array elements. But since the method is overloaded and the invocation is missing parentheses ($xmlitems.Item instead of $xmlitems.Item()) PowerShell shows you the method definition instead.

Remove the Select-Object statements and replace the array variables in your XML fragment template with the "current object" variable $_ and the code will do what you want.

$entryTemplate = @'
<individual>
    <Name>$($_.Name)</Name>
    <Item>$($_.Item)</Item>
    <Purchase Price>$($_."Purchase Price")</Purchase Price>
</individual>
'@

'Doh' of course! I was sure the select-objects were causing the multiple selections, but I couldn't wrap my head around the methods/arrays. Thanks for taking the time to explain it properly for me, this is now working perfectly!

rokumaru · Accepted Answer · 2019-05-22 04:51:42Z

1

You can also use "LINQ to XML" to create xml.

using namespace "System.Xml.Linq"
Add-Type -AssemblyName System.Xml.Linq

[XElement]::new("registration", @(
    Import-Csv -Path $path -Delimiter "`t" | foreach {
        [XElement]::new("Individual",
            [XElement]::new("Name", $_.Name),
            [XElement]::new("Item", $_.Item),
            [XElement]::new("PurchasePrice", $_."Purchase Price"))
    }
)).Save($xmlpath)

edited May 22, 2019 at 4:51

answered Mar 27, 2019 at 19:01

rokumaru

1,2441 gold badge8 silver badges11 bronze badges

Collectives™ on Stack Overflow

Converting a CSV file into appropriate XML format

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related