Microsoft Excel is a convenient tool for organizing and structuring a wide variety of data. It allows you to process information different methods, edit data sets.

Let's consider the possibilities of using it to generate and process web application files. Using a specific example, we will study the basics of working with XML in Excel.

How to create an XML file from Excel

XML is a file standard for transmitting data on the Web. Excel supports its export and import.

Let's look at creating an XML file using the example of a production calendar.

1. Let's make a table from which you need to create an XML file in Excel and fill it with data.
2. Let's create and insert an XML map with the required document structure.
3. Export table data to XML format.

We save the file as XML.

Other ways to get XML data (schema):

1. Download from a database, specialized business application. Schemes can be provided by commercial sites and services. Simple options are publicly available.
2. Use ready-made samples to test XML maps. The samples contain the main elements and XML structure. Copy and paste into Notepad and save with the desired extension.



How to save an Excel file in XML format

One of the options:

1. Click the Office button. Select “Save as” - “Other formats”.
2. We assign a name. Select the save location and file type – XML.

More options:

1. Download XLC to XML converter. Or find a service that allows you to export the file online.
2. Download the XML Tools Add-in from the official Microsoft website. It is freely available.
3. Opening a new book. Office button – “Open”.

How to open an XML file in Excel

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

How to Convert XML File to Excel

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

How to collect data from XML files in Excel

The principle of collecting information from multiple XML files is the same as the principle of transformation. When we import data into Excel, the XML map is transferred at the same time. Other data can be transferred to the same schema.

Every new file will be linked to the existing card. Each element in the table structure corresponds to an element in the map. Only one data binding is allowed.

To configure linking options, open the Map Properties tool from the Developer menu.

Possibilities:

1. Each new file will be scanned Excel program for compliance installed card(if we check the box next to this item).
2. Data may be updated. Or new information will be added to an existing table (makes sense if you need to collect data from similar files).

These are all manual ways to import and export files.

If you have an XML map, do the following to import the XML data into the mapped cells:

Other ways to import XML data

For more information about the issues, see the section at the end of this article.

Import an XML data file as an XML table

Importing Multiple XML Data Files

Importing Multiple XML Data Files as External Data

From XML Data Import. Excel will create a unique XML map for each XML data file you import.

Note:

If you're using Excel with an Office 365 subscription, click Data > To get data > From file > From XML.

If you are using Excel 2016 or earlier, on the Data click the button From other sources and then click From XML Data Import.

Select the drive, folder, or Internet location where the XML data file (XML file) you want to import is located.

Select the file and click the button Open.

In the dialog box Import data select one of the following options:

• To an XML table in an existing workbook. The contents of the file are imported into a new XML table in a new worksheet. If the XML data file does not reference any schema, Excel creates one from the file.

  To an existing sheet. The XML data will be imported into a two-dimensional table consisting of rows and columns. XML tags appear as column headings, and data appears in rows under their corresponding headings. The first element (root node) is used as the title and is displayed in the specified cell. The remaining tags are sorted alphabetically on the second line. In this case, no schema is created and you cannot use the XML map.

  To a new sheet. Excel adds a new worksheet to the workbook and automatically places the XML data in the top left corner of the worksheet. If the XML data file does not reference any schema, Excel creates one from the file.

To customize the behavior of XML data, such as data binding, formatting, and layout, click Properties. A dialog box will open XML Map Properties. For example, importing data by default overwrites the data in the mapped range, but you can change this behavior.

Opening an XML data file to import data

Common problems when importing XML data

If the data cannot be validated against the XML map, a dialog box appears XML import error. For more information about the error, click Intelligence in this dialog box. The following table describes errors that often occur when importing data.

Error	Explanation
Error checking schema	When you have selected in the dialog box XML Map Properties parameter Validate data against schema when importing and exporting, the data was imported but was not validated against the specified XML map.
Some data was imported as text	Some or all of the imported data was converted from a declared type to text. To use this data in calculations, you must convert it to numbers or dates. For example, a date value converted to text will not work as expected in the YEAR function until it is converted to a Date data type. Excel converts data to text in the following cases: The data is in a format that is not supported by Excel. The data is not compatible with the internal representation of the XSD data type in Excel. To correct this issue, ensure that the XML data conforms to the XML schema by checking each of the data type definitions.
XML parsing error	The XML parser cannot open the specified XML file. Make sure that the XML file does not contain any syntax errors and that the XML is constructed correctly.
Cannot find an XML map matching this data	This issue may occur if you select multiple XML data files for import and Excel cannot find a matching XML map for one of them. Import the schema for the file listed in the title bar of this dialog box first, and then import the file again.
Can't resize XML table to include data	You are trying to add rows by importing or appending data to an XML table, but the table cannot be extended. An XML table can only be supplemented from below. For example, there may be an object just below the XML table, such as a picture or even another table, that prevents it from being expanded. It is also possible that when you expand an XML table, you will exceed Excel's 1,048,576 row limit. To fix this problem, change the layout of the tables and objects on the worksheet so that the XML table can be padded from below.

The specified XML file does not reference a schema

The XML file you are trying to open does not reference an XML schema. To work with the XML data contained in a file, Excel requires a schema based on its contents. If such a diagram is incorrect or does not meet your requirements, remove it from the book. Then create an XML schema file and modify the XML data file to reference the schema. For more information, see Mapping XML Elements to XML Map Cells.

Note: A schema created by Excel cannot be exported as a separate XML Schema data file (XSD file). Although there are XML schema editors and other ways to create XML schema files, you may not have access to them or know how to use them.

Follow these steps to remove an Excel-created diagram from a workbook:

Having trouble importing multiple XML files that use the same namespace but different schemas

When working with multiple XML data files and multiple XML schemas, the standard approach is to create an XML map for each schema, map the desired elements, and then import each of the XML data files into the appropriate XML map. When using the command Import You can only use one XML schema to open multiple XML files with the same namespace. If you use this command to import multiple XML files that use the same namespace under different schemas, you may get unexpected results. For example, this could cause data to be overwritten or files to stop opening.

If you need to import multiple XML files with the same namespace but different XML schemas, you can use the command From XML Data Import(select Data > From other sources). This command allows you to import multiple XML files with the same namespace and different XML schemas. Excel will create a unique XML map for each XML data file you import.

Note: When you import multiple XML files that do not have a namespace defined, they are considered to share the same namespace.

XML to Excel and vice versa, let's take a closer look at the types of these files. It should be immediately noted here that Excel documents have many formats, while XML is the file format. Therefore, you should not confuse these two concepts.

Microsoft Excel is powerful program for computing with many useful functions

Microsoft Excel is 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 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 way, which may not work in some cases, is to save the file in in the required format in Microsoft Excel itself. To do this, open 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 http://www.microsoft.com/en-us/download/details.aspx?displaylang=en&id=3108, download and install this file on your computer or laptop. Next, launch Microsoft Excel 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 application Format Factory, 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.

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 the program using a shortcut on your desktop or through the Explorer menu.
2. Click on "Open More Books" at the bottom home page if you are 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 are looking 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.

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.

XML is one of the most common formats for storing and exchanging data between various applications. Works with data Microsoft program Excel, so the issue of converting files from the XML standard to Excel formats. 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 are: 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 direction.

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.

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 context menu team 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 the switches to select whether to export the data structure to separate file XSD 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 XML document, obtained from a 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 the 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 it. this format.