Importing live SPARQL data into MS Excel … don’t go there

PowerPivot - Analytics for Twitter screenshot Recently I wanted to import some linked data into Excel 2010 to experiment with the free PowerPivot plugin. I came across PowerPivot via a post by Ann Smarty HOW TO: Search Twitter from Google Spreadsheets and Excel and it looked like potentially a good way to quickly dashboard a spreadsheet. The ‘Analytics for Twitter’ template looks interesting and might be useful for Windows users who have a copy of Excel 2010.

I should probably have immediately walked away given that PowerPivot is so platform dependant, but briefly persevered giving up completely when I couldn’t find an easy way to get a live data feed from a SPARQL query. I wanted to go down the SPARQL route because consuming this in a Google Spreadsheet as part of the OER Visualisation project worked well. I was hoping that I could take one of my original queries, passed in a SPARQL proxy  and use the Excel Data > From Web.

Here are the problems I encountered:

  • ‘From web’ is looking for html rather than csv; so
  • switching SPARQL Proxy output from csv to html resulted in an error message regarding url length; so
  • shortening long url and using got around the error message but get a general error on import; so
  • switched to Kasabi datastore which has some clever XSLT stylesheets to format query as html but was limited to 1000 results; so 
  • published a google spreadsheet with data as html and pointed the ‘From web’ dialog at it but as this is rendered using lots of css didn’t detect the data table; so
  • used Google Query Language to generate a more raw html table but on import heading were entered on row 2.

At this point I used a cell reference starting at row 2 to insert a pivot report, which worked, but found the pivot tools didn’t fit my needs so decided to draw a line under the investigation.

If you search for ‘importing sparql data excel’ you’ll find other peoples woes and some other convoluted workarounds but I’m giving up for now. I may revisit PowerPivot at a later a date.

PS I also tried importing SPARQL using LibreOffice Calc but the insert external data dialog kept hanging on me.