XML is a common file type for storing and transferring data. In this article, we are going to show you how to import XML files into Excel using two methods: First is a more "direct method" and the other is the Power Query approach.
Let take a closer look at how you can import XML files into Excel.
Direct importing
This method works in Excel versions 2007 or newer. You need to make the Developer tab visible in the Ribbon to use this feature. The XML section in the Developer menu allows you to import, export and manage the XML files.
Once the Developer tab is enabled, click the Import button in the XML section. Select your XML file in the File dialog, and click Import.
If you have not defined a schema beforehand, Excel will give you a warning and offer to create one for you.
If you do not have a schema file listed with the XSD extension, click the OK button to let Excel do this. If, for some reason Excel fails at this step, try the other approach below.
Next, Excel will ask you where you want to import the workbook. You can select either a cell in an existing worksheet or create a new one.
Clicking the OK button completes the importing process. Once the XML is imported, you can manage it from the same section, and update the data if the resource file is changed. Refresh (update) buttons can be found under both the Data or Table Design tabs of the Ribbon, as well as the right-click menu of the table.
Importing XML files by using Power Query
Power Query is a powerful tool for importing and querying data, supported in Excel versions 2010 or newer. If you are using the 2010 or 2013 versions, you can download it as an add-in. Excel has implemented this feature into the base version starting with the 2016 version.
- If you're using Excel with a Microsoft 365 subscription, follow Data > Get Data > From File > From XML.
If you're using Excel 2013 or earlier, follow Power Query > From File > From XML.
- Select your file under Import Data and click Import.
- Navigator displays a preview of your XML
- If you are satisfied with the preview, click the Load button to import the data.
If you want to query data before adding into your spreadsheet, click Transform Data to open Power Query window.
You can access Power Query window any time by right-clicking the query in the Queries & Connections pane.
