{"id":31,"date":"2011-07-28T09:53:38","date_gmt":"2011-07-28T09:53:38","guid":{"rendered":"http:\/\/sixhills-software.com\/blog\/2011\/07\/28\/excel-sparql-ole-db-provider\/"},"modified":"2021-01-25T14:47:35","modified_gmt":"2021-01-25T14:47:35","slug":"excel-sparql-ole-db-provider","status":"publish","type":"page","link":"https:\/\/sixhills-software.com\/blog\/excel-sparql-ole-db-provider\/","title":{"rendered":"SPARQLProv"},"content":{"rendered":"

Excel SPARQL OLE DB Provider<\/strong><\/p>\n

The SPARQL OLE DB Provider allows Excel users to query Semantic Web data using SPARQL. All four types of query are supported, and either SPARQL Results or triples are returned to the spreadsheet through standard OLE DB connection. Up to 16 columns of data may be returned, with up to 255 characters per column.<\/p>\n

Queries are entered either using the Data Connection Wizard, or by opening a pre-existing Office Data Connection (.ODC) file (see examples below). When you have opened the query and displayed the data, then you can edit the query by right clicking on the table produced, and selecting Table->Edit Query. If you edit the query, you will need to refresh the query afterwards.<\/p>\n

This query executed on the DBpedia SPARQL endpoint\u2026<\/p>\n

PREFIX owl: <<\/span>http<\/span>:\/\/www.w3.org\/2002\/07\/owl#><\/span><\/pre>\n
PREFIX xsd: <<\/span>http<\/span>:\/\/www.w3.org\/2001\/XMLSchema#><\/span><\/pre>\n
PREFIX rdfs: <<\/span>http<\/span>:\/\/www.w3.org\/2000\/01\/rdf-schema#><\/span><\/pre>\n
PREFIX rdf: <<\/span>http<\/span>:\/\/www.w3.org\/1999\/02\/22-rdf-syntax-ns#><\/span><\/pre>\n
PREFIX foaf: <<\/span>http<\/span>:\/\/xmlns.com\/foaf\/0.1\/><\/span><\/pre>\n
PREFIX dc: <<\/span>http<\/span>:\/\/purl.org\/dc\/elements\/1.1\/><\/span><\/pre>\n
PREFIX : <<\/span>http<\/span>:\/\/dbpedia.org\/resource\/><\/span><\/pre>\n
PREFIX dbpedia2: <<\/span>http<\/span>:\/\/dbpedia.org\/property\/><\/span><\/pre>\n
PREFIX dbpedia: <<\/span>http<\/span>:\/\/dbpedia.org\/><\/span><\/pre>\n
PREFIX skos: <<\/span>http<\/span>:\/\/www.w3.org\/2004\/02\/skos\/core#><\/span><\/pre>\n
SELECT DISTINCT ?co ?marketCap ?netIncome ?operatingIncome ?revenue<\/pre>\n
WHERE {<\/pre>\n
?co dbpedia2:marketCap ?marketCap;<\/pre>\n
dbpedia2:netIncome ?netIncome;<\/pre>\n
dbpedia2:operatingIncome ?operatingIncome;<\/pre>\n
dbpedia2:revenue ?revenue }<\/pre>\n
LIMIT 30<\/pre>\n
\u2026produces this table of results in Excel\u2026<\/p>\n
\"clip_image002\"<\/a><\/p>\n
Defining a query<\/strong><\/p>\n
The provider takes two parameters for query<\/p>\n
1. Data Source to be queried \u2013<\/p>\n
2. The SPARQL query text<\/p>\n
Parameter: Data Source<\/strong><\/p>\n
Entered in the OLE DB Data Source name field<\/p>\n
General format is <source type>:<source name><\/p>\n
Specifically\u2026<\/p>\n
For remote SPARQL queries<\/p>\n
sparql-http:<<\/span>URI<\/span> of<\/span> sparql<\/span> endpoint<\/span>><\/span><\/pre>\n
For queries executed on RDF files located on the WWW<\/p>\n
url:<<\/span>URI<\/span> of<\/span> RDF<\/span> file<\/span>><\/span><\/pre>\n
For queries executed on local files, in the specified format<\/p>\n
xml:<<\/span>local<\/span> filename<\/span>><\/span><\/pre>\n
n3: <<\/span>local<\/span> filename<\/span>><\/span><\/pre>\n
ntriples: <<\/span>local<\/span> filename<\/span>><\/span><\/pre>\n
For queries executed on triples stored in<\/p>\n
sqlserver:<<\/span>table<\/span>><\/span>:<<\/span>MySQL<\/span> database<\/span> connection<\/span> string<\/span>><\/span><\/pre>\n
sqlite: <<\/span>table<\/span>><\/span>:<<\/span>MySQL<\/span> database<\/span> connection<\/span> string<\/span>><\/span><\/pre>\n
postgresql: <<\/span>table<\/span>><\/span>:<<\/span>MySQL<\/span> database<\/span> connection<\/span> string<\/span>><\/span><\/pre>\n
mysql: <<\/span>table<\/span>><\/span>:<<\/span>MySQL<\/span> database<\/span> connection<\/span> string<\/span>><\/span><\/pre>\n
Note: the SemWeb utility \u201crdfstorage.exe\u201d for loading SQL databases in the necessary format is provided in the application directory<\/p>\n
Parameter: SPARQL query<\/strong><\/p>\n
Query text is entered into the OLE DB command field. ASK, CONSTRUCT, DESCRIBE, SELECT queries are all supported. The query is parsed locally to determine its type.<\/p>\n
Query Examples<\/strong><\/p>\n
Here are some sample ODC files that query DBpedia<\/p>\n
DBpedia 1<\/a><\/p>\n
DBpedia 2<\/a><\/p>\n
DBpedia 3<\/a><\/p>\n
This spreadsheet makes a number of queries on Linked Movie Database and my FOAF file<\/p>\n
Spreadsheet Example<\/a><\/p>\n
Download\/Install<\/strong><\/p>\n
You can download the installer here Download<\/a><\/p>\n
The provider may require installation of .NET Framework 3.5 if not already installed on the target machine.<\/p>\n
If you find any problems, or have any other feedback then please drop me a line at software@sixhills-consulting.com<\/a><\/p>\n
Notice<\/strong><\/p>\n
The provider is currently offered for test and evaluation purposes only, without warranty of any kind. The provider is implemented on top of the SemWeb .NET library by Joshua Tauberer ( http:\/\/razor.occams.info\/code\/semweb\/<\/a>) and subject to the relevant licences of that library.<\/p>\n
\u00a9 2009 Sixhills Consulting Ltd<\/p>\n","protected":false},"excerpt":{"rendered":"
Excel SPARQL OLE DB Provider<\/p>\n
The SPARQL OLE DB Provider allows Excel users to query Semantic Web data using SPARQL. All four types of query are supported, and either SPARQL Results or triples are returned to the spreadsheet through standard OLE DB connection. Up to 16 columns of data may be returned, with up to […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"closed","template":"","meta":[],"_links":{"self":[{"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/pages\/31"}],"collection":[{"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/comments?post=31"}],"version-history":[{"count":6,"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/pages\/31\/revisions"}],"predecessor-version":[{"id":65,"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/pages\/31\/revisions\/65"}],"wp:attachment":[{"href":"https:\/\/sixhills-software.com\/blog\/wp-json\/wp\/v2\/media?parent=31"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}