Shared publicly  - 
 
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  
6
4
juergen jakobitsch's profile photoKingsley Idehen's profile photo
 
there should also be some possibilities with google's calendar. if you click on the down arrow to the right of "other calendars" there's an option "add by url". for this it would be necessary to have an iCal-RDF writer. but it should be doable in a first step to use the csv import via local filesystem and fileupload...
anyways : interesting options...
 
+juergen jakobitsch - in all cases, URLs for these resources are better. Even if they publish makes a virtual resource (something you can do with Virtuoso). Via URLs we just do content negotiation and serve up data in the desired format for the client. I'll more than likely make a demo of this using one of my ODS-Calendar instances :-)
Add a comment...