How can I convert an XML File to Excel

by

You can import XML data into mapped cells using an XML Map that you have previously built, but there are also a number of commands and techniques that you can use to import XML data without an XML Map.

To import XML data into mapped cells if you have an XML map, follow these steps:

  1. Choose a mapped cell from the XML Map.
  2. Select IMPORT under DEVELOPER.
  3. Find and pick the XML data file (.xml) that you wish to import, then click IMPORT on the IMPORT XML dialog box.

Open an XML table by importing an XML data file.

Bring in several XML data files

few XML data files to be imported as external data

To import data from an XML data file, open it.

Find and pick the XML data file (.xml) that you wish to import, then click IMPORT on the IMPORT XML dialog box.

Choose one of the following actions in the IMPORT DATA dialog box:

  • To import the data from the XML data file into an XML table in your worksheet at the designated cell location, select XML TABLE IN EXISTING WORKSHEET.
  • To import the contents of the file into an XML table in a new worksheet, select XML TABLE IN NEW WORKSHEET, beginning at cell A1. In the XML SOURCE task window, the schema of the XML data file is visible.

Excel deduces the schema from the XML data file if it isn’t referenced in the file.

Click PROPERTIES to open the XML MAP PROPERTIES dialog box and adjust the properties of XML data, including data binding, format, and layout. For instance, by default, importing data will overwrite any existing data in a mapped region; however, this can be customized.

To import several XML files into a single set of mapped cells, select a mapped cell.

Click any unmapped cell anywhere on the spreadsheet to import numerous XML files into different sets of mapped cells.

Find the XML data file (.xml) that you wish to import, then choose it in the IMPORT XML dialog box.

Press Shift and choose the first and last file in the list if the files are consecutive. The XML files’ whole contents will be imported and added to the mapped cells.

If the files are not all together, click each file you wish to import from the list while holding down the Ctrl key.

Press IMPORT.

In the event that non-contiguous files are selected, the IMPORTING .XML dialog box is displayed. For every file you’re importing, choose the XML Map that goes with the XML data file.

Choose USE THIS XML MAP FOR ALL SELECTED FILES OF THIS SCHEMA to apply a single map to all of the selected files that haven’t been imported yet.

The FROM XML DATA IMPORT command can be used to import numerous XML files with different XML schemas but the same namespace.

In case you have a Microsoft 365 subscription and are using Excel, select DATA > GET DATA > FROM FILE > FROM XML.

To import XML data, select DATA > FROM OTHER SOURCES > FROM XML DATA IMPORT in Excel 2016 or before.

Navigate to the drive, folder, or URL on the Internet containing the XML data file (.xml) that has to be imported.

Click OPEN after selecting the file.

Choose one of the following actions in the IMPORT DATA dialog box:

  • XML Table in Currently Open Worksheet: The file’s contents are imported into a brand-new worksheet’s XML table. Excel deduces the schema from the XML data file if it isn’t referenced in the file.
  • CURRENT WORKSHEET: A two-dimensional table with rows and columns that displays data in rows beneath the column headings and XML tags as column headers is created when the XML data is imported. The root node, which is the initial element, is shown in the designated cell location and functions as a title. The remaining tags are arranged in the second row alphabetically. You cannot utilize an XML Map in this scenario, and Excel does not infer a schema.
  • A NEW SHEET: Excel creates a new worksheet in your workbook and places the XML data in the worksheet’s upper-left corner automatically. Excel deduces the schema from the XML data file if it isn’t referenced in the file.

Click PROPERTIES to open the XML MAP PROPERTIES dialog box and apply formatting, layout, and data binding controls to XML data. For instance, you can modify the default behavior of imported data overwriting existing data in a mapped range.

Click OPEN under FILE.

Click MICROSOFT OFFICE BUTTON [Office button picture] > OPEN if you’re using Excel 2007.

Click the drive, folder, or online destination that contains the file you wish to open from the OPEN dialog box.

Click OPEN after selecting the file.

