Unknown Secret - Blogger Post into Excel Using XML


I personally faced lot of challenges in when trying to find out ways to extract Blogger data into Excel.  As you all agree once you have the data in excel you can do wonders with the same.  Example you can easily find out the number post you have added month on month / year on year in blogger.

Also, you can find out the post titles and the topic you have already written and the category.  This would open up new opportunity and new topic which will help you to generate organic visit to your site.  Having experienced the  change over and the advantage of extracting the blog data into excel, I am sharing the ways and means to get this done. I have tried my best to share all the details, however there could be some challenges due to different factors which you can comment and I will try to help you out.

**************************

Open Blogger, Click on Setting and Select "Others"



Look out for Import & Backup



Under Content (pages, posts & comments) - Select "Back Up Content"


Download the file...... the download time depends on the size of your blog

Open Excel and Click on “Data”


  • Get Data and then select “From File” and from the Drop Down Click on “From XML”
  • File Open window would open and select the downloaded the Blog XML file
Once you select the XML file, Excel will have a message like “Connecting”


You would be provided with a similar screen


Click on “Load” and it would open in a New Sheet.  A Similar screen would be presented to you in excel



Right Click on “Queries & Connections” and select the “Edit” option from the choices provided


Once you click on Edit, a Query Editor window would appear similar as below

The ICON, you see below will suggest you that it can be expanded


Click on the Expand option on the Table name called “Entry”


Click on “Close & Load”






Further Data would load; however it would not load everything which is for the title
  • Again, Right Click on “Queries & Connections” and select the “Edit” option from the choices provided
  • Now expand the entry.title.Element:Text





Click on “Close & Load”

In the Excel Sheet now, you will have the title



Now, you have the title and the updated date also if you expand the different fields, you will also get the category

Hope this article help you to get the blog details extracted to Excel using XML