I am truly a MS Office to Google Docs convertee, but there is a handful of things that make me wish sometime I kept that office install (if only for a moment). One of those niggling little issues is the ability to import XML. Back in the bad old Office days I use to just import XML and then use the old text to columns to format it, but unfortunately Docs wont let you do this.
To be honest moments like this make me feel a bit like Homer when he needs to ask Mr Burns for his job back:
However all is not lost because all you need to do is use Google Docs importXML tool:
https://support.google.com/docs/answer/3093342?hl=en
Now to be honest I find this formula a little tricky. In this case we are going to look at how to import an XML sitemap doc like the one you would download from a site like this:
https://www.xml-sitemaps.com/
Once you have generated your xml sitemap open it up and clear out the top few lines so it reads something like this:
[dt_code]<?xml version=”1.0″ encoding=”UTF-8″?>[/dt_code]
Then I like to clean up the XML file a bit by removing this line:
[dt_code]<urlset xsi:schemaLocation=”http://www.sitemaps.org/schemas/sitemap/0.9 http://www.sitemaps.org/schemas/sitemap/0.9/sitemap.xsd”>[/dt_code]
and replacing it with something like this:
[dt_code]<urlset>[/dt_code]
Why are we cleaning the XML doc up?
It seems like XML sitemaps adds a bunch of schema to the xml file which seems to make it unreadable by google docs, I found that cleaning all this stuff up means you can import it into google docs
Now all we need to do is upload the file to a public facing server, you can do this anyway you want and place it anywhere you want, I usually just ftp into a clients home directory and upload it there. The reason we do this is that google docs needs a url for the xml file as you will see in the next step:
Then paste this formula into any cell in google docs:
=importXML(“http://yoursite.com/sitemap.xml”,“//url/loc”)
You should now have a clean list of url’s!
Did it work for you? Let me know your feedback below 🙂
