PowerShell - Convert from CSV to XML & update XML records-CodePudding

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.

CodePudding user response：

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>