Simple Guide for Google Spreadsheet Data Imports via SPARQL

This is a simple guide for importing Linked Data into SPARQL using SPARQL Protocol URLs. Note, unlike my prior demos [1], I am going to use the ImportData() as opposed to ImportHtml() function.

Why is this important?

Spreadsheets are eternally powerful productivity tools. For instance, showcasing the use of an ODBC Data Source Name (DSN) as the basis for binding and importing data into Excel en route to making Pivot Tables remains ODBC's ultimately value proposition showcase. Now, we can unshackle this capability from Excel and simply repeat the patter at Web scale via Google Spreadsheet. In addition, your Google Spreadsheet becomes a powerful Linked Data exploration entry point, all you have to do is click on one of the links in the spreadsheet for full effect.

Steps

1. Go to the HTML UI of your SPARQL Endpoint -- in this case I am using DBpedia SPARQL endpoint (which means Virtuoso's SPARQL Service)

2. Enter your SPARQL Query choosing CSV as your output format -- for this exercise we use SELECT but note it works just as well for more sophisticated DESCRIBE and CONSTRUCT queries

3. Copy your SPARQL Results URL and then paste into the ImportData() function as basis for a Google Spreadsheet formula -- the function signature is ImportData(URL) so usage patter is: ImportData("{SPARQL-Protocol-based-Query-Results-URL}").

I've attached screenshots to this post that showcase steps 1-3 above.

Additional Information

1. http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=select+distinct+%3Fperson+%28sql%3AGROUP_CONCAT%28%3Fgenre%2C+%22+%2C+%22%29%29+as+%3Fgenres+%3Fdied+%3Fborn%0D%0A%28+if+%28%0D%0A++%28datatype+%28%3Fborn%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29+and%0D%0A++%28datatype+%28%3Fdied%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29%2C%0D%0A++bif%3Adatediff%28%27year%27%2Cxsd%3AdateTime%28str%28%3Fborn%29%29%2Cxsd%3AdateTime%28str%28%3Fdied%29%29%29%2C%0D%0A++%22error%22+%29+%29+as+%3Fage%0D%0Awhere+%7B+%7B%0D%0Aselect+distinct+%3Fperson+%3Fgenre+%3Fdied+%3Fborn+from+%3Chttp%3A%2F%2Fdbpedia.org%3E+where+%0D%0A%7B+%0D%0A%3Fperson+a+%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FMusicalArtist%3E+%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2Fgenre%3E+%3Fgenre%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FdeathDate%3E+%3Fdied%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FbirthDate%3E+%3Fborn+.%0D%0A%0D%0A%0D%0A%7D%0D%0Aorder+by+desc+%28+%3CLONG%3A%3AIRI_RANK%3E+%28%3Fperson%29+%29+limit+50++%7D%7D&format=text%2Fcsv&timeout=15000&debug=on -- SPARQL Query Results URL (note: this works with Excel and any other package that can handle CSV files/resources)


2. http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&qtxt=select+distinct+%3Fperson+%28sql%3AGROUP_CONCAT%28%3Fgenre%2C+%22+%2C+%22%29%29+as+%3Fgenres+%3Fdied+%3Fborn%0D%0A%28+if+%28%0D%0A++%28datatype+%28%3Fborn%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29+and%0D%0A++%28datatype+%28%3Fdied%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29%2C%0D%0A++bif%3Adatediff%28%27year%27%2Cxsd%3AdateTime%28str%28%3Fborn%29%29%2Cxsd%3AdateTime%28str%28%3Fdied%29%29%29%2C%0D%0A++%22error%22+%29+%29+as+%3Fage%0D%0Awhere+%7B+%7B%0D%0Aselect+distinct+%3Fperson+%3Fgenre+%3Fdied+%3Fborn+from+%3Chttp%3A%2F%2Fdbpedia.org%3E+where+%0D%0A%7B+%0D%0A%3Fperson+a+%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FMusicalArtist%3E+%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2Fgenre%3E+%3Fgenre%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FdeathDate%3E+%3Fdied%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FbirthDate%3E+%3Fborn+.%0D%0A%0D%0A%0D%0A%7D%0D%0Aorder+by+desc+%28+%3CLONG%3A%3AIRI_RANK%3E+%28%3Fperson%29+%29+limit+50++%7D%7D&format=text%2Fcsv&timeout=0&debug=on -- SPARQL Query Text URL

3. =ImportData("http://dbpedia.org/sparql?default-graph-uri=http%3A%2F%2Fdbpedia.org&query=select+distinct+%3Fperson+%28sql%3AGROUP_CONCAT%28%3Fgenre%2C+%22+%2C+%22%29%29+as+%3Fgenres+%3Fdied+%3Fborn%0D%0A%28+if+%28%0D%0A++%28datatype+%28%3Fborn%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29+and%0D%0A++%28datatype+%28%3Fdied%29+in+%28xsd%3AdateTime%2C+xsd%3Adate%29%29%2C%0D%0A++bif%3Adatediff%28%27year%27%2Cxsd%3AdateTime%28str%28%3Fborn%29%29%2Cxsd%3AdateTime%28str%28%3Fdied%29%29%29%2C%0D%0A++%22error%22+%29+%29+as+%3Fage%0D%0Awhere+%7B+%7B%0D%0Aselect+distinct+%3Fperson+%3Fgenre+%3Fdied+%3Fborn+from+%3Chttp%3A%2F%2Fdbpedia.org%3E+where+%0D%0A%7B+%0D%0A%3Fperson+a+%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FMusicalArtist%3E+%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2Fgenre%3E+%3Fgenre%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FdeathDate%3E+%3Fdied%3B%0D%0A+++%3Chttp%3A%2F%2Fdbpedia.org%2Fontology%2FbirthDate%3E+%3Fborn+.%0D%0A%0D%0A%0D%0A%7D%0D%0Aorder+by+desc+%28+%3CLONG%3A%3AIRI_RANK%3E+%28%3Fperson%29+%29+limit+50++%7D%7D&format=text%2Fcsv&timeout=15000&debug=on") -- actual Google Spreadsheet Formula (just paste as is into a cell of choice in your Google Spreadsheet)

4. https://docs.google.com/spreadsheet/ccc?key=0AihbIyhlsQSxdFNzZzVsMzJOdDh5TDdvR25JSXNLTlE

Links

1. http://goo.gl/LmFR5 -- previous posts about importing data into Google Spreadsheet via the ImportHtml() function

2. http://goo.gl/ovqPL -- detailed post about why and how Linked Data trumps ODBC, JDBC, ADO.NET (Entity Frameworks), JDO etc.. re. Open Data Access & Connectivity .

#LinkedData #Spreadsheet #SPARQL #SemanticWeb #CoRelational #CoSQL #NoSQL #RDBMS #DBMS #BigData #SmartData   #Web30  
PhotoPhotoPhotoPhotoPhoto
5 Photos - View album
Shared publiclyView activity