PowerShell - Convert from CSV to XML & update XML records

Question

I am trying to loop through a folder, import all CSV files, transform them to XML, change the XML values according to the CSV data and export them to a different folder while keeping the same name.

#CSV files format (No headers)

booker12,9012,Rachel,Booker
grey07,2070,Laura,Grey
johnson81,4081,Craig,Johnson
jenkins46,9346,Mary,Jenkins
smith79,5079,Jamie,Smith

#Desired XML file format (I would like to be able to pull the data from the CSV files straight into the XML values)

<?xml version="1.0"?>
<book>
      
      <title>booker12</title>
      <acode>9012</acode>
      <name>Rachel,Booker</name>
   </book>

I have managed to pick up all CSV files, move them to a folder and "rename to XML" using the code below.

# Folder containing source CSV files
$folderPath = 'C:\Users\somename\Desktop\FOLDER\Import\'

# Destination folder for the new files
$folderPathDest = 'C:\Users\somename\Desktop\FOLDER\Output\'

# Generate a list of all files in the folder and pipe it to ForEach-Object
Get-ChildItem $folderPath -Name |

# Loop through each file
ForEach-Object { 

    # Combines source folder path and file name
    $filePath = $folderPath + $_

    # Combines destination folder and file name
    $filePathdest = $folderPathDest + $_


    # Imports CSV file, exports as CSV to the desired destination
    Import-Csv $filePath |
    Export-Csv -Path $filePathDest –NoTypeInformation
}


#Converts all items from output folder to XML
Get-ChildItem -Path 'C:\Users\somename\Desktop\FOLDER\Output\' |
   ForEach-Object {
       (Get-Content -Path $_.FullName) |
            Set-Content -Path $_.FullName -Encoding UTF8
       Rename-Item -NewName "$($_.BaseName).xml" -Path $_.FullName
    }

I have found a code snippet with something similar and I've been playing around with it, but I am not sure how to integrate this in my current code. (Formatting xml fields from a csv with powershell)

foreach ($router in $routers) {

$router_hostname = $router.hostname
$router_ip = $router.ip

$xml = @"
<?xml version="1.0" encoding="UTF-8" standalone="true"?>
<root expanded="True" name="Test MSP" type="database" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<connection name=$router_hostname type="PuTTY">
<name>$router_hostname</name>
<protocol>Telnet</protocol>
<host>$router_ip</host>
<port>23</port>
<session>Default Settings</session>
<connectiontimeout>1500</connectiontimeout>
<logintimeout>1000</logintimeout>
<passwordtimeout>1000</passwordtimeout>
<commandtimeout>1000</commandtimeout>
<postcommands>False</postcommands>
</connection>
</root>
"@

Any help would be appreciated. Many thanks.

Theo · Accepted Answer · 2021-10-26 14:29:37Z

2

You can create the xml files in one single loop using Here-Strings like this:

# Folder containing source CSV files
$folderPath     = 'C:\Users\somename\Desktop\FOLDER\Import'
# Destination folder for the new files
$folderPathDest = 'C:\Users\somename\Desktop\FOLDER\Output'

# create a template Here-string for the XML (all <book> nodes need to be inside a root node)
$xmlTemplate = @"
<?xml version="1.0" encoding="utf-8"?>
<books>
@@BOOKNODES@@
</books>
"@

# and also a template for the individual <book> nodes
# inside are placeholders '{0}' we will fill in later
$bookTemplate = @"
    <book>
        <title>{0}</title>
        <acode>{1}</acode>
        <name>{2},{3}</name>
    </book>
"@

# Generate a list of all files in the folder and pipe it to ForEach-Object
Get-ChildItem -Path $folderPath -Filter '*.csv' -File | ForEach-Object { 
    # Combines destination path and file name with extension .xml
    $filePathdest = Join-Path -Path $folderPathDest -ChildPath ('{0}.xml' -f $_.BaseName)
    # Import the CSV file
    $data  = Import-Csv -Path $_.FullName -Header Title, Acode, FirstName, LastName
    $books = foreach ($book in $data) {
        # output a <book> section with placeholders filled in
        $bookTemplate -f $book.Title, $book.Acode, $book.FirstName, $book.LastName
    }
    # create the completed XML and write this to file
    $xmlTemplate -replace '@@BOOKNODES@@', ($books -join [environment]::NewLine) |
    Set-Content -Path $filePathdest -Encoding utf8
}

Output wil be xml files like this:

<?xml version="1.0" encoding="utf-8"?>
<books>
    <book>
        <title>booker12</title>
        <acode>9012</acode>
        <name>Rachel,Booker</name>
    </book>
    <book>
        <title>grey07</title>
        <acode>2070</acode>
        <name>Laura,Grey</name>
    </book>
    <book>
        <title>johnson81</title>
        <acode>4081</acode>
        <name>Craig,Johnson</name>
    </book>
    <book>
        <title>jenkins46</title>
        <acode>9346</acode>
        <name>Mary,Jenkins</name>
    </book>
    <book>
        <title>smith79</title>
        <acode>5079</acode>
        <name>Jamie,Smith</name>
    </book>
</books>

answered Oct 26, 2021 at 14:29

Theo

61.9k8 gold badges28 silver badges48 bronze badges

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

6 Comments

Jin Over a year ago

Hi, This works wonders except one thing. I am getting all the results comma separated in the first XML string and all the others are blank. <title>booker12,9012,Rachel,Booker</title>

Theo Over a year ago

@GabiTopciu Then your csv files are different from what you've posted.. Possibly, the do not use the comma as delimiter character, but some other character? Or every line is surrounded by quotes as a whole? Please provide accurate workable examples. As you can see, it works as expected for me.

Theo Over a year ago

@GabiTopciu then, why does it work for me? The screenshot reveals that also Excel cannot parse the file correctly as it puts every field in one cell. Open the csv in notepad to see what it really looks like.

Theo Over a year ago

@GabiTopciu It almost looks like you have surrounded the first data line with quotes, making it a single string..

Jin Over a year ago

apologies for this. Indeed you are right and the code works as intended. Thank you very much.

|

Collectives™ on Stack Overflow

PowerShell - Convert from CSV to XML & update XML records

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related