0

I am trying to convert a CSV document into an XML document for import into a directory. The CSV is formatted as follows:

 Firstname,Lastname,Email,Phone,Room,ID
 Bob,Smith,Bobs@email.com,1111,Suite 101,1
 John,Doe,John@email.com,2222,Suite 102,2

The desired XML output needs to be as follows:

<?xml version="1.0" encoding="UTF-8"?>
<TSP version="1.1">
    <contact firstname="Bob" lastname="Smith" email="Bobs@email.com" Phone="1111" room="Suite 101" id="1"/>
    <contact firstname="John" lastname="Doe" email="John@email.com" phone="2222" room="Suite 102" id="2"/>
</TSP>

Using information from this post Powershell CSV to XML I was able to get the following output which is close but missing some bindings:

<contact Firstname ="Bob" Lastname= "Smith Email= "Bobs@email.com Phone= "1111" Room= "Suite 101" Phone= "1111" />
<contact Firstname ="John" Lastname= "Doe Email= "John@email.com Phone= "2222" Room= "Suite 102" Phone= "2222" />

Any help is very much appreciated!

Script Used:

$docTemplate = @'
<contact $($contacts -join "`n") />
'@
$entryTemplate = @'
Firstname ="$($Phone.Firstname)" Lastname= "$($Phone.Lastname) Email= "$($Phone.Email) Phone= "$($Phone.Phone)" Room= "$($Phone.Room)" Phone= "$($Phone.Phone)"
'@

Import-Csv Test.csv -Delimiter ',' | Group-Object Id -ov grp | ForEach-Object {
  $contacts = foreach ($Phone in $_.Group) {
    $ExecutionContext.InvokeCommand.ExpandString($entryTemplate)  
  }
 $ExecutionContext.InvokeCommand.ExpandString($docTemplate) } | 
  Set-Content -LiteralPath file.xml
Ian Drumm
  • 1
  • 1
  • You need to edit your template to a) remove the space between `=` and the start of the attribute value (e.g., `Phone= "$($Phone.Phone)"` should be `Phone="$($Phone.Phone)"`, and b) add a binding for `ID`, which you left off from your template, just like the others demonstrate. – Ken White Feb 27 '20 at 01:50
  • Hi Ken, Thank you for your response, I do see now that I left a space there. I have fixed this in my script. As far as the ID binding im not sure what you're referencing to. I do not need my XML to display UserID. The script needs to convert the CSV into an XML that looks exactly like the desired outcome, with the XML version info and the TSP binding which I can not get to write outside the user's info. If I add this to my current template it duplicates it for each user, I believe this has to do with, foreach ($Phone in $_.Group), but I'm not that familiar with for each/foreach object. – Ian Drumm Feb 28 '20 at 06:33
  • You said *get the following output which is close but missing some bindings*, and the only missing binding I can see is that oyu forgot to bind `ID`, as I mentioned before. If you're having some problem other than that, it's not clear from your post. – Ken White Feb 29 '20 at 00:52

1 Answers1

0

Ian

There is a lot going on in this section that is making Troubleshooting and testing difficult.

Import-Csv Test.csv -Delimiter ',' | Group-Object Id -ov grp | ForEach-Object { $contacts =...

Suggest breaking this into individual statements with print statements in between. The goal is to figure it if the issue is with 1. Import-Cvs 2. Group-Object 3. Expand entryTemplate or the respective template 4. expand docTemplate. or the respective template

Another thought based on the issue with quotes in the output is the need to escape special characters like quotes. I haven’t done much I’m PowerShell but a back slash before the special character is one way of telling the parser that you want the quote to be treated as a quote character not as an instruction to define a string.

eshbaugh
  • 1
  • 1