Powershell script CSV to XML

Question

Hi I am trying to convert a csv file into a single XML file

CSV Data:

CustomerNumber,FirstName,LastName,Address_1,Address_2,Address_3,City,State,zip
1,ABC,DEF,Street 1,Area 52,,Madurai,TN,123
2,DEF,GHI,Street 2,Area 53,demo,chennai,TN,321
3,GHI,JKL,Street 3,Area 54,,Bangalore,KA,456
4,JKL,MNO,Street 4,Area 55,demo2,Hyderabad,TA,5654
5,MNO,abc,Street 5,Area 56,,Delhi,DL,766

The expected XML file should be

<Content>
      <Customers>
          <Customer>
            <CustomerNumber>1</CustomerNumber>
            <FirstName>ABC</FirstName>
            <LastName>DEF</LastName>
            <Address_1>Street 1</Address_1>
            <Address_2>Area 52</Address_2>
            <Address_3></Address_3>
            <City>Madurai</City>
            <State>TN</State>
            <Zip>123</Zip>
          </Customer>
          <Customer>
            <CustomerNumber>2</CustomerNumber>
            <FirstName>DEF</FirstName>
            <LastName>GHI</LastName>
            <Address_1>Street 2</Address_1>
            <Address_2>Area 53</Address_2>
            <Address_3>demo</Address_3>
            <City>chennai</City>
            <State>TN</State>
            <Zip>321</Zip>
          </Customer>
          <Customer>
            <CustomerNumber>3</CustomerNumber>
            <FirstName>GHI</FirstName>
            <LastName>JKL</LastName>
            <Address_1>Street 3</Address_1>
            <Address_2>Area 54</Address_2>
            <Address_3></Address_3>
            <City>Bangalore</City>
            <State>KA</State>
            <Zip>456</Zip>
          </Customer>
          <Customer>
            <CustomerNumber>4</CustomerNumber>
            <FirstName>JKL</FirstName>
            <LastName>MNO</LastName>
            <Address_1>Street 4</Address_1>
            <Address_2>Area 55</Address_2>
            <Address_3>demo2</Address_3>
            <City>Hyderabad</City>
            <State>TA</State>
            <Zip>5654</Zip>
          </Customer>
          <Customer>
            <CustomerNumber>5</CustomerNumber>
            <FirstName>MNO</FirstName>
            <LastName>abc</LastName>
            <Address_1>Street 5</Address_1>
            <Address_2>Area 56</Address_2>
            <Address_3></Address_3>
            <City>Delhi</City>
            <State>DL</State>
            <Zip>766</Zip>
          </Customer>
      </Customers>
  </Content>

Code I Used:

$docTemplate = @'
<Content>
      <Customers>
$($ctms -join "`n")
      </Customers>
  </Content>
'@

$entryTemplate = @'
          <Customer>
            <CustomerNumber>$($ctm.CustomerNumber)</CustomerNumber>
            <FirstName>$($ctm.FirstName)</FirstName>
            <LastName>$($ctm.LastName)</LastName>
            <Address_1>$($ctm.Address_1)</Address_1>
            <Address_2>$($ctm.Address_2)</Address_2>
            <Address_3>$($ctm.Address_3)</Address_3>
            <City>$($ctm.City)</City>
            <State>$($ctm.State)</State>
            <Zip>$($ctm.zip)</Zip>
          </Customer>
'@


Import-Csv "sample.csv" -Delimiter ',' | Group-Object CustomerNumber -ov grp | ForEach-Object {
  $ctms = foreach ($ctm in $_.Group) {
    $ExecutionContext.InvokeCommand.ExpandString($entryTemplate)  
  }

  $ExecutionContext.InvokeCommand.ExpandString($docTemplate)
} | Set-Content -LiteralPath { $ctm.CustomerNumber +'.xml' }

the above code is working fine but creates different XML files for each customer.

Could you please help me to modify this code to create an XML file which is having all the customer data into a single file

--------------Updated the Question after the first Answer -------------

I have updated the set-content to a single file name as below

Set-Content -LiteralPath 'sample.xml'

still it is not correct it is printing like below

