How to convert xml file to excel online. Import XML data. Having trouble importing multiple XML files that use the same namespace but different schemas

XML is one of the most common formats for storing data and exchanging it between various applications. The program also works with the data. Microsoft Excel Therefore, the issue of converting files from the XML standard to Excel formats is very relevant. Let's find out how to carry out this procedure in various ways.

XML files are written in a special markup language something similar to HTML web pages. Therefore, these formats have a fairly similar structure. At the same time, Excel is primarily a program that has several “native” formats. The most famous of them: Excel workbook(XLSX) and Excel Workbook 97 - 2003 (XLS). Let's find out the main ways to convert XML files into these formats.

Method 1: Built-in Excel functionality

Excel works great with XML files. She can open them, change them, create them, save them. Therefore, the simplest version of the task assigned to us is to open this object and save it through the application interface in the form of XLSX or XLS documents.

This completes the procedure for converting an XML file into Excel format through the program interface.

Method 2: Import Data

The above method is only suitable for XML files with the simplest structure. More complex tables may not be translated correctly when converted this way. But, there is another built-in Excel tool that will help you import data correctly. It is located in "Developer Menu", which is disabled by default. Therefore, first of all, it needs to be activated.

Thus, the conversion in the direction we need will be completed with the most correct data conversion.

Method 3: Online Converter

Those users who, for some reason, do not have Excel installed on their computer, but who need to urgently convert a file from XML to EXCEL format, can use one of the many specialized online conversion services. One of the most convenient sites of this type is Convertio.

This option can serve as a good safety net in case of lack of access to standard tools for reformatting in this area.

As you can see, Excel itself has built-in tools that allow you to convert an XML file into one of the “native” formats of this program. The simplest copies can be easily converted using the usual “Save As...” function. For documents with a more complex structure, there is a separate conversion procedure via import. Those users who for some reason cannot use these tools have the opportunity to complete the task using specialized online services for converting files.

XML is universal format working with data. It is supported by many programs, including those from the DBMS field. Therefore, converting information to XML is important precisely from the point of view of interaction and data exchange between various applications. Excel is just one of the programs that work with tables, and can even perform manipulations with databases. Let's figure out how to convert Excel files to XML.

Converting data to XML format not such a simple process, since during it a special schema (schema.xml) must be created. However, to convert information into simple file of this format It is enough to have the usual saving tools in Excel at hand, but to create a well-structured element you will have to tinker a lot with drawing up a diagram and connecting it to the document.

Method 1: Simple Saving

In Excel, you can save data in XML format simply by using the menu "Save as…". True, there is no guarantee that all programs will then work correctly with a file that was created in this way. And this method does not work in all cases.

Thus, the file conversion from Excel to XML format will be completed.

Method 2: Developer Tools

You can convert the Excel format to XML using the developer tools on the program tab. Moreover, if the user does everything correctly, then at the output he will receive, unlike the previous method, a full-fledged XML file that will be perceived correctly third party applications. But it must be said right away that not every beginner may have enough knowledge and skills to immediately learn how to convert data in this way.

1. By default, the Developer Tools tab is disabled. Therefore, first of all, you need to activate it. Go to the tab "File" and click on the item "Options".



2. In the parameters window that opens, move to the subsection "Customize the Ribbon". On the right side of the window, check the box next to the value "Developer". After that, click on the button "OK" located at the bottom of the window. The developer tools tab is now enabled.



3. Next we open Excel spreadsheet in the program in any convenient way.



4. Based on it, we have to create a diagram that can be generated in any text editor. For these purposes, you can use regular Windows Notepad, but it is better to use a specialized application for programming and working with markup languages ​​Notepad++. Let's launch this program. We create a diagram in it. In our example, it will look as shown below in the screenshot of the Notepad++ window.

   

   As you can see, the opening and closing tag for the document as a whole is "data-set". The tag plays the same role for each line "record". For the schema it will be enough if we take only two rows of the table, and do not manually translate it all into XML. The name of the opening and closing tag of a column can be arbitrary, but in this case, for convenience, we preferred to simply translate the Russian-language names of the columns into English language. After the data is entered, we simply save it through the functionality text editor anywhere on your hard drive in XML format called "schema".

5. Again we go to Excel with the table already open. Moving to the tab "Developer". On the ribbon in the toolbox "XML" click on the button "Source". In the field that opens on the left side of the window, click on the button "XML Maps...".



6. In the window that opens, click on the button "Add…".



