Do you know what an XML file is? Do you care what an XML file is? Do you know why you might want to export the contents of an XML file to Excel? All is explained in this week’s YouTube video
As a podcast host I recently needed to extract some information from the podcast’s feed file and display it in a table in Excel. A podcast’s feed file is an XML file which, simply put, is a plain text file with an XML extension (as opposed to TXT or CSV). An example of part of a typical podcast feed file is shown below.
Even if you’re not a podcaster, more and more systems are using the XML format for data storage. This video is really about is how to import data from an XML file into Excel and clean that data to make it useable. The process that I demo works on Excel for Windows and Excel for Mac.
Looks like gobbledygook, right? That’s because XML is not designed to be read by a human. Having said that, it’s not difficult to understand. XML is a tag-based language that describes the structure of the data, for example:
<item>
<title>Episode 2: Classic Vanilla</title>
<pubDate>Tue, 08 Jan 2023 00:00:00 GMT</pubDate>
</item>
<item>
<title>Episode 2: Chocolate Delights</title>
<pubDate>Tue, 15 Jan 2023 00:00:00 GMT</pubDate>
</item>
This defines an item (in this case a podcast episode). Each “item” has 2 attributes: title and pubDate, which describe the episode title and publish date of the podcast.
I needed specific pieces of information (attributes) about each episode of the podcast to be stored in a Table in Excel but didn’t really want to have to manually copy and paste from the XML file.
Power Query to the rescue!