How to collect and export data from XML-files in Excel

Microsoft Excel is a useful instrument for organizing and structuring very different data. It can be used to process information by various methods and edit data arrays.

Let us see its tools for generation and processing web applications files. Using a particular example, we will learn the basics of work with XML in Excel.



How to create XML-file in Excel

XML is a file standard for transferring files over the Internet. Excel supports its export and import.

We are going to look through the process of creation an XML-file using factory calendar as a particular example.

1. Create a table on which you want to create an XML file in Excel and fill it with data.
2. Create and insert a map with the required XML document structure.
3. Export the table data in XML format.

Altogether.

1. Our table is a Holiday calendar.

2. Create any text editor (such as «Notepad») the desired XML structure to generate a map file is saved. In this example, use the map structure:

<DocumentElement> <Calendar> <Date></Date><Name></Name><Year></Year><Month></Month><Day></Day><Weekday></Weekday><Types></Types><Country></Country> </Calendar> <Calendar> <Date></Date><Name></Name><Year></Year><Month></Month><Day></Day><Weekday></Weekday><Types></Types><Country></Country> </Calendar> </DocumentElement>

3. Open the tool «DEVELOPER». Choose the dialog box «Source» in the XML tab.

4. If Excel cannot see XML maps, they have to be added. Press «XML Maps» button.

5. Press «Add». Find your XML file – press ОК.

6. Elements of the schema will appear in the right column. They have to be dragged to the corresponding column names of the table.

7. Check whether the export is possible.

8. When all of the elements will be associated, right click any cell on the table and choose XML – Export.



Save the file.

How to save an Excel file in XML format

One of the options:

1. Press CTRL+S, choose «Save as» - «Other formats».
2. Set the name. Choose the file destination and type – XML.

Press «Save» button.

If you get any errors, try to save the workbook as XML 2003 table or web page. Usually, there are no problems with these formats.

Other options:

1. Download XLC to XML converter, or find a service, which exports files online.
2. Download XML Tools Add-in from the official Microsoft web site. It is available free of charge.
3. Open a new workbook. Press CTRL+O «Open».

How to open an XML file in Excel

1. Change the format to XML file. Choose the desired file. Press CTRL+O.
2. Open it as an XML-table. Press OK.

3. Similar notification will appear.

4. Press OK. You can work with this table the same way as with any Excel file.

How to convert XML file to Excel

1. Open the tool «DEVELOPER». Choose the dialog box «Import».

2. Choose a file in the dialog box, that you want to convert.
3. Press «Import». Excel will offer to create a scheme automatically based on the data received. Press OK. A window will open, where you will need to choose a destination for the importing file.

4. Set the range for import. It is better to add extra cells for future entries. Press «OK».

From now on, the created table is being edited and saved in Excel format.

How to collect data from XML-files to Excel

The principle of collecting data from several XML-files is the same as the principle of conversion. When we import data to Excel, the XML map transfers at the same time. You can also transfer other data in this schema.

Each new file will be linked to the existing map. Every element of the table structure binds to an element from XML map. Only one data link is allowed.

In order to configure binding settings, in the «DEVELOPER» menu choose the «Map Proparties» instrument.

Features:

1. Excel will check every new file for compliance with the existing map (if you check the box next to this line).
2. The data can be update. Also, new information can be added to the existing table (it makes sense, if you need to collect data from similar files).

Other ways to get structure data (schema):

1. Download it from a database of a specialized business application. Commercial sites and services may provide schemas. Simple options are in the public domain.
2. Use prepared examples in order to validate the maps. Examples contain the basic elements and structure data. Copy and paste it into the program «Notepad» – and save it with the desired extension.

These are all manual methods of import and export files structure data.