7. The source selection window opens. Go to the directory for the location of the diagram compiled earlier, select it and click on the button "Open".



8. After the elements of the diagram have appeared in the window, drag them using the cursor into the corresponding cells of the table column names.



9. Click right click mouse according to the resulting table. IN context menu go step by step step by step "XML" And "Export…". After that, save the file in any directory.

As you can see, there are two main conversion methods XLS files and XLSX to XML format Microsoft program Excel. The first of them is extremely simple and consists of an elementary saving procedure with a given extension through the function "Save as…". Simplicity and clarity this option are undoubtedly advantages. But it has one very serious drawback. The conversion is carried out without taking into account certain standards, and therefore a file converted in this way may simply not be recognized by third-party applications. The second option involves drawing up an XML map. Unlike the first method, a table converted using this schema will meet all XML quality standards. But, unfortunately, not every user will be able to quickly understand the nuances of this procedure.

Let's look at the procedure for converting a database object into XML format:

1. In the database window, select the required object by left-clicking and select the command File, Export(File, Export) or right-click on the object and select the command from the context menu Export(Export).
2. A dialog box will appear Export an object(Export To). In the dropdown list Types(Save as type) select the element XML Documents(XML Documents). In field File name(File name) enter the name HTML document and press the button Export(Export).
3. A dialog box will appear XML export(Export XML) (Fig. 12.9).
4. This dialog box contains three checkboxes for the three components of an XML document: data, data schema, and data presentation. (For more information, see the “XML Format in Microsoft Office 2002” section of this chapter). Select the check boxes for the parts of the XML document that you want to export. For example, if you select the checkboxes simultaneously data (XML)(Data (XML)) and data schema(Schema of the data), the data schema will be exported and saved separately from the XML data file, in an XSD file. For tables, you can export only the data or its structure, or the data and structure together and not export the table view as a Web page: checkbox data presentation(XSL) (Presentation of your data (XSL)) can be removed.

Rice. 12.9. Dialog window XML export

Comment

The Data (XML) check box should only be cleared if you only want to export a new representation of the database object and the data has previously been exported to an XML file. Otherwise, when you open the resulting Web page, you will receive a message about an error connecting to the data source, and the page itself will be almost empty.

1. To set additional conversion options, click the button Additionally(Advanced) (Fig. 12.10).

Rice. 12.10. Tab Data dialog box XML export

1. In addition, this dialog box contains two more tabs for the two remaining components of the XML document: data schema and data representation. On each of these tabs, you can select a checkbox that allows you to export the corresponding part of the XML document, specify the path and name of the file in which this part of the document will be saved, and set some additional parameters:
   • on the tab Data(Data) using dropdown list Encoding(Encoding) you can select the encoding to save the page contents;
   • on the tab Scheme(Schema) you can use radio buttons to choose whether to export the data structure to a separate XSD file or embed it in an XML file along with the data;

Rice. 12.11. Account report opened in database

• on the tab Presentation(Presentation) using the switches, you can select the presentation format of the Web page: HTML processed on the client side or ASP server side. If the exported object contains pictures that you want to include in the resulting document, you can specify the name of the folder in which they are located. On the same tab there is a field for entering the name of the XSL file with a description of the page design. If specified file does not exist, Access 2002 will create an XSL file describing the external representation of the document (the developer can later use this as the basis for developing an XSLT file to convert the XML document to another format).

If required Additional Information about representing data in XML format, click the button Reference(Help). Information about developing and converting XML documents can be found on the Microsoft Developer Web site: http://msdn.microsoft.com/.

Rice. 12.12. The "Invoice" report, the data of which is converted into XML format, opened in Internet Explorer

1. Click OK.
2. If the proposed transformation parameters have not been changed, three files will be created as a result: XML - with data, XSL - with styles, and HTM - with a representation of the converted object. To open the resulting Web page, you need to open an HTM or ASP file in Internet Explorer, depending on which presentation format you selected.

Unfortunately, subforms and reports, and associated graphics, will not be added to the XML document obtained from the database object. However, simple objects with embedded graphics exported to XML will look almost exactly the same as the original ones in Access. You can verify this by comparing, for example, the original and the Invoice report converted to XML format from the Northwind database (Figures 12.11 and 12.12).

If there is a lot of data in the data source of the selected object, loading the Web page into the Internet browser may take a significant amount of time.

Comment

