XML and Excel

Pages in this article

XML in Excel

Let's get a bit more practical and use Excel to do something with XML files. With Excel 2003/2007/2010/2013 importing XML data is very simple. Unfortunately, the XML facilities are only available in Excel 2007 and up and in the Microsoft Office Professional or ultimate versions of Excel 2003, or when Excel 2003 is bought as a standalone package. When you select File (Office button in Excel 2007 and up), Open from Excel, then you will see xml file types are shown along with the available Excel files:

By default, XML files show when you try to open a file with Excel
Figure 2: The File, Open window of Excel 2007 and up.

Selecting the file test-en.xml causes a small window to appear (see figure 3).

The Open XML selection dialog of Excel
Figure 3: Excel asking what to do with the XML file

By selecting the third option Excel will analyse the structure of the XML file and present the structure in the taskpane (figure 4). Excel does not open the XML file at this moment.

Figure 4, The structure of the xml file shows up in the taskpane

The elements of the XML file can now be tied to cells on the sheet, simply by dragging them from the task pane to a cell in Excel. See Figure 5 and 6.

Figure 5: Dragging an element to an Excel cell

Figure 6: Result after dropping the element

By dragging an element which contains multiple other elements (or by selecting multiple elements in the taskpane using control+click or shift+click) all elements are placed next to each other as a list:(see figure 7).

Figure 7: List produced by dragging the "employee" element to the sheet

Now a template has been defined which can import data from XML files which have the same structure as the one we "opened" in Excel. We can import the data from test_en.xml, simply by pressing the refresh button on the "External Table Data" group of the custom "Table tools, design" tab of the ribbon in Excel 2007 and up, or by clicking the Refresh XML data button on the List toolbar of Excel 2003 (see figure 8 and 9). The data from test_en.xml will be read into our newly created template. See figure 10.

Figure 8: Refresh Data using the ribbon

Figure 9: Button "Refresh XML data"on the "List" toolbar

Figure 10: Result of importing the data

To import a different file into the same structure, use the connection properties dialog, which you can access by clicking the dropdown arrow below the refresh button (see figure 11):

Changing connection properties to Import another XML file into Excel

Figure 11: Changing connection properties to Import another XML file into Excel

After clicking connection properties, click the Details tab on the dialog that shows up and you can hit the browse button to access a different XML file. Or -in Excel 2003- Simply press the "Import XML" button on the List toolbar.

Comments

Showing last 8 comments of 203 in total (Show All Comments):

Comment by: Bruce Schneider (28-8-2017 04:28:18) deeplink to this comment

Think I've cracked the code on Excel not changing data into a table format automatically when drag & drop mapping from XML Source pane!

Reference

Comment by: When pulling xml data is not coming like a Table (4/22/2015 2:34:45 PM)
deeplink to this comment
Hello, When I am pulling fields from XML source into cells, usually it gives me a table with respected header.

But unfortunately for one of the link it is giving like a blue border boxes with hear option (like, handle).

Suggested solution

Excel appears to be looking for multiple <record> folders in the XML Map. By using XML Notebook (free download from Microsoft), I could open a sample XML file from TestLink (I'm trying to import to this) and duplicate one of the lowest level <testcase> folders. In the Easy-Excel demo, they call theirs <record>. As soon as I saved this XML file, opened up my Excel data file, clicked XML Maps..., opened the modified XML map I'd just saved, then dragged the top folder over the upper left data item: BINGO, Excel converted my data file to a table with 18 rows automatically (that's how many rows I had. 2.5 hours on that one, so I hope this will save someone else some time. Best Wishes, Me!

Comment by: Thomas (8-1-2018 16:41:03) deeplink to this comment

This thread is very useful for understanding better the ways to manipulate the XML maps, creat them etc using VBA. But i noticed at least one comment that didnt get and answer and is the same problem I have.

so I have an rss feed mapped from yahoo finance using a url as such

https://feeds.finance.yahoo.com/rss/2.0/headline?s=F

(This is the only element i want to update/change in an existing mapped XML map

Is this possible?

In case I am not asking correctly

what i want to know is it possible for a different url (made with a macro) to somehow import into that already mapped XML schema to simply replace the link and url elements in the already mapped XML map??

I noticed one comment had this code

Sub GetInterestRates()
Dim TargetPath As String
TargetPath = ActiveSheet.Range("S12").Select
ActiveWorkbook.XmlMaps("interestRateCurve_Map").Import URL:=TargetPath
End Sub

so specifically, if i had a cell with a new URL value and a new link value

Is there a simple code for the xml maps already created to simply replace one or two elements then run the regular refresh databinding routine?

Comment by: Jan Karel Pieterse (9-1-2018 08:26:41) deeplink to this comment

Hi Thomas,

This appears to work for me:

ActiveWorkbook.XmlMaps(1).Import "https://feeds.finance.yahoo.com/rss/2.0/headline?s=F", True

So if you have a cell in which the new URL is located you could do something like:

ActiveWorkbook.XmlMaps(1).Import Worksheets("Sheet1").Range("A1").Value, True

Comment by: Thomas (9-1-2018 15:29:45) deeplink to this comment

Outstanding!!!! That worked for me. Thank you so much Jan

Comment by: keepITcool (29-9-2019 04:56:00) deeplink to this comment

please note that you for large tables it really pays to get the count of the records you will be importing and set the range prior to import.

'This takes a whopping 60 secs for 60'000 records x 10 fields
lst.XmlMap.Import "c:\data\dummy.xml"
'This takes less than 5 secs.
lst.Resize lst.Range.Resize(data.count)
lst.XmlMap.Import "c:\data\dummy.xml"

hth!

Comment by: Jan Karel Pieterse (29-9-2019 20:36:00) deeplink to this comment

Hi KeepITCool,

Thanks! Nice to hear from you again.

Comment by: hnodrog (21-1-2021 15:23:00) deeplink to this comment

Is it possible to get Excel to open the XML without the prompt? I want the "Open as Data Table" to be the default behaviour when I right click on my .xml and select "Open with Excel". I thought if Excel inferred the schema, then the next time this would saved within the file and it'd know what to do with it. I have tried creating a schema, and referring to this .xsd from within the xml, so it wouldn't have to infer it next time, but still got the prompt, not sure I did this correctly. Can't find much info anywhere on this, but assume it's a bridge many have crossed.

Comment by: Jan Karel Pieterse (21-1-2021 17:56:00) deeplink to this comment

Hi hnodrog,

Once you've imported the xml into the grid you can simply tell Excel to import another xml using the same schema. This page mentions a way to do that just above the first comment.

Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.