Export xlsx file to XML

PowerShell Help
1

Hi Gurus !

I want to export data that are on an xlsx file (pets.xlsx) to xml format.

I used this code :

$xlXMLSpreadsheet = 46
$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("d:\pets.xlsx")
$WorkBook.SaveAs("d:\pets.xml", $xlXMLSpreadsheet)
$Excel.Quit()

The problem is that the generated xml file is not the data.

Here is the xlsx file (pets.xlsx)

Here is the desired output (pets_desired_output.xml)

Thanks in advance

Note : I’ve also posted the problem on Stackoverflow to maximize my chances to solve the problem.

2

Amine,
welcome to the forum.

May I ask if this works the way you expect it when you do it in the Excel GUI? Why / whatfor do you need the data in XML?

3

Hi Olaf. Thanks for answering.

Yes, when I use the Excel GUI, I save the file as XML (I choose the XML data in type) and It works well. Another option in the GUI is to export the file using the XML Export function in the Developer tab).

4

Unfortunately I don’t have Excel to test at the moment. What does the “46” mean in the “save” method you’re using?

And BTW:

When you crosspost the same question at the same time to different forums you should at least post links to the other forums along with your question to avoid people willing to help you making their work twice or more.

https://stackoverflow.com/questions/68137066/export-xlsx-file-to-xml-using-powershell

Thanks in advance

5

The “pure PowerShell” way actually works for me …

$CSV = 
@'
name,age,type,color
Amanda,10,dog,brown
Fox,4,dog,black
Bernard,12,dog,white
Jack,3,cat,black
Naia,1,cat,brown
Chip,14,cat,silver
Stop,5,pig,brown
Kiki,2,pig,brown
Mira,1,rabbit,white
Maya,1,rabbit,black
Olga,1,rabbit,white
'@ |
    ConvertFrom-Csv -Delimiter ','

$XML = ConvertTo-Xml -As Document -Depth 3 -InputObject ($CSV) -NoTypeInformation
$XML.Save("D:\sample\xml.xml")

The names of the nodes are a little different but the actual informations are there.

<?xml version="1.0" encoding="utf-8"?>
<Objects>
  <Object>
    <Property>
      <Property Name="name">Amanda</Property>
      <Property Name="age">10</Property>
      <Property Name="type">dog</Property>
      <Property Name="color">brown</Property>
    </Property>
    <Property>
      <Property Name="name">Fox</Property>
      <Property Name="age">4</Property>
      <Property Name="type">dog</Property>
      <Property Name="color">black</Property>
    </Property>
    <Property>
      <Property Name="name">Bernard</Property>
      <Property Name="age">12</Property>
      <Property Name="type">dog</Property>
      <Property Name="color">white</Property>
    </Property>
    <Property>
      <Property Name="name">Jack</Property>
      <Property Name="age">3</Property>
      <Property Name="type">cat</Property>
      <Property Name="color">black</Property>
    </Property>
    <Property>
      <Property Name="name">Naia</Property>
      <Property Name="age">1</Property>
      <Property Name="type">cat</Property>
      <Property Name="color">brown</Property>
    </Property>
    <Property>
      <Property Name="name">Chip</Property>
      <Property Name="age">14</Property>
      <Property Name="type">cat</Property>
      <Property Name="color">silver</Property>
    </Property>
    <Property>
      <Property Name="name">Stop</Property>
      <Property Name="age">5</Property>
      <Property Name="type">pig</Property>
      <Property Name="color">brown</Property>
    </Property>
    <Property>
      <Property Name="name">Kiki</Property>
      <Property Name="age">2</Property>
      <Property Name="type">pig</Property>
      <Property Name="color">brown</Property>
    </Property>
    <Property>
      <Property Name="name">Mira</Property>
      <Property Name="age">1</Property>
      <Property Name="type">rabbit</Property>
      <Property Name="color">white</Property>
    </Property>
    <Property>
      <Property Name="name">Maya</Property>
      <Property Name="age">1</Property>
      <Property Name="type">rabbit</Property>
      <Property Name="color">black</Property>
    </Property>
    <Property>
      <Property Name="name">Olga</Property>
      <Property Name="age">1</Property>
      <Property Name="type">rabbit</Property>
      <Property Name="color">white</Property>
    </Property>
  </Object>
</Objects>
6

Thanks for the solution.
But actually, I need the XML files to be as I’ve posted the desired output.
I have about 300 xlsx files that come from different regions to be imported into an Oracle DB.

The format you’ve provided doesn’t work for me.

7

I’m not a DBA but I’d expect an Oracle DB to be able to import either XLSX or CSV files directly wihtout the need to transform them to a certain format before.

You may take a look at the great module from Doug Finke ImportExcel.

8

Thanks. I’ve searched for it, and also contacted Douglas Finke, but I couldn’t use it to do the job…

9

The problem with the way you’re saving it is that you’re actually saving a spreadsheet defined in XML, not exporting the data as XML.

I think you can do it with a COM object but it looks a bit more complicated and I haven’t been able to figure it out with just a quick look. I think you need to create an XMLMap object and then use the Workbook’s saveAsXMLData() method, referencing the map, to export the data.

Sticking with PowerShell and the ImportExcel module, you could do something like this:

$data = Import-Excel E:\Temp\Files\pets.xlsx

$xmlFile = 'E:\Temp\Files\pets.xml'

$header = @'
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<pets xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
'@

$footer = @'
</pets>
'@

Set-Content -Value $header -Path $xmlFile

foreach ($d in $data) {

$xmlData = @"
    <pet>
		    <name>$($d.name)</name>
		    <age>$($d.age)</age>
		    <type>$($d.type)</type>
		    <color>$($d.color)</color>
    </pet>
"@

   Add-Content -Value $xmlData -Path $xmlFile

}

Add-Content -Value $footer -Path $xmlFile
2 Likes
10

Thank you very much matt-bloomfield.

I just discovered PowerShell it’s really powerful.

Thanks again

11

Hi Matt.
After little searches I succeeded to write this code

$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("d:\pets.xlsx")
$WorkBook.XmlMaps("pets_Mappage").Export("D:\pets1.xml")
$Excel.Quit()

Now I am looking for a way to Overwrite the file created in the case we replay the job.

I’ve wrote this :
$WorkBook.XmlMaps("pets_Mappage").Export("D:\pets1.xml"**, True)**

But it does not work.

Any help ?

12

Your code doesn’t work for me but I would try $true:

$WorkBook.XmlMaps("pets_Mappage").Export("D:\pets1.xml", $true)

If that doesn’t work you could fall back to Test-Path / Remove-Item for which I’d probably switch to a variable:

$path = 'D:\pets1.xml'
$Excel = New-Object -Com Excel.Application
$WorkBook = $Excel.Workbooks.Open("d:\pets.xlsx")
if (Test-Path $path) {
    Remove-Item $path
}
$WorkBook.XmlMaps("pets_Mappage").Export($path)
$Excel.Quit()