How to Convert XML to Excel (XLSX & XLS)? Step-by-Step Guide

Users convert XML to Excel to transform XML data into XLSX spreadsheet. Because XML stores information in a structured, tag-based format, making it ideal for data exchange between applications, while XLSX files are used for data analysis, reporting, filtering, and visualization. Basically, users can easily manage and organize complex XML data into rows and columns.

The question arises, is it possible to convert XML to XLSX / XLS? Yes, we can do it using Power Query, automatic conversion, manual mapping, using Python language, XML Schema (XSD), XSLT Transformation and an online and offline XML to XLSX converter. We’ve covered each method in detail below.

This stepwise guide will explain the complete information and step-by-step guide to import XML to Excel XLSX / XLS formats. Before knowing the methods, let’s understand what the common challenges with XML files are.

Common Challenges with XML Files

Before conversion, users often face these issues with XML files, which compels users to convert XML to XLS / XLSX formats:

• Complex structure and deeply nested files.
• Large file size.
• Missing tags and invalid files
• Difficulty in mapping data into rows and columns

How to Convert XML to XLS / XLSX Format Manually?

You can easily convert XML to Excel using Power Query, automatic conversion, manual mapping, using Python language, XML Schema (XSD), XSLT Transformation and an online and offline XML to XLSX converter. Let’s start the process one by one:

Method 1. Power Query

Power Query is a built-in feature in Excel that allows you to import, transform and convert XML data into Excel workbook without requiring any other software.

1. Open MS Excel and create a new blank workbook.
2. Navigate “Data” tab, choose “Get Data” option.
3. Select the “From File” >> “From XML” option.
4. Browse your XML file that you want to convert.
5. Select your file and choose “Import” button.

Now Excel opens a Power Query Editor.

• Navigator pane will display the XML structure and available tables.
• Choose the table or elements containing the data you want to convert.
• Select Transform Data to modify data before loading it into Excel sheet.

If your XML file contains nested elements then:

1. Locate columns with “Expand (double-arrow)” icons.
2. Choose the icon to view available fields.
3. Select the required fields to include.
4. Hit the “OK” button to flatten the XML structure into columns.

Once the data appears correctly:

1. Choose “Home” then “Close & Load” option.
2. Finally, “Power Query” loads XML data into new worksheet as an Excel table.
3. Now you can save this as XLSX format.

Method 2: Import XML to Excel (Automatic Conversion)

1. Launch your Excel application.
2. Choose the “File” tab from the top.
3. And then, select the “Open” >> “Browse” options.
4. Change the file type to the “All Files” option.
5. Select your XML file and choose the “Open” icon.
6. Choose the “As an XML table” option.
7. After that, Excel will map the data in rows and columns automatically.
8. Lastly, save your file as .xlsx format from “File” >> “Save As” >> “Excel Workbook (XLSX)”.

Method 3: Convert XML to Excel via Manual Mapping

1. Go to the “File” tab in the Excel sheet.
2. Choose the “Options” >> “Customize Ribbon” >> “Developer” tabs.
3. Choose the source in the XML Group.
4. Now drag the XML file elements manually into the cells.
5. Lastly, save your file in .xlsx format to convert XML to XLSX without opening file.

Also Read: How to Fix the “Unable to Open XML File” Issue?

Method 4: Convert XML to XLSX using Python Language

Step 1: Open Command Prompt / Terminal and run.

pip install pandas lxml openpyxl

Step 2: Next, prepare your XML file.

Place your XML file in a folder (for example):

C:\xml-to-excel\

├── data.xml

└── convert.py

Step 3: Create a Python Script

Create a file named convert.py and open it in Notepad or any editor.

Step 4: Read the XML file using Python

Use the given code to import XML data:

import pandas as pd

# Load XML file

df = pd.read_xml(“data.xml”)

print(df)

Step 5: Now export XML file data to Excel by adding the code below the previous code.

df.to_excel(“output.xlsx”, index=False)

Step 6: Run the Python script.

Open Command Prompt inside your folder and run:

python convert.py

Step 7: Finally, verify the Excel output.

Method 5: Using an XML Schema (XSD)

An XSD file stands for XML Schema Definition helps to define the structure of XML document including its elements, attributes, data types, and relationships. It can map data more accurately into rows and columns when MS Excel allows you to import XML file with an XSD schema.

1. Head to the “File” >> “Options” >> “Customize Ribbon” in Excel.
2. Enable the Developer checkbox.
3. Hit on the “OK” button.
4. Now navigate to the “Developer” pane.
5. Choose Source in the XML section.
6. Now the XML source pane will appear on the right side.
7. Select “XML Maps” from “XML Source Pane”.
8. Choose “Add” option.
9. Now browse your XSD file and click “Open”.
10. Now Excel will create an XML map based on the schema.
11. Drag XML elements from the XML Source pane onto the worksheet.
12. Next, head to the “Developer” > “Import”.
13. Choose your XML file linked with schema.
14. After loading data, you can save the file in .xlsx format.

Method 6: Convert XML to Excel Using XSLT Transformation

XSLT (Extensible Stylesheet Language Transformations) is a language which is used to transform XML documents into formats such as HTML, CSV or Spreadsheet-compatible XML. It is mainly useful when XML data contains deeply nested elements that Excel cannot easily interpret.

1. Firstly, create a XSLT stylesheet and name it transform.xsl.
2. Now use XML editor, browser, PowerShell, or an XSLT processor to transform XML file.
3. And now output will be in HTML table.
4. Now open this generated file in Excel.
5. MS Excel will import the table automatically.
6. Next, save it as XLSX format.

Method 7: XML to Excel Converter for Mac & Windows

For large, nested, and invalid XML files, you should use the given solution. If you are not comfortable with manual methods and have large and multiple XML files, then download the mentioned software. This XML to XLSX converter gives you a 100% guarantee of complete and safe XML to Excel conversion. It offers different types of features and settings, which make the software the first preference of all experts to convert XML to XLSX / XLS without opening a file.

It provides a simple and smooth process without requiring complex coding and expertise in programming languages. Also, the XML to XLS converter tool saves your valuable time and effort and prevents data loss situations. Try the demo edition for more information.

How to Convert XML to Excel Sheet? Working Steps

1. Firstly, download the software through the given buttons and install the XML to XLSX converter on your system.

   

2. Import your XML files and folders to the software using the Add Files and Add Folders options.

   

3. After selecting the required XML files/folders, choose the Next icon to continue the process.

   

4. Now select the XLSX option from the saving list and browse the location to save the output data file using the Change icon.

   

5. Lastly, choose the Export button to convert XML to XLSX / XLS format. And choose the OK button to confirm the process.

   

Method 8: Online XML to XLSX Converter

If you have one or two XML files that don’t contain crucial information, then you can choose any online XML to XLSX converter tool to convert XML to Excel spreadsheet. However, when choosing them, keep these points in mind.

• Uploading your XML file data to a third-party server is not safe for your data.
• You can’t convert a large XML file with the online XML to XLSX converter.
• These tools can work with simple and plain XML files, which means nested files can’t be supported.
• Also, a strong Internet connection is required.

Final Words

This write-up provided the best ways to convert XML to XLSX / XLS without opening file. You can easily import XML to Excel formats using the built-in direct option and the Power Query option. If you are a technical person, then you should go for the Python option. An automatic solution called XML to XLSX converter is the best option for those who don’t have any technical knowledge and have multiple large XML files to convert XML to Excel in bulk. If you have any queries or want any changes, then contact us through live chat support.