# Export xlsx file to XML

**URL:** <https://forums.powershell.org/t/export-xlsx-file-to-xml/16927>\
**Category:** PowerShell Help\
**Created:** [June 25, 2021, 8:39pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927 "2021-06-25T20:39:02Z")\
**Posts on this page:** 13\
**Page:** 1

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 25, 2021, 8:39pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/1 "2021-06-25T20:39:02Z")

</div>

Hi Gurus !

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

I used this code :

```auto
$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)

> **[pets.xlsx](https://drive.google.com/file/d/1QPP_cVhfp4l5T1I4D5vzydjpDZwlsSn1/view?usp=sharing)**
>
> Google Drive file.

Here is the desired output (pets\_desired\_output.xml)

> **[pets\_desired\_output.xml](https://drive.google.com/file/d/1_Mjn08dYocj1dsaAggk8Cfi_oUXk3T6C/view?usp=sharing)**
>
> Google Drive file.

Thanks in advance

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

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 25, 2021, 8:47pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/2 "2021-06-25T20:47:52Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 25, 2021, 8:52pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/3 "2021-06-25T20:52:29Z")

</div>

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).

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 25, 2021, 9:16pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/4 "2021-06-25T21:16:13Z")

</div>

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](https://stackoverflow.com/questions/68137066/export-xlsx-file-to-xml-using-powershell)

Thanks in advance

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 25, 2021, 9:23pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/5 "2021-06-25T21:23:29Z")

</div>

The “pure PowerShell” way actually works for me …

```auto
$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.

```auto
<?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>

```

---

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 25, 2021, 9:28pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/6 "2021-06-25T21:28:22Z")

</div>

> [@Olaf](#):
>
> 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.

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.

---

<div class="post-metadata">

**Author:** ![Olaf](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.powershell.org/olaf/32/992_2.png) [@Olaf](https://forums.powershell.org/u/Olaf)\
**Post date:** [June 25, 2021, 9:36pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/7 "2021-06-25T21:36:31Z")

</div>

> [@Amine](#):
>
> to be imported into an Oracle DB

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.

> **[ImportExcel 7.8.6](https://www.powershellgallery.com/packages/ImportExcel/7.8.6)**
>
> PowerShell module to import/export Excel spreadsheets, without Excel.
> Check out the How To Videos https://www.youtube.com/watch?v=U3Ne\_yX4tYo&list=PL5uoqS92stXioZw-u-ze\_NtvSo0k0K0kq

---

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 25, 2021, 9:40pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/8 "2021-06-25T21:40:57Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![matt-bloomfield](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield)\
**Post date:** [June 25, 2021, 10:21pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/9 "2021-06-25T22:21:56Z")

</div>

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:

```auto
$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

```

---

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 26, 2021, 4:24pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/10 "2021-06-26T16:24:28Z")

</div>

Thank you very much [matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield).

I just discovered PowerShell it’s really powerful.

Thanks again

---

<div class="post-metadata">

**Author:** ![Amine](https://avatars.discourse-cdn.com/v4/letter/a/aeb1de/32.png) [@Amine](https://forums.powershell.org/u/Amine)\
**Post date:** [June 27, 2021, 6:40pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/11 "2021-06-27T18:40:10Z")

</div>

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

```auto
$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 ?

---

<div class="post-metadata">

**Author:** ![matt-bloomfield](https://avatars.discourse-cdn.com/v4/letter/m/dec6dc/32.png) [@matt-bloomfield](https://forums.powershell.org/u/matt-bloomfield)\
**Post date:** [June 27, 2021, 9:22pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/12 "2021-06-27T21:22:33Z")

</div>

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:

```auto
$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()

```

---

<div class="post-metadata">

**Author:** ![dotnVo](https://avatars.discourse-cdn.com/v4/letter/d/4af34b/32.png) [@dotnVo](https://forums.powershell.org/u/dotnVo)\
**Post date:** [May 16, 2024, 8:24pm UTC](https://forums.powershell.org/t/export-xlsx-file-to-xml/16927/13 "2024-05-16T20:24:00Z")

</div>