If you previously created an XML Map, you can use it to import XML data into cells that are mapped, but there also are several methods and commands for importing XML data without an XML Map.
If you have an XML Map, do this to import XML data into mapped cells:
-
In the XML Map, select one of the mapped cells.
-
Click Developer > Import.
If you don't see the Developer tab, see Show the Developer tab.
-
In the Import XML dialog box, locate and select the XML data file (.xml) you want to import, and click Import.
Other ways to import XML data
For more information about issues, see Common issues with importing XML data at the end of this article.
Import an XML data file as an XML table
-
Click Developer > Import.
If you don't see the Developer tab, see Show the Developer tab.
-
In the Import XML dialog box, locate and select the XML data file (.xml) you want to import, and click Import.
If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.
-
In the Import Data dialog box, do one of the following:
-
Select XML table in existing worksheet to import the contents of the XML data file into an XML table in your worksheet at the specified cell location.
-
Select XML table in new worksheet to import the contents of the file into an XML table in a new worksheet starting at cell A1. The schema of the XML data file is displayed in the XML Source task pane.
-
-
If the XML data file doesn't refer to a schema, then Excel infers the schema from the XML data file.
-
To control the behavior of XML data (such as data binding, format, and layout), click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range will be overwritten when you import data by default, but you can change this.
Import multiple XML data files
-
Select a mapped cell to import multiple XML files into a single set of mapped cells.
If you want to import multiple XML files into multiple sets of mapped cells, click a cell anywhere on the worksheet that isn't mapped.
-
Click Developer > Import.
If you don't see the Developer tab, see Show the Developer tab.
-
In the Import XML dialog box, locate and select the XML data file (.xml) you want to import.
-
If the files are contiguous, press Shift, and click the first and the last file in the list. All of the data from the XML files will be imported and appended to the mapped cells.
-
If the files aren't contiguous, press and hold Ctrl, and click each file you want to import in the list.
-
-
Click Import.
If you selected files that aren't contiguous, the Importing <filename>.xml dialog box appears. Select the XML Map that corresponds to the XML data file you're importing for each file.
To use a single map for all of the selected files that aren't yet imported, select Use this XML Map for all selected files of this schema.
Import multiple XML data files as external data
To import multiple XML files that use the same namespace but different XML schemas, you can use the From XML Data Import command. Excel creates a unique XML Map for each XML data file you import.
Note: If you're importing multiple XML files that don't define a namespace, these XML files are treated as if they use the same namespace.
-
If you're using Excel with a Microsoft 365 subscription, click Data > Get Data > From File > From XML.
If you're using Excel 2016 or earlier, click Data > From Other Sources > From XML Data Import.
-
Go to the drive, folder, or Internet location that has the XML data file (.xml) you want to import.
-
Select the file and click Open.
-
In the Import Data dialog box, do one of the following:
-
XML table in existing worksheet The contents of the file are imported into a new XML table in a new worksheet. If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.
-
Existing worksheet The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn't infer a schema, and you can't use an XML Map.
-
New worksheet Excel adds a new worksheet to your workbook and automatically puts the XML data in the upper-left corner of the new worksheet. If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.
-
-
To control the behavior of XML data, such as data binding, format, and layout, click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range is overwritten when you import data by default, but you can change this.
Open an XML data file to import its data
-
Click File > Open.
If you're using Excel 2007, click Microsoft Office Button
> Open. -
In the Open dialog box, click the drive, folder, or Internet location that has the file that you want to open.
-
Select the file and click Open.
-
If the Import XML dialog box appears, the file you opened refers to one or more Extensible Stylesheet Language Transformation (XSLT) style sheets, so you can click one of the following options:
-
Open the file without applying a style sheet The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn't infer a schema, and you can't use an XML Map.
-
Open the file with the following style sheet applied (select one) Select the style sheet that you want to apply, and then click OK. The XML data is formatted according to the style sheet that you selected.
Note: The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel doesn't infer a schema, and you can't use an XML Map.
-
-
If the Open XML dialog box appears, the XML file doesn't have any XSLT style sheet references. To open the file, click one of the following options:
-
Click As an XML table to create an XML table in a new workbook.
The contents of the file are imported into the XML table. If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.
-
Click As a read-only workbook.
The XML data is imported in a two-dimensional table with rows and columns that shows XML tags as column headings, and data in rows below the column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row. In this case, Excel doesn't infer a schema, and you can't use an XML Map.
The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel doesn't infer a schema, and you can't use an XML Map.
-
Click Use the XML Source task pane.
The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.
If the XML data file doesn't refer to a schema, Excel infers the schema from the XML data file.
-
Common issues with importing XML data
Excel displays the XML Import Error dialog box when it can't validate data according to the XML Map. In this dialog box, click Details for additional information about each error. The following table lists some common import errors:
| Error | What happened |
| Failed schema validation | When you clicked Validate data against schema for import and export in the XML Map Properties dialog box, the data was imported, but the data wasn't validated against the specified XML Map. |
| Some data was imported as text | Some or all of the data you imported was converted from its declared data type to text. To use this data in a calculation, you'll need to convert the data from text to numbers or dates. For example, a date value converted to text won't work as intended in the YEAR function until you convert it to the Date data type. Excel converts data to text when:
|
| XML parse error | The XML parser can't open the specified XML file. Make sure the XML file doesn't have syntax errors, and that the XML is well-formed. |
| Can't find an XML Map that corresponds to the data | This problem can occur when you select more than one XML data file to import, and Excel can't find a matching XML Map for one of the files. For the file named in the title bar of the dialog box, import an appropriate schema first, and then try importing the file again. |
| An XML table can't be resized to accommodate data | You're attempting to add rows by importing or appending data to the XML table, but there's no room for the table to expand. An XML table can only expand from the bottom down. For example, there may be an object, such as a picture or another table, immediately below the XML table that prevents the XML table from expanding. Or, for the XML table to expand, it will exceed the Excel row limit of 1,048,576. To fix this problem, rearrange the tables and objects on the worksheet to allow the XML table to expand from the bottom down. |
The specified XML file doesn't refer to a schema
The XML file you're trying to open doesn't refer to an XML schema. To work with the XML data that's in the file, Excel needs a schema based on the contents of the XML file. If that schema is incorrect or insufficient, remove it from your workbook. Then create an XML schema file and edit the XML data file so that the XML data file refers to the schema. For more information, see Map XML elements to cells in an XML Map.
Note: You can't export the Excel inferred schema as a separate XML schema data file (.xsd). Although there are XML schema editors and other methods for creating an XML schema file, you may not have convenient access to them or know how to use them.
Follow these steps to remove the schema that Excel created from your workbook:
-
Click Developer > Source.
If you don't see the Developer tab, see Show the Developer tab.
-
In the XML Source task pane, click XML Maps.
-
In the XML Maps dialog box, click the XML Map Excel created, and click Delete.
Importing multiple XML files that use the same namespace but different schemas doesn't work as expected
When you work with multiple XML data files and XML schemas, you typically create an XML Map for each schema, map the elements you want, and then import each XML data file to the appropriate XML Map. Using the Import command to open multiple XML files with the same namespace, you can only use one XML schema. When you use this command to open multiple XML files that use the same namespace but different schemas, you can get unexpected results. For example, data may get overwritten, or the files won't open.
To import multiple XML files that use the same namespace but different XML schemas, try use the From XML Data Import command (click Data > From Other Sources). This command allows multiple XML files with the same namespace to use multiple XML schemas. Excel creates a unique XML Map for each XML data file you want to import.
Note: If you're importing multiple XML files that don't define a namespace, these XML files are treated as if they use the same namespace.
Show the Developer tab
If you don't see the Developer tab, do the following to display it:
-
In Excel 2013 and Excel 2010:
-
Click File > Options.
-
Click the Customize Ribbon category.
-
Under Main Tabs, check the Developer box, and click OK.
-
-
In Excel 2007:
-
Click the Microsoft Office Button
> Excel Options. -
Click the Popular category.
-
Under Top options for working with Excel, check the Show Developer tab in the Ribbon box, and click OK.
-
No comments:
Post a Comment