Reviews 

Is it possible to convert xls file to xml. Convert XML files to Excel formats. How to open an XML file in Excel

XML is a universal format for working with data. It is supported by many programs, including those from the DBMS field. Therefore, converting information into 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 is not such a simple process, since during this process a special schema (schema.xml) must be created. However, to convert information into simplest file In 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 translate it all manually 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 we 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 ways to convert XLS and XLSX files into XML format with a program Microsoft Excel. The first of them is extremely simple and consists of an elementary saving procedure with a given extension through the function "Save as…". The simplicity and clarity of 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.

I'll tell you quick way creating an xml file from an excel table!

XML documents are a very strong and powerful thing. With one XML file and you can fill the site with information in a matter of seconds! After all, all modern engines (CMS) have the ability to import from xml file. So why am I doing this?

As you know, *.xls formats (*.xlsx in 2007 office) are Microsoft formats Office Excel. The 2003 office is a thing of the past, there is already 2010, but I work on 2007, and therefore I will talk based on it. Let's go!

1. Go to the Microsoft website and download the add-on for working with XML. Download Excel 2003 Add-in: XML Tools Add-in. It doesn't weigh much, 397 KB.



2. Install it on your computer. There is nothing difficult to install. By default, the add-in is installed here: c:\Office Samples\OfficeExcel2003XMLToolsAddin

3. Now open Excel, go to the menu and select “Excel Options”.

4. In the window that appears, on the left, select the “Add-Ins” item, and at the bottom of the window, click on the “Go...” button.

5. A new window will open in front of you, in which you need to click the “Browse...” button. What it looks like is shown below.

6. Now you need to find the installed XmlTools add-on (see). Select it and click OK!

7. If you did everything correctly, you will see the following window! Feel free to click OK, installation is complete!

8. You now have an add-on tab in the top menu, and an XML Tools item on the left.

We've sorted out the installation, and now let's move directly to converting (exporting) xls to xml.

1. To do this, open the file with the data that needs to be distilled. Then select the first item in the drop-down menu “Convert a Range to an XML List...”

2. A small window will open in front of you, what do you need in it? There are radio buttons, No and Yes, what are they for? It’s simple, if you have a header for the data (in my case there is one), select Yes, and if you don’t have it, then No. Then click on the small rectangle in the top line.

3. Select the area of ​​data that we are converting and click on the button on the right in the window that appears. The previous window returns and click OK.

4. Your sign should change, one might say transform, for me it looks like this:

6. In the “File type” drop-down list, select XML data and click “Save”.

Congratulations, your file has been created!

I hope everything was presented in detail and clearly, but if you have any questions, write!

When working with block data, there is often a need to convert it into other formats, so to speak, for better interaction. This article will provide possible XML to XLS converters. In other words, we will talk about ways to convert one format to another.

Method 1: Using Excel

Since Excel works flawlessly with block format files, it can open the XML extension without problems. So we can say without a twinge of conscience that Excel is an XML to XLS converter. Let's look at how to transform the format using it:

  1. Open Excel.
  2. On the panel, click "File".
  3. Click on the “Open” option.
  4. In the Explorer window that appears, go to the directory with the XML file and open it.
  1. Open the File tab again.
  2. Select the "Save As" option.
  3. Go to the directory where you want to place the modified document.
  4. In the “File type” drop-down list, select “ Excel workbook».
  5. Save the file by clicking the appropriate button.

After all the manipulations described above, the file, which originally had an XML extension, will turn into an Excel workbook, that is, it will have an XLS extension.

Method 2: Import Data Option in Excel

Excel is an XML to XLS converter with two ways to accomplish this task. Therefore, let’s move directly to the second option, which involves using the “Data Import” option. But before you start, you need to enable the “Developer” menu, since this is where the necessary tool is located. Next you need to do the following:

  1. Open the program.
  2. Go to the "File" menu.
  3. Follow the "Options" section.
  4. Open the Customize Ribbon menu.
  5. Place a check mark next to the “Developer” line, which is located in the right area of ​​the window.
  6. Click OK.

