SPARQL results in spreadsheets

I made a little progress with SPARQL and spreadsheets. On Kingsley Idehen’s advice, revisited OpenOffice (NeoOffice on MacOSX) and used the HTML table import utility.

It turns out that if I have an HTTP URL for a GET query to ARC‘s SPARQL endpoint, and I pass in the non-standard format=htmltab parameter, I get an HTML table which can directly be understood by NeoOffice. I’ll write this up properly another time, but in brief, I hacked the HTML form generator for the endpoint to use GET instead of POST, making it easier to get URLs associated with SPARQL queries. Then in NeoOffice, under “Insert” / “Link to External Data…”, paste in that URL, hit return, select the appropriate HTML table … and the data should import.

Here’s some fragments of FOAF and related info in NeoOffice (from a query that asked for the data source URI, a name and a homepage URI):

Open your data?

I tried this on a version of Excel for MacOSX (an old one, 2001), but failed to get HTML import working. Instead I just copied the info over from NeoOffice, since I wanted to try learning Excel’s Pivot and charting tools. Here is a quick example derrived from an Excel analysis of the RDF properties that appear in the different graphs in my SPARQL store. The imported data was just two columns a GRAPH URI, ?g, and a property URI ?property:

SELECT DISTINCT ?g ?property
GRAPH ?g { ?thing ?property ?value . }
ORDER BY ?property

Excel (and NeoOffice) offer Pivot and charting tools (similar to simplified version of what you’ll find under the Business Intelligence banner elsewhere) that take simple flat tables like this and (as they say) slice, dice, count and summarise. Here’s an unreadable chart I managed to make it generate:

SPARQL stats

Am still learning the ropes here… but I like the idea of combining desktop data analysis and reports with an ARC SPARQL store attached to my blog, and populated with data crawled from my web2ish ‘network neighbourhood’ (ie. FOAF, RSS, XFN, SIOC etc). Work in spare-time progress.

Published by danbri

Click here to type

Join the Conversation


Leave a comment