Unanswered

Using Power Automate Desktop to convert XML files to Excel files

Like (0) Share

Report

Posted on 26 Jun 2021 02:12:37 by shikarishambu

Hi, I wanted to see if there is a way to convert XML files in a folder to the Excel files using Power Automate desktop. I see there is a way to iterate over files in a folder and read XML. What I seem to be missing is a way to convert XML to Excel/ write XML data to Excel. Any ideas?

Thanks

All responses (3)

Answers (0)

_STATION_ 2 on 22 Sep 2022 at 22:55:06

Like (0)

Report
Re: Using Power Automate Desktop to convert XML files to Excel files

I know this is an old thread but I'd like to chime in here for other lost users with an example that made quick work of this for me. It converts .xml to .csv but will also convert excel files to .csv tables.
If you would like to directly convert the file without having to iterate through all XML nodes in multiple loops, you can directly convert the file using a CMD script calling a .vbs file that does the transformation for you. It takes all but a few milliseconds to process.
What I did was
Create a blank .txt document (use notepad)
Add the following text to the text document (this is VBS scripting)
if WScript.Arguments.Count < 2 Then WScript.Echo "Please specify the source and the destination files. Usage: EXCELtoCSV <xls/xlsx source file> <csv destination file>" Wscript.Quit End If csv_format = 6 Set objFSO = CreateObject("Scripting.FileSystemObject") src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0)) dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1)) Dim oExcel Set oExcel = CreateObject("Excel.Application") Dim oBook Set oBook = oExcel.Workbooks.Open(src_file) oBook.SaveAs dest_file, csv_format oBook.Close False oExcel.Quit
Save the text document to your desktop & name it "ExceltoCSV" (I know this doesn't sound right but it works)
Change the extension of the text document from .txt to .vbs
In your Power Automate Desktop (PAD) process, add the action 'Run DOS command'
Set the properties of the DOS command as follows:
DOS command or application = EXCELtoCSV.vbs YourXMLFileName.xml YourXMLFileName.csv
(**Important here that you use only your XML file's name followed by the appropriate .csv or .xml)
Working Folder = c:\users\<Your username>\Desktop
Run the process and get your results
Important to note that wherever you are performing the conversion (DOS working folder, you place the .xml file there for conversion. Also, you need to ensure the .VBS file you created is also located in that folder. I only used "Desktop" as an example. I usually run this in my c:\foldername

Hope this helps someone one day.
tkuehara 667 on 28 Jun 2021 at 18:21:50

Like (0)

Report
Re: Using Power Automate Desktop to convert XML files to Excel files

You'll need to read the XML file, store its values into variables and then write those variables to an Excel file. Also, I'm assuming all your XML files have basically the same structure and you want the same output structure (same columns in Excel). Basically, you'll need to perform the following steps:
Read the XML file as a whole through "Read XML from file" action.
Get its values/attributes, using "Execute XPath expression" and/or "Get XML element value" and/or "Get XML element attribute" actions.
Generate a new Excel file with "Launch Excel"
Write the values captured from your XML file to your new Excel file via "Write to Excel worksheet"
This is a very high level generalization to guide you to the desired outcome.
Community Power Pla... on 27 Jun 2021 at 00:53:10

Like (0)

Report

Re: Using Power Automate Desktop to convert XML files to Excel files

Didi you try importing a xml to Excel?
1) Get the full path + name of XML file
2) Lauch Excel with a blank sheet
3) Navigate with UI elements to 'Developer' tab, then 'Import' under 'XML' group. In the select file window, use 'Populate text field in window' to fill the path&name of xml file captured in step 1 and click Import.
4) 'Save Excel' as xlsx file
Previously you have to show Developer tab in Excel (Show the Developer tab (microsoft.com))

Community site session details

Using Power Automate Desktop to convert XML files to Excel files

I know this is an old thread but I'd like to chime in here for other lost users with an example that made quick work of this for me. It converts .xml to .csv but will also convert excel files to .csv tables.

If you would like to directly convert the file without having to iterate through all XML nodes in multiple loops, you can directly convert the file using a CMD script calling a .vbs file that does the transformation for you. It takes all but a few milliseconds to process.

What I did was

Create a blank .txt document (use notepad)

Add the following text to the text document (this is VBS scripting)

Save the text document to your desktop & name it "ExceltoCSV" (I know this doesn't sound right but it works)

Change the extension of the text document from .txt to .vbs

In your Power Automate Desktop (PAD) process, add the action 'Run DOS command'

Set the properties of the DOS command as follows:

DOS command or application = EXCELtoCSV.vbs YourXMLFileName.xml YourXMLFileName.csv

(**Important here that you use only your XML file's name followed by the appropriate .csv or .xml)

Working Folder = c:\users\<Your username>\Desktop

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Leaderboard > Power Automate - Power Automate Desktop

Featured topics

Product updates

Community site session details

Using Power Automate Desktop to convert XML files to Excel files

I know this is an old thread but I'd like to chime in here for other lost users with an example that made quick work of this for me. It converts .xml to .csv but will also convert excel files to .csv tables.

If you would like to directly convert the file without having to iterate through all XML nodes in multiple loops, you can directly convert the file using a CMD script calling a .vbs file that does the transformation for you. It takes all but a few milliseconds to process.

What I did was

Create a blank .txt document (use notepad)

Add the following text to the text document (this is VBS scripting)

Save the text document to your desktop & name it "ExceltoCSV" (I know this doesn't sound right but it works)

Change the extension of the text document from .txt to .vbs

In your Power Automate Desktop (PAD) process, add the action 'Run DOS command'

Set the properties of the DOS command as follows:

DOS command or application = EXCELtoCSV.vbs YourXMLFileName.xml YourXMLFileName.csv

(**Important here that you use only your XML file's name followed by the appropriate .csv or .xml)

Working Folder = c:\users\<Your username>\Desktop

Helpful resources

News and Announcements

Quick Links

Understanding Microsoft Agents - Introductory Session

Warren Belz – Community Spotlight

Congratulations to the April Top 10 Community Stars!

Subscribe to this forum!

Select categories

Leaderboard > Power Automate - Power Automate Desktop

Featured topics

Product updates