#PluginFridays: Spreadsheet Layers plugin

27 Jul 2018 ᛫ 4 min read

Share:


I’ve discussed about how powerful, and sometimes underutilized, QGIS plugins are in a previous post. In this series, which I’m aptly calling #PluginFridays, I would like to highlight some of the plugins that I use in hopes that more people become aware of them and the power they bring to QGIS.

If you don’t know how to install plugins yet, you can check-out this post for QGIS 2 and this post for QGIS 3.

This week, I’ll take a look at a simple yet powerful (aren’t all QGIS plugins like that?) QGIS plugin: the Spreadsheets Layers plugin.

The Plugin

You already probably know that you can load vector files, raster files, and even delimited text files like comma-separated value (CSV) files or tab-separated value (TSV) files in QGIS but did you know that you can also load spreadsheet files (like .xls and .xlsx from MS Excel and .ods from LibreOffice)? This is done through the Spreadsheet Layers plugin.

You can install the Spreadsheet Layers plugin via Plugins -> Manage and Install Plugins.

Spreadsheet Layers plugin

How to Use

The Spreadsheet Layers plugin can load sheets with or without geometry attributes. The plugin also allows the user load a worksheet from a spreadsheet with multiple worksheets.

For example, we have a spreadsheet file (SAMPLE SPREADSHEET.ods) with two sheets – one (Sample Spreadsheet NO GEOM) is just a list of Names and Ages of individuals and has no geometry attribute while the other (Sample Spreadsheet WITH GEOM) is a list of households with their latitude and longitude.

Sample Sheet No Geometry
Sample Sheet With Geometry

Both sheets can be loaded in QGIS using the Spreadsheet Layers plugin.

Loading a Spreadsheet Layer Without Geometry

  1. Go to Layer -> Add Layer -> Add spreadsheet layer.

    Add Spreadsheet Layer
  2. Select the spreadsheet file and the sheet to load. Give the layer name. The rows in the spreadsheet file are shown together with their corresponding column headers and data types.

  3. Leave the Geometry Checkbox UNCHECKED.

    Add Spreadsheet Layer
  4. Click OK and the layer should be loaded in QGIS. Note that since there is no spatial reference for this table, it will be loaded as a non-spatial table in QGIS. We can notice this by the layer having a table logo before its name.

    Add Spreadsheet Layer
  5. Check the contents of the table layer by right-clicking -> Open attribute table.

    Add Spreadsheet Layer

Loading a Spreadsheet Layer With Geometry

  1. Go to Layer -> Add Layer -> Add spreadsheet layer.

    Add Spreadsheet Layer
  2. Select the spreadsheet file and the sheet to load. Give the layer name. The rows in the spreadsheet file are shown together with their corresponding column headers and data types.

  3. CHECK the Geometry Checkbox.

  4. Select the fields that correspond to the X and Y coordinates as well as the Reference system that the X and Y coordinates are in. When the Show fields in attribute table checkbox is checked, the X and Y fields will be included in the attribute table.

    Add Spreadsheet Layer
  5. Click OK and the layer should be loaded in QGIS as a point layer.

    Add Spreadsheet Layer
  6. Check the contents of the table layer by right-clicking -> Open attribute table. Notice that there are no fields for Latitude and Longitude because we did not check the Show fields in attribute table option.

    Add Spreadsheet Layer

Other plugin options

When present, the plugin automatically detects headers in the sheet to use as headers for the attribute table. It also detects the data type to use (String, Real, Integer, etc). If needed, you can select the data type of the field directly in the plugin.

Number of lines to ignore is useful when the header is not the first line of the sheet. This option will skip the number of lines indicated and use the first row it encounters after skipping lines as the headers. For example, you have metadata on the first 3 rows, a blank line on the 4th, and the header and data are found starting from the 5th row like the one below:

Spreadsheet SKIP ROWS

When loading this sheet using the plugin, we skip 5 rows (the header is in the 5th row while the data itself is in the 6th row) to get:

Spreadsheet SKIP ROWS

You can then load this as a layer in QGIS.

And that’s the Spreadsheet Layers plugin. Stay tuned for the next #PluginFridays post and if you have any suggestions or questions, feel free to message me or leave a comment below.

If you want to check out more QGIS Plugins, you can visit the Official QGIS Plugins web portal.

Cheers!


Share:


You may also like:

Mapping Icebergs in QGIS



Hillshade in QGIS



#MakeTheSwitch: Choose GeoPackage



Connecting QGIS to Remote Services (WFS, WMS, GeoNode, OSM): Hazard Mapping with Phil-LiDAR 1 and Project NOAH Hazard Maps on the Web