The file you opened refers to one or more Extensible Stylesheet Language Transformation (XSLT) style sheets, so if the IMPORT XML dialog box displays, select one of the following options:

  • ACCESS THE FILE AND DO NOT USE A STYLE SHEET: A two-dimensional table with rows and columns that displays data in rows beneath the column headings and XML tags as column headers is created when the XML data is imported. The root node, which is the initial element, is shown in the designated cell location and functions as a title. The remaining tags are arranged in the second row alphabetically. You cannot utilize an XML Map in this scenario, and Excel does not infer a schema.
  • APPLY THE STYLE SHEET BELOW (SELECT ONE) AND OPEN THE FILE: After making your selection and clicking OK, apply the desired style sheet. The prepared XML data follows the style sheet that you have chosen.

The XML file lacks XSLT style sheet references if the OPEN XML dialog box shows up. Select one of the following options to open the file:

  • To create an XML table in a new worksheet, click AS AN XML TABLE. The file is imported and its contents added to the XML table. Excel deduces the schema from the XML data file if it isn’t referenced in the file.
  • Press AS AN EXTENDED WORKBOOK. A two-dimensional table with rows and columns that displays data in rows beneath the column headings and XML tags as column headers is created when the XML data is imported. The root node, which is the initial element, is shown in the designated cell location and functions as a title. The remaining tags are arranged in the second row alphabetically. You cannot utilize an XML Map in this scenario, and Excel does not infer a schema.
  • Press USE THE TASK PANE FOR XML SOURCE. In the XML SOURCE task window, the schema of the XML data file is visible. After that, you may drag the schema’s components onto the spreadsheet to map them there.

Navigate to DEVELOPER > SOURCE.

Click XML MAPS in the XML SOURCE task pane.

Click the XML Map Excel created in the XML MAPS dialog box, then select DELETE.

To make the DEVELOPER tab visible if you can’t see it, follow these steps:

Both Excel 2010 and Excel 2013 allow:

  1. Navigate to FILE > OPTIONS.
  2. Select the category named CUSTOMIZE RIBBON.
  3. Click OK after checking the DEVELOPER box under MAIN TABS.

Under Excel 2007:

  1. To access the Excel options, click the Microsoft Office button (Office button image).
  2. Select the POPULAR heading.
  3. Check the SHOW DEVELOPER TAB IN THE RIBBON box under TOP OPTIONS FOR WORKING WITH EXCEL, then click OK.

We’ll then discover how to convert an XML file to an XLSX file.

It is really simple to convert XML data files to Excel; all you have to do is import the data into a worksheet and save the file.

Before storing the file in the XLSX format, you can transform XML data using Excel’s Power Query function.

In addition to files currently on your computer, you can import XML files from the internet if you’re using Windows.

In the event that your Mac or PC does not have Microsoft Excel installed, you can use a text editor to copy and paste an XML file’s contents into an online converter.

In Excel, open a new, blank worksheet.

On the Data tab, click.

Choose "Get Data."

On Windows, choose From File; on Mac, select Text/CSV.

Choose From XML (only to Windows).

Click Next (for Mac) or Import (for Windows) after selecting the XML file.

Pick the name of the XML table and click Load.

Put the file away.

Open a web browser and navigate to https://wtools.io/convert-xml-to-excel.

Use a text editor to view the contents of your XML file.

Make a copy of the XML file’s contents.

In the web form, paste the copied info.

Beneath the form, click the CONVERT & DOWNLOAD option.

Let’s discuss how to transform an XML file into a usable format.

Converting your XML files from XML TO TEXT is one of the easiest ways to give them additional flexibility.

The XML format implements machine- and human-readable data storage in a structured manner.

Numerous XML to text converters can be found online.

This section explains the three simple steps to convert XML to text:

FIRST STEP: OPEN AN XML

  1. PRESENT XML

  2. TRANSFORM XML INTO TEXT

Let’s see how to transform an XML file.

These general procedures would normally need to be followed in order to convert a regular XML file to a customized XML file:

  1. To identify what has to be changed in order to generate the customized XML file, the first step is to study the content and structure of the standard XML file.

  2. You must specify the schema for the customized XML file based on the examination of the standard XML file.

  3. After the customized XML file’s schema has been established, the standard XML file must be changed into the customized XML file using an XML transformation tool.

  4. You must validate the modified XML file after the transformation to make sure it complies with the specified schema.

  5. You must test the customized XML file after it has been prepared to make sure it satisfies the specifications.

These instructions will let you create a customized XML file that satisfies particular requirements from a regular XML file.