1
<properties>
<entry key="type">exSafe:Nocontract</entry>
<entry key="aspects">cm:version,cm:title,cm:own,cm:author</entry>
<entry key="cm:creator">1122334455</entry>
<entry key="cm:autoVersion">true</entry>
<entry key="cm:title">XXXXXXXXXXXXXXX</entry>
<entry key="cm:modifier">1122334455</entry>
<entry key="cm:created">2013-07-28T08:30:51.000+01:00</entry>
<entry key="gwSafe:contractCustVend">XXXXXXXXXXXXXXXXXXX999</entry>
</properties>

I have following XML file and I would like to convert them into Excel file through PowerShell, Can anyone help me, please?

Here is what I have so far:

[xml]$inputFile = Get-Content "k1.xml" 
#export xml as csv
$inputFile.Sites.ChildNodes | Export-Csv "k1.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8
3
  • SO is NOT a script writing service. Own effort is required to get help. Can you provide us with a code sample of what you have already tried? A tip I can give to to script this yourself : to parse an XML you can use the [xml] type in Powershell. To export to Excel you can hook up the Excel COM object. What I've also noticed : your XML sample is incomplete, the Properties element is not being closed. Commented Mar 16, 2017 at 10:38
  • First, I am new to Poweshell and second, I post my question once I failed to do so with my own script. Thanks anyways! [xml]$inputFile = Get-Content "k1.xml" #export xml as csv $inputFile.Sites.ChildNodes | Export-Csv "k1.csv" -NoTypeInformation -Delimiter:";" -Encoding:UTF8 Even I have tried to convert the XML file into CSV, but that did not work too. Commented Mar 16, 2017 at 10:41
  • Welcome to the site Tuhin, I have updated your question for you to include your code snippet, in future always include that so we can see what you have attempted/failed on - this will help you become better and learn other ways of coding in PowerShell. You can refer to this site for how to ask good questions: stackoverflow.com/help/how-to-ask Commented Mar 16, 2017 at 10:55

1 Answer 1

2

I've written a small sample to get you started. This contains the basic logic. I didn't include the code to add the headers (since this is quite trivial to accomplish and this also leaves something to do yourself)

[xml]$XML = @'
<?xml version="1.0" encoding="UTF-8"?>
 <!DOCTYPE properties SYSTEM "http://java.sun.com/dtd/properties.dtd">
<properties>
<entry key="type">exSafe:Nocontract</entry>
<entry key="aspects">cm:version,cm:title,cm:own,cm:author</entry>
<entry key="cm:creator">1122334455</entry>
<entry key="cm:autoVersion">true</entry>
<entry key="cm:title">XXXXXXXXXXXXXXX</entry>
<entry key="cm:modifier">1122334455</entry>
<entry key="cm:created">2013-07-28T08:30:51.000+01:00</entry>
<entry key="gwSafe:contractCustVend">XXXXXXXXXXXXXXXXXXX999</entry>
</properties>
'@

## Create Excel COM Object
$excel = New-Object -ComObject Excel.Application
# Set the sheet to visible so you can actually see it (for testing purposes)
$excel.Visible = $true
# Create a workbook
$workbook = $excel.Workbooks.Add()
# Get sheet 1
$sheet = $workbook.Worksheets.Item(1)
# Set a counter
$x = 1;
# here we loop through the XML data and add this to the excel sheet
foreach ($entry in $XML.properties.entry)
    {
        $sheet.Cells.Item($x,1) = $entry.Key
        $sheet.Cells.Item($x,2) = $entry.'#text'
        $x++
    }
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.