Chapter 16: Mapping a Spreadsheet

The bus company’s feed covered the routes. The stops themselves lived somewhere else: in a spreadsheet the dispatcher kept on the office computer, workbook.xlsx. “Everything is in the Stops tab,” she said. “Don’t mind the title at the top.”

Dr. Portbridge opened it. A banner, an empty line, then a table: one row per stop. It looked a lot like the CSV files from earlier, only with more than one tab, and with real dates in it.

The problem

A spreadsheet is a CSV file with extras: several sheets in one file, a table that does not always start on the first row, and cells that know they hold a number, a date or a formula. The mapping still reads one row at a time and still finds each value by its column name. It only needs to be told which sheet, and which row holds the column names.

The file

The Stops sheet, as the spreadsheet application shows it:

Column opened is formatted as a date, ok holds booleans, n the number of platforms, and f a formula: a stop code computed from the id. The workbook has a second sheet, Info, before this one.

The whole mapping

prefix <http://vocab.org/transit/terms/> as transit

mapping from <package://workbook.xlsx> as XLSX
  sheet "Stops"
  header row 3
  map:
    id     as ?id
    opened as ?opened
    ok     as ?ok
    n      as ?n
  to:
    fact <http://airport.example.com/stop/{?id}> a transit:Stop
      transit:opened {?opened} as date
      transit:open {?ok} as boolean
      transit:platforms {?n} as int

mapping from <package://workbook.xlsx> as XLSX
  sheet "Stops"
  header row 3
  map:
    id as ?id
    f  as ?code
  to:
    fact <http://airport.example.com/code/{?id}> a transit:Code
      transit:value {?code}

There are two mappings over the same sheet: the first builds the stops, the second their codes.

1. The source

mapping from <package://workbook.xlsx> as XLSX names the file and picks the Excel reader. For a LibreOffice .ods file, write as ODS. The selector decides the reader, not the file name.

2. sheet and header row

sheet "Stops" picks the tab by its exact name. Without it the mapping would read the first tab, Info.

header row 3 says the column names are on row 3, numbered as the application numbers its rows. Without it the first non-empty row would be the header, and that is the banner: the mapping would then fail on mapping references unknown column 'id'. Everything above the header is ignored, and the empty row 5 is skipped.

3. map: and to:

From here on it is a CSV mapping: column names on the left of as, one fact template per row, and the usual casts.

What comes out

<http://airport.example.com/stop/6523> rdf:type owl:NamedIndividual
  , transit:Stop
  ; rdfs:isDefinedBy <http://example.org/stops/stops>
  .
<http://airport.example.com/stop/6523> transit:opened "2024-01-01"^^xsd:date .
<http://airport.example.com/stop/6523> transit:open "true"^^xsd:boolean .
<http://airport.example.com/stop/6523> transit:platforms "25"^^xsd:integer .

<http://airport.example.com/code/6523> rdf:type owl:NamedIndividual
  , transit:Code
  ; rdfs:isDefinedBy <http://example.org/stops/stops>
  .
<http://airport.example.com/code/6523> transit:value "13046" .

Look at the values:

• Dates. The date cell binds 2024-01-01, the way it is displayed, so as date gives an xsd:date. Inside the file it is the number 45292; the reader converts any cell formatted as a date.
• Numbers. A spreadsheet stores every number as a decimal, so the 25 in column n is really 25.0. It binds "25", and as int accepts it.
• Booleans. TRUE binds "true", which as boolean reads.
• Formulas. =A4*2 binds its saved result, 13046. Nothing is recalculated.

Without a cast, a cell still gives a plain string, like a CSV cell: the code is "13046", not a number.

Why row 6 is missing

Stop 6524 is in neither mapping, for two different reasons:

• its opened cell holds a date and a time. It binds 2024-01-01T12:00:00, and as date refuses it rather than drop the time without telling you: row 2: cannot cast '2024-01-01T12:00:00' to date for property transit:opened.
• its f cell is an error, #DIV/0!. A row with an error in a bound column is skipped (row 2: cell error #DIV/0! for binding ?code), even when the property is optional: an error is a broken sheet, not a missing value.

row 2 is the second data row, not the application’s row 6. These are warnings: the build goes on with the rows that worked.

The same mappings over the workbook saved as workbook.ods (as ODS) give the same triples, plus a code for 6524 with no value: OpenDocument has no error cells, and LibreOffice saves =1/0 as an empty string.

Spreadsheets are read by radeau build only. The playground and Agrafe cannot open them and report unsupported mapping format 'XLSX'. Word-processor files (.odt, .docx) are not spreadsheets and are not read at all. The syntax reference has every rule.

The dispatcher fixed the formula and changed the time to midnight. Dr. Portbridge rebuilt: two stops, two codes. When the spreadsheet grew a new tab for the night buses, it got a second sheet line, in a second mapping.