<Content>
      <Customers>
          <Customer>
            <CustomerNumber>1</CustomerNumber>
            <FirstName>ABC</FirstName>
            <LastName>DEF</LastName>
            <Address_1>Street 1</Address_1>
            <Address_2>Area 52</Address_2>
            <Address_3></Address_3>
            <City>Madurai</City>
            <State>TN</State>
            <Zip>123</Zip>
          </Customer>
      </Customers>
</Content>
<Content>
      <Customers>
          <Customer>
            <CustomerNumber>2</CustomerNumber>
            <FirstName>DEF</FirstName>
            <LastName>GHI</LastName>
            <Address_1>Street 2</Address_1>
            <Address_2>Area 53</Address_2>
            <Address_3>demo</Address_3>
            <City>chennai</City>
            <State>TN</State>
            <Zip>321</Zip>
          </Customer>
      </Customers>
</Content>
<Content>
      <Customers>
          <Customer>
            <CustomerNumber>3</CustomerNumber>
            <FirstName>GHI</FirstName>
            <LastName>JKL</LastName>
            <Address_1>Street 3</Address_1>
            <Address_2>Area 54</Address_2>
            <Address_3></Address_3>
            <City>Bangalore</City>
            <State>KA</State>
            <Zip>456</Zip>
          </Customer>
      </Customers>
</Content>
<Content>
      <Customers>
          <Customer>
            <CustomerNumber>4</CustomerNumber>
            <FirstName>JKL</FirstName>
            <LastName>MNO</LastName>
            <Address_1>Street 4</Address_1>
            <Address_2>Area 55</Address_2>
            <Address_3>demo2</Address_3>
            <City>Hyderabad</City>
            <State>TA</State>
            <Zip>5654</Zip>
          </Customer>
      </Customers>
</Content>
<Content>
      <Customers>
          <Customer>
            <CustomerNumber>5</CustomerNumber>
            <FirstName>MNO</FirstName>
            <LastName>abc</LastName>
            <Address_1>Street 5</Address_1>
            <Address_2>Area 56</Address_2>
            <Address_3></Address_3>
            <City>Delhi</City>
            <State>DL</State>
            <Zip>766</Zip>
          </Customer>
      </Customers>
</Content>

No, it is not working and I have updated my question with Updated result I am getting using the given solution — Venkatesh Kopula Sivakumar, Aug 26 '21 at 12:51

mklement0 · Answer 1 · 2021-08-26T13:32:39.303

Passing a script block ({ ... }) to Set-Content's -LiteralPath parameter is a technique known as delay-bind script-block parameters, in which case the target parameter's value is calculated for each input object, based on the script block's output.

In the answer you took the code from, that is the very intent: write to a different file for each input object, based on a property value of the input object.

By contrast, you want to write to a single output file for all input objects - as is typical - and for that you simply pass a string - the output file path - rather than a script block; e.g.:

... | Set-Content -LiteralPath AllCustomers.xml

If $ctm.CustomerNumber +'.xml' is the desired file name, use
Set-Content -LiteralPath ($ctm.CustomerNumber +'.xml') or - via an expandable string:
Set-Content -LiteralPath "$($ctm.CustomerNumber).xml"

Additionally, to structure the output XML as shown in your question, you need to collect all expanded customer templates first, and then use them as a single array when expanding the document template, outside the loop. The result can then be sent to a file:

$docTemplate = @'
<Content>
      <Customers>
$($ctms -join "`n")
      </Customers>
  </Content>
'@

$entryTemplate = @'
          <Customer>
            <CustomerNumber>$($ctm.CustomerNumber)</CustomerNumber>
            <FirstName>$($ctm.FirstName)</FirstName>
            <LastName>$($ctm.LastName)</LastName>
            <Address_1>$($ctm.Address_1)</Address_1>
            <Address_2>$($ctm.Address_2)</Address_2>
            <Address_3>$($ctm.Address_3)</Address_3>
            <City>$($ctm.City)</City>
            <State>$($ctm.State)</State>
            <Zip>$($ctm.zip)</Zip>
          </Customer>
'@


$ctms = Import-Csv "sample.csv" | Group-Object CustomerNumber -ov grp | ForEach-Object {
  foreach ($ctm in $_.Group) {
    $ExecutionContext.InvokeCommand.ExpandString($entryTemplate)  
  }
}

$ExecutionContext.InvokeCommand.ExpandString($docTemplate) |
  Set-Content -LiteralPath AllCustomers.xml

Powershell script CSV to XML

1 Answers1