How to extract data from an XML file using Google sheets

Create Google sheets

XML is a format that some applications use to share data. For example I use a metronome called ‘Tempo’ on my iPhone. It allows me to share the tempos of all my songs by exporting an ‘XML’ file to an email.

This document can be read by the tempo app and it contains all the information about my tempos but it’s very hard to read and I would like to share the tempos with other people in a clearer format.

Here’s how to get the data out of such a file and into a spreadsheet.  This should work for any XML file that you need to parse.

If I look at the exported ‘XML’ file it looks something like this:

XML file

I can see the data. It is obvious here that the tempo for “Amazing Grace” is “63”.

But is not a very user-friendly format!

You can ‘parse’ this data using Google sheets.

Here’s how.

 

  1. Copy the XML file to your Google Drive.

To do this you will need to have Google Drive set up. Just copy the XML file into your Google Drive folder.

 

2. Share the XML file with Google Drive

 

Just go to the file in Google Drive and select Link Sharing and ‘ON – Anyone with a link’

Share Google file

 

3. Get the web address of the Google Drive file.

 

To do this go into Google Drive in Chrome, right click on ‘get shareable link.’

This will copy something to your clipboard that looks something like this:

https://drive.google.com/file/d/1vBz2fr177V5-cjn6ZOusKn3sVLFTVyLt/view?usp=sharing

This is the address of the Google file but you do not need the entire address unit just need the filename.  It is the selection in bold above.

Select the portion of the filename that is just the filename.

In this example it is: 1vBz2fr177V5-cjn6ZOusKn3sVLFTVyLt

 

4. Open a new Google Sheet  and paste the following text into the first cell of the document:

 

=importxml(“https://drive.google.com/uc?id=###FILEID###&export=download”, “//dict”)

(Replace ###FILEID### with the filename of your file.)

So in my case it looked like this:

=importxml(“https://drive.google.com/uc?id=1vBz2fr177V5-cjn6ZOusKn3sVLFTVyLt&export=download”, “//dict”)

 

5. This should import your XML file into the Google spreadsheet.

My results  in Google Sheets looked like this:

Parsed XML file

As you can see it has extracted the information out of the XML file and pasted it into the Google sheet automatically!

I can now copy this information and paste it into another spreadsheet or word processor.

 

Note: You may need to play around with the second variable in the importxml command. (e.g. “//dict”)

It’s called an ‘xpath’.

I used “//dict”  to extract the entire contents of the file but you can use this to select the exact contents that you want.

For more information on the syntax of this path command read this page on how to format an Xpath.

 

 

 

 

 

 

 

 

 

Posted

Comments

11 responses to “How to extract data from an XML file using Google sheets”

  1. Cameron

    Hey, I found this helpful, but FYI the quotes on this line are not valid in Google Sheets

    > =importxml(“https://drive.google.com/uc?id=###FILEID###&export=download”, “//dict”)

    They should be “, not “ or ”. Subtle, but fatal.

  2. Leonard

    Cant get it to work – dont understand the quotes reference. So what should the line say?

  3. Stef

    I don’t believe this works anymore. I’ve tried several formulas and all of them are no go. Even Google’s. And they don’t use quotes in their example at all. Any chance our XML file is whacked?

  4. todd

    does not work. don’t understand the deal with the quotes. any updates to get this to work?

  5. TL

    The comment about the quotes is that the text uses “smart quotes” like “ or ” but the string you insert into Google Sheets must use ascii ” characters. So use this:

    =importxml(“https://drive.google.com/uc?id=###FILEID###&export=download”, “//dict”)

    1. Thanks TL – good point.

  6. Robert

    Hi!

    I cannot get this work in 2020.

    When using xpath “/” to select the root of the imported “XML file” I can see the HTML of the Google Drive landing page “Meet Google Drive – One place for all your files…”.

    This is very strange while pasting the same URL into a web browser the XML file is downloaded as expected.

    Cheers,
    Robert

    1. Rezoan

      same here.

      1. Eugene

        Same thing, doesn’t work

  7. temps

    great cookbook! worked first time for me. Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.