Learn How To Convert XML To Excel In Minutes

XML is a great way for systems and apps to work with, but it can be difficult for people because we don’t understand XML data very easily.

Excel, on the other hand, is a popular spreadsheet application which is designed to make working with data much easier for humans. So converting XML data into Excel makes sense!

There are two main ways to convert XML files into an Excel spreadsheet. One is to use a converter tool, or by manually editing the file. 

Converter Tools

Converter tools are pieces of software which can take an XML file and turn it into another format – in this case, an .xls or .xlsx file which can be opened in Excel.

The advantage of using a converter tool is that it’s usually much faster and easier than doing it yourself. All you need to do is download the converter, open your XML file, select the output as an Excel file, and then click convert. 

Your file will be ready in a few seconds or minutes, depending on its size.

Manually Editing The File

The other way to convert XML to Excel is by manually editing the file. This is a more complex process, but it gives you more control over the final outcome.

To do this, you’ll need to open the XML file in a text editor (we recommend using Notepad++)

Once you have the file open, you’ll need to find and replace all of the tags with commas (which can be cumbersome), for example, if your XML file looks like this:

[insert example here]- this will presumably require a screenshot, we don’t provide these for standard content

You ‘ll need to replace all of the “<” symbols with commas, so that it looks like this:

[insert example here]- this will presumably require a screenshot, we don’t provide these for standard content

Then you can save the file as a .csv file, which can be opened in Excel.

Manually editing an XML file is definitely more complex than using a converter tool, but it does give you more control over the final outcome!

What Is An XML File?

Extensible markup language (EML) is a file that can be used for storage and transportation purposes. The file consists of tags and text. Tags give structure to the data stored in the file, while the text is the core part of the document.

Take a look at the example below:

<employees list>
<employee id=”1”>
<FirstName>Molly</FirstName>
<LastName>Copoc</LastName>
<certificate>True</certificate>
<scores>
<module1>50</module1>
<module2>70</modeul2>

The file contains tags and texts. The plain text is the data that is being stored, while the tags indicate what the data is. This example shows the XML is storing employee names and their test scores.

XML Data Types

A data type is an attribute of data that informs a computer system how to interpret its value. There are many different XML data types, and all have different values. Take a look at some examples below:

Boolean

Boolean, also known as Bool, is used to represent logical values. The two values it usually denotes are true or false. Boolean expressions use common operators, which are simple words like OR, AND, XOR, and NOT as conjunctions. These words are used to either combine or exclude words in a keyword search so users can access results that are more focused and productive. 

Integer

Integer is a data type used for whole numbers and is commonly used to store numbers without a fractional component. Examples of this data type include 33, 21, and 55. The whole number can be zero, negative, or positive. So, -456 would be valid integers. However, 4.2 would not as it is not a whole number. 

Decimal

The decimal data type is exact numeric data defined by its total number of digits and the number of digits to the right of the decimal points (precision and scale). You would tend to use this data type if you wanted to store or complete operations on precise decimal values. 

String

String, also known as str or text, is used for alphanumeric characters. It is a popular data type that is used in programming. However, it represents text rather than numbers. A string is a set of characters that contains characters and often numbers too. For example, the word “cake” and the phrase “I baked 3 cakes” are both string data type examples. 

Duration

This data type represents the difference between one point in time to another. It is intended for use with time durations measured in seconds, minutes, hours, or days. 

DateTime

DateTime stores a value that contains both date and time parts together. It allows programmers to store dates from 01/01/1753 to 31/12/9999 and time information from 00:00:00.000 to 23:59:59.997. It is ideal if you want to serve your data together regardless of different time zones. 

Date

This data type is the most self-explanatory of the group. It stores a date in the following format – YYYY/MM/DD. 

Time

This stores time in an hours, minutes and seconds format. This data type can also be used to store time intervals between two events. 

Importing An XML Data File

• Open the Excel file that you want to import your XML data into then select the “data” tab.
• Click on the “Get Data” section. A list should appear. 
• Click the “From file” option and from there click on “From XML”.
• An input data dialogue box will open up. This is where you will locate your XML file that you wish to import. Once you find it, select the file.  
• As you click import the file will import into the power query and the Navigator dialogue box will open.
• Select the data that you wish to import from your XML file. 
• Click on load and you are finished. 

How To Convert XML To An Excel File

• Open Microsoft Excel and click “File” on the top left of the screen. 
• Click open to view your files and double click the XML file you wish to convert. 
• Again, click “File” and select the “Save as” button. 
• Find the file in the folder you have saved it in. 
• Click on the file and select “Excel workbook” from the “Save as type” in the drop-down menu.
• Once you click save your XML file will now be converted to an Excel file. 

FAQS