Convert Excel files to XML and vice versa

A little about Excel and XML

So, before we learn how to open, create, or translate an XML file to and from Excel, let's take a closer look at the types of these files. Here it should be immediately noted that Excel documents have many formats, while XML is the file format. Therefore, you should not confuse these two concepts.

Microsoft Excel is a powerful calculation program with many useful features

Microsoft Excel is a special utility designed for creating and editing tables. With them you can already do whatever you want: create databases, charts, formulas and other types of data. This is a very powerful tool, so we will not discuss all its capabilities. Today our goal is somewhat different, and we will not deviate from the topic of today's discussion.

XML files, for easier understanding, are designed for storing and exchanging various data between utilities. This markup language is especially often used when transmitting information over the Internet. And when you need to transfer tabular data from Excel to a website, for example, you will need to transform your document so that it displays correctly. Therefore, let's not delay the moment of truth and immediately begin the operation.

How to Convert Excel to XML

Dear friends, in fact, converting files is quite a simple task. However, some users may have problems transferring data, so today we will look at several ways to implement our plan. Therefore, we must be patient and proceed to action:

1. The simplest method, which may not work in some cases, is to save the file in the desired format in Microsoft Excel itself. To do this, open the required document in this application. Then click on the “File” button (discussed using Office 2013 as an example, in other versions the saving process may be slightly different) in the upper left corner of the Excel window, then “Save As”, then select the location to save the document, give it a name and in the tab Specify the file type as “XML data”. After that, check if everything worked out correctly for you. If not, then try the following methods.
2. So, if the error in the previous case occurs, then perhaps your copy of the program does not provide support and the corresponding add-ons. To install them, go to the Microsoft website https://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=3108, download and install this file on your computer or laptop. Next, launch the Microsoft Excel program and click on the large colored button in the upper left corner of the application window (here we are already looking at an example on the 2007 version). In the block that appears, click on the parameters to the left of exiting the utility. Now go to the add-ons item and click on the “Go” button opposite the “Manage” item. A dialog box will pop up where you need to click on the review and find the document that you downloaded from the Microsoft website earlier. Now click OK and go to the main program window, where you now have the XML Tools tab - with it you can now easily create an XML file from an existing table. You need to save it in the same way as the previous point.
3. If you don’t want to load both yourself and your computer with such information, you can resort to using special converter utilities or online versions of these programs. For example, you can download the Format Factory application, which will allow you to perform all the necessary actions. But it is worth remembering that files saved and converted in this way do not always work in the future. That is, during the operation, sectors or areas in the document’s memory may be damaged, which will negatively affect it - it simply may not open in an XML editor, for example. Therefore, it is recommended to use either professional programs or Microsoft Excel itself.

Trick #95. Loading an XML document into Excel

If someone sends an XML file containing data in tables, you won't have to read all the text and all the tagged angle brackets. You can load this document directly into Excel, tell Excel how to display the document, and work with the data using maps. In the last few years, XML (Extensible Markup Language) has become a common format for exchanging information, and it is not unusual for people and organizations to send XML files to each other. The simple structures underlying XML make exchanging information extremely simple, regardless of whether all parties are using the same software and browsers. However, until recently, although general XML utilities have become widespread, bridging the gap between XML documents and the user interface was still difficult. Microsoft Excel makes this task easy, at least for data in a table grid.

This trick uses Excel features that are only available in Excel for Windows older than 2003. Earlier versions of Excel do not support them; These features are not supported in current or planned versions of Excel for Macintosh.

Let's start with the simple XML document shown in Listing 8.1.

// Listing 8.1. A simple XML document for parsing in Excel <?xml version-"1.0" encoding-"UTF-8"?> 2003-10-05 0596005385 34.95 200 Zork's Books 2003-10-05 0596002920 2003-10-05 0596002378 29.95 30 0 Zork's Books 2003-10-05 0596005385 34.95 10 Books of Glory 2003-10-05 0596002920 39.95 25 Books of Glory 2003-10-07 0596002378 29.95 5 Books of Glory 2003-10-18 05 96002378 29.95 15 Title Wave 2003-10-21 0596002920 39.95 15 Books for You

This document can be opened directly in Excel using the File → Open command. A dialog box will open (Fig. 8.1).

Rice. 8.1. Opening an XML file in Excel

If you select the As an XML list radio button, you will see a warning that Excel will create its own schema for this document, which does not have a schema (Figure 8.2).

Rice. 8.2. Excel warning about missing schema references

By clicking OK, you will see which way Excel has chosen to present the information in the document you open as a spreadsheet (Figure 8.3). Note that Excel expects to encounter the date format that is used for the date element, so dates imported as 2003-10-05 will appear as 10/5/2003.

Rice. 8.3. XML data represented as an XML list in Excel

Now that your document is loaded into Excel, you can process the data just like you would any other data in Excel—pasting it into formulas, creating named ranges, building charts based on the content, and more. To help you, Excel has several built-in data analysis capabilities.

Drop-down lists in the column headers allow you to choose how the data is sorted (by default, the data is displayed in the order in which it was recorded in the source document). You can also enable display of the Total line; To do this, you can use the List toolbar or right-click anywhere in the list and select the command List → Total Row from the context menu. When the summary line appears, you can select the type of summary information in the drop-down menu (Fig. 8.4).