The required toolbar is included in the program interface, which means that we can start converting:

  1. Go to the Developer tab.
  2. From the toolbar, select Import.
  3. A window will open in which you need to select a file XML format.
  4. A window will appear asking you to create a schema based on the data. Click OK.
  5. After this, a second window will appear in which you need to decide on the location of the imported table. Click cell A1 and click OK.

The file is imported into Excel. Now you can save it as XLS. To do this, by analogy with the previous instructions, open the “File” tab, click on “Save as”, specify the directory, select the format and save.

Method 3: Using an Internet service

It may happen that you need an XLS to XML converter for VAT, for example. In this case, Excel will not help, and you will need to turn to another program. In this case, let's talk about the online service Convertio:

  1. In any browser, go to home page of this service.
  2. Select a file upload method. If it is located on your disk, click the "From Computer" button and select it in file manager. If in the “cloud”, then select the storage itself, and then the file.
  3. The document has been added and is ready for conversion. To do this, you need to select the XML format from the drop-down list on the right.
  4. Click the "Convert" button.

After this, the file will be prepared, and all you have to do is download it. Convertio is not only an XLS to XML file converter. It is capable of working with a much larger number of formats. Simply upload the file, and then choose the format you want to convert it to.

So we looked at XML to XLS converters and vice versa. As you can note, it is excellent for converting XML Excel program. However, it cannot convert XLS, so it requires the help of other programs. In this case, it is the online service Convertio.

If you need to create an XML data file and an XML schema file from a range of cells in a worksheet, you can use version 1.1 of the XML Tools for Excel 2003 add-in to extend the existing XML capabilities in Microsoft Excel 2007 and later versions.

Note: This add-in was developed for Excel 2003. The documentation and user interface refer to lists, which are called Excel tables in versions of the application later than Excel 2003.

For more information about working with this add-in, see Use the XML Tools add-in version 1.1 for Excel 2003.

Step 2: Convert a range of cells to an XML table

    Enter the data for which you want to create an XML data file and an XML schema file. The data must be presented in a tabular format in the form of columns and rows (called ordinary data).

    On the tab Add-ons in Group Menu commands click the arrow next to the caption XML Tools and press the button Convert range to XML list.

    Enter the range of cells with the data you want to convert as an absolute reference in the text box.

    In field The first line contains the column names select No, if the first row contains data, or Yes if the first row contains column headers, and click OK.

    Excel will automatically create XML schema, will bind the cells to the schema and create an XML table.

    Important: If the editor opens Visual Basic and a Visual Basic for Applications (VBA) error message appears, follow these steps:

    1. Click the button OK.

      In the highlighted line of the VBA code module, remove "50" from the line. In other words, change:
      XMLDoc as MSXML2 . DOMDocument50
      To whom:
      XMLDoc as Msxml2. Domdocument

      Press F5 to search for the next line containing the text "XMLDoc As msxml2.DOMDocument50", click OK and change the line as in the previous paragraph.

      Press F5 again to find and change other instances of the line.

      If you no longer see the VBA error message after you press F5, close the Visual Basic Editor to return to the workbook. The range of cells will be converted to an XML table.

      Note: To display all XML maps in a workbook, in the tab Developer in Group XML click the button Source to display the XML Source task pane. At the bottom of the XML Source task pane, click XML Maps.

      If the tab Developer is not visible, follow the first three steps in the next section to add it to the Excel ribbon.

Step 3: Export the XML table to an XML data (XML) file

Note: When creating XML maps and exporting data in Excel to XML files, there is a limit to the number of rows that can be exported. When exporting to an XML file from Excel, you can save up to 65,536 rows. If the file contains more than 65,536 rows, Excel will only be able to export the first rows (number of rows mod 65,537). For example, if a worksheet contains 70,000 rows, Excel exports 4,464 rows (70,000 mod 65,537). We recommend following one of the following tips: 1) use the XLSX format; 2) save the file in "XML 2003 Table (*.xml)" format (this will lose the mappings); 3) remove all lines after 65536 and then export again (this will keep the mappings but will lose the lines at the end of the file).

XML is one of the most common formats for storing data and exchanging it 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 somewhat similar to the HTML of 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 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.