Since the data itself contained in such a Web document is stored entirely in the XML file, there is no need to access the database from which the data was originally taken (as, for example, is the case when converting a report to XML format). This means that that to provide access to such a document it is enough to place only the mentioned set of files containing a description of this document on Web server. On the other hand, this means that changing the data in such a document is only possible using applications that have access to the XML file and are able to perceive this format.

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 capabilities are not supported in both existing and planned Excel versions 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 300 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 0596002378 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).

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

By clicking OK, you will see which Excel way chose to present the information in the document being opened in the form of a spreadsheet (Fig. 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.

Now that your document is loaded into Excel, you can process the data just like you would any other data in Excel - inserting it into formulas, creating named ranges, building charts based on the content, etc. 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.

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

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

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 in a structure that follows a table grid, Excel has very few rules about what XML types can be sent there. With a few clicks and no programming at all, you can integrate XML data into spreadsheets.

Looking for best free Online XML to Excel converters? You are at the right place. This list of free online tools will let you convert XML to Excel format. All these converters support both XML to XLS, as well as XML to XLSX conversion. Most of these converters let you upload XML file from your PC, or specify URL of XML file, or even paste the text of XML file; while one of these software lets you batch convert XML to Excel. Some of these provide some advanced options for a custom output as well. Carry out the conversion and save the Excel file to your PC.

Luxonsoftware.com

Luxonsoftware.com is a free online converter tool to convert an XML to Excel file. Click on the browse button to select an XML file from your device. Select the file to upload, and start the conversion. After the conversion is complete, a dialogue box appears, which will ask you to download the file to your system. The downloaded Excel file is in ZIP format. the file to . An error less conversion can be achieved with the help of this online XML to Excel converter. You can also access advanced conversion options for the conversion. In Advanced options, it lets you choose a DTD file. So, if you have a XML file that has a corresponding DTD (Document Type Definition) file, then you can upload both of them together and convert to Excel. Apart from XML to Excel conversion, you can also carry out XML to CSV, XML to JSON, CSV to XML, and XML to XSD conversion on this website. The free version only allows a maximum file upload size of 4 MB.

Xmlgrid.net

Xmlgrid.net lets you convert an XML file to Excel format pretty easily. Upload the XML file to the converter. You can either directly paste the XML script in the input box, or upload the XML file. This XML to XLS converter even lets you provide the URL of the XML file. It shows you the data source, and the conversion status. Hit the convert button, and the converted file appears in the output box. Full screen mode lets you view the Excel file on the whole screen. Save the Excel file to any desired location on your device, once you have inspected the output. This online tool not only lets you convert XML to Excel format, but you can also convert XML To Text, XSD To XML, CSV To XML, XML To XSD, and Excel To XML, absolutely free.

Daemonservices.com

Daemonservices.com provides free online web and document conversion services. To convert XML to Excel, you will have to browse and select an XML file from your system. After the upload is complete, click on the Convert button, which is right below the browse option. A status bar shows the status of conversion. After the conversion is successful, download the Excel file to your computer in any desired folder. HTML Page to PDF, HTML Table to CSV Excel (XLS) to PDF, Excel (XLS) to HTML, Excel (XLS) to CSV OpenOffice Calc (ODS) to CSV Image to PDF, CSV to XML and PDF to TEXT are other converters available on this website. The above mentioned tools are free to use.
The maximum file upload size is 150 MB.

Office Converter

Office Converter is another good online document converter. Convert your XML 2 Excel with the help of this free online tool. You can convert a single file or batch convert XML to Excel format. Upload one or more XML files from your PC, or enter the URL of the XML file. You have to start the conversion separately for each XML file. A status bar shows if the conversion is complete for each file. Download the Excel file once the XML 2 Excel conversion is complete. This website is capable of conversion to the following document formats: PDF, Word, Doc, PowerPoint(PPT and PPTX), Flash, OpenOffice, HTML, ODF RTF, TXT, and XML. Apart from document conversion, this website also lets you perform multimedia conversion, Ebook conversion, and Archive conversion.

Json-xls.com

Json-xls.com offers free online document conversion. To convert an XML to XLS or XLSX format, you can upload the XML file to the browser, or paste the XML text, or directly provide the URL of the file. You can also select if you want the conversion in XLS, or XLSX format. After passing the human verification, hit the submit option. The conversion starts and the download initiates automatically. Save the converted Excel file to a desired folder. This online converter also lets you convert JSON to XLS, and CSV to XLS format. The conversion is pretty swift and error free.