Rice. 8.4. Selecting totals for an XML list in Excel

Data can be updated by adding information from an XML document with the same structure to the area being updated. If you have another document with this structure, you can right-click the list, select XML → Import from the context menu, and select the second document. Additionally, after editing, the data can be exported back to an XML file by right-clicking the list and selecting XML → Export from the context menu. This turns Excel into a very convenient tool for editing simple XML documents with a tabular structure.

If the data is simple enough, you can often trust Excel to choose how to present the contents of the file and use the default settings provided. If the data gets more complex, especially if it contains dates or text that looks like numbers, then you may want to use XML schemas to tell Excel how to read the data and what data will fit in a given map. For our document, the XML schema might look like Listing 8.2.

// Listing 8.2.
Schema for book sales data < ?xml version=»1.0″ encoding=»UTF-8″?> Note that the date element is defined as a date, and the ISBN element is defined as a string, not an integer. If you start by opening this diagram rather than the document, you will force Excel to load the document by storing the leading zero in the ISBN.

This time, you'll create the list before loading the XML document, starting with a blank worksheet. You will need to open the XML Source task pane. If it is not already open, press the keyboard shortcut Ctrl+Fl. Then, from the drop-down list at the top of the task pane, select XML Source and you will see something similar to Fig. 8.6.

Rice. 8.6. XML Source Task Pane

To download the diagram, click the XML Maps button. The XML Maps dialog box will open (Figure 8.7).

Rice. 8.7. XML Maps Dialog Box

Click the Add button to open the schema and select the schema (Figure 8.8). If the schema does not limit documents to one starting element, a dialog box appears asking you to select a root element. Since the documents in this example begin with the element sales, select "sales".

Rice. 8.8. Selecting an XML Schema

When you click OK, a warning will appear warning you that the diagrams may be difficult to interpret. XML Schema is a huge specification that supports an extremely large number of structures that do not fit the way Excel understands information, so Excel has some limitations.

In the XML Maps dialog box, Excel will indicate that the diagram has been added to the spreadsheet. If you click OK, you return to the main Excel window and a diagram showing the schema structure appears in the XML Source task pane. Now that you have the structure, you can create the list. The easiest way to do this, especially with small documents like ours, is to drag the sales icon onto cell A1.

Now that you've set up a home for your data, you need to move it in. You can click the Import XML Data button on the List toolbar, or right-click the list and select XML → Import from the context menu. If you select a file that you opened earlier (in Listing 8.1), you will see the result as in Fig. 8.3. Note the addition of leading zeros to the values, which are now text as they should be.

You can also drag items individually if you want to rearrange them, or place different pieces of information in different places in the spreadsheet.

Excel's support for XML maps and lists means you can create spreadsheets that work with data that comes in separate files with more flexibility than was possible with previous formats such as CSV (comma-separated value) or tab-delimited format .

Instead of connecting to a database to edit data interactively, the user can edit the XML file while on the plane and transfer it to the customer immediately upon landing. Perhaps the best feature of Excel's new XML features is their flexibility. As long as the data is organized into a structure that follows a table grid, Excel has very few rules about what kinds of XML can be passed there. With a few clicks and no programming at all, you can integrate XML data into spreadsheets.

How to translate XML to Excel

Before you can translate XML into Excel, you need to open it first. Let's look at this entire short process below. So let's get started:

1. Launch Microsoft Excel using the desktop shortcut or through the Explorer menu.
2. Click on "Open More Workbooks" at the bottom of the home page if you're using Office. The process may be slightly different in other versions.
3. Next, click on “Computer” or other location where your XML file is located, then click “Browse” and change the data type to “XML Files” in the window that appears.
4. Now we look for the required file and open it in our program using the button of the same name.
5. Next, we'll discuss how to transform our document. Go to the developer tab, then click on the import button.
6. In the window that opens, you will need to again find and select our document on which you want to perform the action, then click on the “Import” button.
7. Then you customize everything for yourself: the import area, save location and other parameters. After which you will receive a table, which you can later save in the format you need. As you can see, there is nothing complicated.

How to open an XML file in Excel

1. We change the format to “XML files”. Select the desired file. Click “Open”.
2. Opening method – XML table. OK.
3. An alert appears like

Click OK. You can work with the resulting table as with any Excel file.

How to Convert XML File to Excel

1. "Developer" menu - "Import" tab.
2. In the dialog box, select the XML file that you want to convert.
3. Click “Import”. Excel will offer to independently create a diagram based on the data received. We agree - OK. A window will open where you need to select a location for the imported file.
4. We assign a range for import. It’s better to take it with a “spare”. Click OK.

We edit the created table and save it in Excel format.

Let's sum it up

Dear readers, today we learned not only how to convert XML files to Excel and vice versa, but also how to create and open them if the versions are incompatible. We hope that everything worked out for you and there are no more questions left. Don’t forget to share your opinion and experience in the comments: perhaps you know a simple conversion method. Tell other users if you were successful in translating one type of document. If it worked, then share what method you used.