Open xml file in excel. Export XML data. Editing, saving and exporting

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 easier, 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 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 click right click mouse anywhere in the list and in context menu select the command List → Total Row. 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 the new features XML Excel- this 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.

Announcement

XML Document File Format

Having been created as a format for storing text data, XML format is a document that is understood not only by people, but also by machines. XML is a platform-independent language that was created to store various types of data. With simplicity and ease of use, given language not inferior in popularity HTML language. It is also quite common on the Internet. The fact that XML files can be easily edited by the simplest text editors, only increases its popularity.

Technical information about XML files

An XML document is a sequence of characters in which each Unicode character can be found in each individual document. This Unicode character code that makes up an XML document is divided into tokens and text content based on simple syntax rules. This format has an important advantage over HTML: XML supports arbitrary placement of tags to clearly define the data that such tags cover.

Additional information about the XML format

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 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 as XLSX or XLS documents.

This is the procedure for converting an XML file to Excel format through the program interface is completed.

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.

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 a 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), then the data schema will be exported and saved separately from the file XML data, in the 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 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.

After importing XML data, mapping the data to worksheet cells, and making changes to the data, you often need to export or save the data as an XML file.

Important:

Export XML data (with a maximum of 65,536 rows)

Export XML data (for more than 65,536 rows)

Find the difference between the total number of lines in the file and the number 65,537. Let's denote this number as x.

Delete x rows from the beginning of an Excel sheet.

Export the sheet as an XML data file (see previous section of the procedure).

Click the button Close, But don't save sheet. Open the Excel sheet again.

Remove all but the first x lines and then export as an XML data file (see previous section of the procedure).

This procedure will allow the remainder of the string. On this moment There are two Export XML files that combine to create a duplicate of the original sheet.

Saving XML data in mapped cells in an XML data file

If you need to provide backwards compatible with earlier versions of XML functionality, you can save the file as an XML data file rather than using the command Export.

Note: If the sheet contains titles or labels that differ from the XML element names in the XML map, then when you export or save the XML data Excel application uses XML element names.

Common problems when exporting XML data

Messages similar to the following may appear when exporting XML data.

This XML map can be exported, but some required elements are not mapped

This message may appear for the following reasons.

The XML map associated with this XML table has one or more required elements that are not mapped to it.

In the hierarchical list of elements in the XML Source task pane, required elements are highlighted with a red asterisk. It is located in the upper right corner of the icon, which is located to the left of the element. To match the element you want, drag it to where you want it to appear.

The element represents a recursive structure.

A typical example of a recursive structure is a hierarchy of employees and managers, in which the same XML elements are nested at several levels. You may have mapped all the elements in the XML Source task pane. But Excel doesn't support recursive structures that are more than one level deep, so you won't be able to match all the elements.

The XML table contains mixed content.

Mixed content occurs when an element contains a child element and plain text outside of the child element. This is often the case when formatting tags (such as bold tags) are used to mark data within an element. The child element can be displayed (if supported in Excel), but the text content is lost when the data is imported and is not available when exported, meaning it is not used in either the forward or reverse operation.

Can't export XML maps in a workbook

The XML map will fail to export if the relationships of the mapped element to other elements cannot be preserved. The relationship may not survive for the following reasons.

The mapped element's schema definition is contained in a sequence with the following attributes:

• attribute maxoccurs not equal to 1;

  the sequence contains more than one direct child element or includes another composite object as such an element.

Non-repeating sibling elements with the same repeating parent element are mapped to different XML tables.

Multiple duplicate elements are mapped to the same XML table, and the repetition is not defined by an ancestor.

Children of different parent elements are mapped to the same XML table.

Additionally, you cannot export an XML map if it contains one of the following XML schema constructs.

List of lists. One list of elements contains another list of elements.

Unnormalized data. The XML table contains an element that, according to the definition in the schema, must occur once (the attribute maxoccurs assigned the value 1). When you add such an element to an XML table, Excel will populate the table column with multiple instances of it.

Choice. The matched element is part of the circuit construct .

Article rating:

(No ratings yet)

Loading...

Share with friends: