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.
