#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 in QGIS
  • Name: Spreadsheet Layers plugin
  • Funtion: Load layers from spreadsheet files (.ods, .xls, .xlsx)
  • Description: This plugin adds a Add spreadsheet layer entry in Layer -> Add new Layer menu and a corresponding button in the Layers toolbar. These two links open the same dialog to load a layer from a spreadsheet file (.ods, .xls, .xlsx) with some options (use header at first line, ignore some rows and optionally load geometry from x and y fields). When this dialog is accepted, it creates a new GDAL VRT file in same folder as the source data file and layer name, expanded with a .vrt suffix which is loaded into QGIS using OGR VRT driver. When reusing the same file twice, the dialog loads its values from the existing .vrt file. No need to install additional dependencies.
  • Author: Camptocamp
  • Code Repository: https://github.com/camptocamp/QGIS-SpreadSheetLayers

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 spreadsheet with No Geometry in QGIS
Sample spreadsheet With Geometry in QGIS

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 Plugin in QGIS menu bar
  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 dialog in QGIS
  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.

    Spreadsheet layer loaded in QGIS
  5. Check the contents of the table layer by right-clicking -> Open attribute table.

    Attribute table of loaded spreadsheet layer in QGIS

Loading a spreadsheet layer with geometry

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

    Add Spreadsheet Layer plugin in QGIS menu bar
  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 with geometry field in QGIS
  5. Click OK and the layer should be loaded in QGIS as a point layer.

    Point layer from spreadsheet loaded in QGIS
  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.

    Attribute table of point layer from spreadsheet loaded in QGIS

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:

Sample spreadsheet with rows to skip

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 layer with skipped rows in QGIS

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:
comments powered by Disqus

You may also like:

Towards a spatial analysis of shooting in Philippine basketball (FOSS4G2021)

01 Oct 2021 ᛫ 1 min read

The opposite of free/libre and open source isn't commercial, it's proprietary

27 May 2021 ᛫ 2 min read

Towards a Spatial Analysis of Philippine Basketball: Applications in the UAAP MBT (Season 81) [Part 1]

02 May 2021 ᛫ 4 min read

Win and let win: On being unconventional, openness, and building communities

30 Apr 2021 ᛫ 1 min read

QGIS Styles based on HLURB Land Use Categories and Color Coding (CLUP Guidebook Vol 3, 2014)

15 Dec 2020 ᛫ 2 min read

Support BNHR

If you find my website or any of the materials I share useful, you can consider donating to the cause below.

Donate and support BNHR

BNHR

[email protected]

Creative Commons License
Except when explicitly stated otherwise, this work and its contents by Ben Hur S. Pintor is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License.
Other works (software, source code, etc.) referenced in this website are under their own respective licenses.
This site is powered by Jekyll and hosted on Github (view source)