1

I've been working with an Microsoft Excel Spreadsheet where the rows are different investments and the columns are different variables/criteria (a column could for example be the year of the investment, a brief description of the investment or what budget the investment belongs to). This makes the use of column-filters and pivot tables for reporting/summarising easy.

In addition, most of these investments are georeferenced (most are represented as a polyline and some as points) - however, not all of them are.

The goal is to link the information in the Excel sheet with QGIS so that I have the benefit of the filtering/sorting/etc. of the rows/columns while in Excel as well as exporting atlas maps displaying the investments including key information in QGIS.

So far I've solved this by assigning each investment an unique ID/key in the Excel sheet. I've then made a new sheet named "QGIS" that automatically gathers the most important information from the "main" sheet. More precisely the columns are:

  • "ID" (string), unique ID/key for the investment
  • "2021" (int), budget cost for 2021. (there is column for each year: 2022, 2023, 2024, 2025 and so on)
  • "description" (string), a brief description of the investment
  • "budget" (string), what budget the investment belongs to.

When I'm done editing in Excel, I've saved the "QGIS"-sheet as an ".csv" file.

In QGIS I've made two layers: a point-layer and a line-layer. I've assigned each layer an ID column and then I've joined the Excel sheet to each layer on the ID column.

I've then made multiple duplicates of the "master" point-layer and line-layer based on budget categories, investment year, etc.

This works, but the routine is easily prone to errors, and it's very inefficient.

Is there a more elegant way to connect Excel with QGIS?

I'm very interested in how you would solve this problem.

3
  • 3
    Did you check the QGIS-SpreadSheetLayers Plugin: github.com/camptocamp/QGIS-SpreadSheetLayers and then you can take 'vitual layers' into consideration instead of make multiple copies of your master-layer.
    – eurojam
    Dec 26, 2020 at 9:05
  • Thanks for the tip but I've already tried using the QGIS-SpreadSheetLayers Plugin. However, I encountered some errors when using it, that's why I defaulted back to using a regular CSV-file instead.
    – Simen
    Dec 26, 2020 at 20:02
  • You can filter, sort and pivot in QGIS. Can you add a specific example of what you are doing in excel, maybe it can almost as easily be done in QGIS?
    – BERA
    Dec 27, 2020 at 15:13

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.