Excel

Tabular SPARQL queries against the RDL can readily be included in MS Excel spreadsheets, using Microsoft’s Office Data Connection (ODC) file format for encapsulating web data sources. A live table in MS Excel also works in MS Word. To download an Excel document that demonstrates how to use this approach, containing the query CDD and conversion factors, click this button:

On this page, we go through the steps from writing a query to having an embedded table in a spreadsheet. For more information about ODC data sources, see the Microsoft article Share external data by using an ODC file (Power Query).

The page Tabular content (UoM) describes how to write RDL queries using SPARQL.

The following unit-of-measures query (see here ) will serve as our tabular RDL data source.

PREFIX rdfs: <http://www.w3.org/2000/01/rdf-schema#>
PREFIX lis: <http://rds.posccaesar.org/ontology/lis14/rdl/>
select ?uom ?uom_label {
  ?uom a lis:Scale ; rdfs:label ?uom_label
  filter ( regex(?uom_label, "metre") )
}
order by ?uom_label
limit 5

To embed this in Excel, we need to create a Office Data Connection string. The Fuseki SPARQL front-end provides a “share query” facility that does most of the work. The following button takes you to the query:

On the Fuseki page, click the “share” symbol , located above the query form.

A box will pop up, containing your query in Percent-encoding (URL encoding). Copy this string (using control-C on Windows, command-C on Mac), and paste it into an editor to make some changes. Any editor will work, as long as no newlines are added.

The string of our sample query looks like the following, and two simple changes are needed.

https://rds.posccaesar.org/ontology/fuseki/index.html#query=PREFIX+rdfs%3A+%3Chttp%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23%3E%0APREFIX+lis%3A+%3Chttp%3A%2F%2Frds.posccaesar.org%2Fontology%2Flis14%2Frdl%2F%3E%0Aselect+%3Fuom+%3Fuom_label+%7B%0A++%3Fuom+a+lis%3AScale+%3B+rdfs%3Alabel+%3Fuom_label%0A++filter+(+regex(%3Fuom_label%2C+%22metre%22)+)%0A%7D%0Aorder+by+%3Fuom_label%0Alimit+5

Make the following edits in the string from Fuseki.

• replace index.html# with ontology/sparql?
• append &output=csv to the end

The result should be as follows. Copy this string to the clipboard, as it will be used to retrieve data in Excel.

https://rds.posccaesar.org/ontology/fuseki/ontology/sparql?query=PREFIX+rdfs%3A+%3Chttp%3A%2F%2Fwww.w3.org%2F2000%2F01%2Frdf-schema%23%3E%0APREFIX+lis%3A+%3Chttp%3A%2F%2Frds.posccaesar.org%2Fontology%2Flis14%2Frdl%2F%3E%0Aselect+%3Fuom+%3Fuom_label+%7B%0A++%3Fuom+a+lis%3AScale+%3B+rdfs%3Alabel+%3Fuom_label%0A++filter+(+regex(%3Fuom_label%2C+%22metre%22)+)%0A%7D%0Aorder+by+%3Fuom_label%0Alimit+5&output=csv

In Excel’s Data menu, select From Web.

A dialog box opens; paste the string you edited above into the URL field and click OK.

A new dialog opens, showing a preview of the query results. Click the Load button.

You will see the data in a new worksheet, named after the ODC string.

This result can be improved: to ensure the first row is used as table headers, rather than “Column1” and “Column2”, double-click the new entry under Queries & Connections.

The Power Query Editor opens. Select Use First Row as Headers, then Close and Load.

That’s it! You now have RDL data in Excel, linked to the source by the SPARQL query embedded in the ODC specification. One advantage of this embedding is that the table can be easily updated as the RDL evolves, by right-clicking the table and selecting Refresh.