Correct syntax for loading virtualxl spatialite table?

Question

I believe I have successfully loaded a virtual Excel table into a spatialite database using the SQL below - typed into the qspatialite plugin for QGIS.

create VIRTUAL TABLE MyVirtualTable1 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls");

I'd like to understand how to specify any additional arguments. I can't find any documentation I can understand.

I think that it is possible to specify whether the first row has column names. I imagine it might be possible to provide other arguments. I don't know how (and experiments haven't worked).

Answer 1

You can have one to three arguments in the VirtualXL() call.

If you have only one, its the file name (as you have):

CREATE VIRTUAL TABLE MyVirtualTable1 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls");

If you have two, the first is the file name, and the second is the worksheet index (since Excel supports multiple sheets within the file). If you don't specify that worksheet name (i.e. you just specify the filename), its always the first worksheet. Note that this is zero-based (so the first worksheet is 0, second worksheet is 1, third worksheet is 2, and so on)

CREATE VIRTUAL TABLE MyVirtualTable1 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 2);

If you have three, the first two arguments are for the two argument case, and the third argument specifies whether the first line is titles (1) or not (anything else).

CREATE VIRTUAL TABLE MyVirtualTable1 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 2, 1);

For exact code, see https://www.gaia-gis.it/fossil/libspatialite/artifact/48852afbf76861da31691fe230dc73e561682e24 and look for vXL_create

You also might find the new ImportXLS() function useful if you'd prefer to import rather than work using virtual tables (i.e. copy rather than work on the .xls). See http://www.gaia-gis.it/gaia-sins/spatialite-sql-4.3.0.html for the syntax, which is very similar to the above.

Answer 2

Couldn't find much documentation either, unless you want to dig deep into the sourcecode at the freexl page. But I think I figured it out after playing around a bit.

Accessing sheets start counting from 0:

create VIRTUAL TABLE mvt1 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 0); --Sheet1
create VIRTUAL TABLE mvt2 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 1); --Sheet2
create VIRTUAL TABLE mvt3 USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 2); --Sheet3, and so on.

And to toggle the 'First line contains column names' option, just do:

create VIRTUAL TABLE mvt1h USING VirtualXL("C:\Users\[path-to-file]\TestExcelSheet.xls", 0, 1); --Sheet1 with first line as header