CSV conversion

Just been pointed at and the blog This looks really useful.

It spurred me to tidy up my own converter using XQuery, although it's neither as powerful or as fast as Joubert Nel's work.

The HTML view uses the sorttable.js script developed by Stuart Langridge - a wonderfully useful and simple-to-use script to allow any table to be sortable by column (although I haven't tested it in all current browsers)

What's great about is that it converts .xls files. I can include it in XQuery to get an XML version of a spreadsheet, so that further analysis can be done. Here I fetch a table of statistics on fires via the site and turn selected data into an N-dimensional XML structure:

let $uri := ""
let $xml := doc(concat("",$uri))
  <Fires  source="{$uri}" table="1">
     {for $i in (0 to 7)
      let $year := 2001 + $i
      for $q in (1 to 4)
       let $row-number  := 17 + $i * 5 + $q
 (: offset of 17 has to be counted  because blank rows are omitted,so row numbers in the xls cant be used directly :)
      let $row := $xml//row[$row-number]
           <fires  year="{$year}" qtr="{$q}" category="dwellings">{data($row/Column_8)}</fires>,
           <fires  year="{$year}" qtr="{$q}" category="otherBuildings">{data($row/Column_10)}</fires>,
           <fires  year="{$year}" qtr="{$q}" category="vehicles">{data($row/Column_12)}</fires>


Parsing the generated XML still needs some work. Indexing into the rows is tricky because blank rows are ignored so that its hard to work out offsets.  Adding a row number attribute , or, better still, including blank rows, would make processing easier.