The recent stable version of Spatialite, 3.0, supports linking to and importing Excel spreadsheet tables. Read on to see how it’s done.
The developers of spatialite have added a driver for *.xls files (thru the FreeXL library ). You can either link to, or import a single sheet from an Excel file provided that:
- the file is Excel 2003 format (not the newer xml format)
- the table is “cleanly” formated – only data in rows, no empty rows, etc.
- the first row contains either column headers, or straight-away the first data entry
However the columns of data are imported into spatialite without any data type. If you want to specify the data type for each column, my procedure is:
- link to the external table (create a virtualXL table)
- create your own, well defined table
- use the SQL construct “INSERT INTO … SELECT FROM…” in order to copy all data from the linked table into your structured table.
- then run the spatial funtions AddGeometryColumn() and MakePoint() in order to convert the table to a spatial layer
These few steps might seems a bit complex just to get a table of data into spatialite, but this method insures that data will be structured exactly as you want.
If you’re working with spatialite from the command line, here’s the command syntax for loading an excel sheet:
.loadxl <args> Loads a XL spreadsheet (.xls) into a SpatiaLite table
arg_list: xl_path table_name
Note that the worksheet index begins with 0. So if you have more than one sheet in your Excel file, be sure to set the correct index for the worksheet containing the table. The last parameter indicates (by values 0 or 1) whether the first row holds column headers. So for example you could import the first sheet of an excel worksheet called ‘host_trees.xls’, which contains column headers in the first row, with the following command:
spatialite>.loadxl 'host_trees.xls' host_trees 0 1
3770 inserted rows
We’ll follow the full procedure to smoothly import a spreadsheet of longitude,latitude data and create a spatial layer with screen shots from the spatialite_gui. Suppose we begin with a table that looks like this:
First import the table using the VirtualXL button. This creates a link in the spatialite db to the sheet from Excel.
In the next image note that the linked data table has no types for the columns. So we’ll create our premanent table (named here ‘MyHostTrees’)within spatialite, specifying the correct data types, then we will transfer the date over from the virtual table to the permanent one. Follow the steps…
Now we transfer the data from the virtual table to the permanent table using the SQL construct: “INSERT (…) INTO … SELECT … FROM …”. Here’s how it looks:
And here’s the result: