0

I am trying to join the information I have in some Excel files to their corresponding layers in QGIS 3.16. Disclosure: I am pretty new to QGIS.

My workflow is the following:

  • I have an Excel file of 26000 rows and 12 columns with formulas that I am interested in keeping it like this, for possible future changes I might need to do.
  • I use the "Spreadsheets layers" plugin to input that as a layer of type dataset.
  • I join new layer to another that contains the geographic information, by going to the properties menu and using the "Joins" option. Cache join layer in memory is ticked.

The result is that if showing that layer it runs too slow, panning over the map takes minutes to render and cannot work with the data there.

My question is twofold:

  • Is there a way of having that dynamic Join done, so when I am modifying my Excel file won't be a hassle to import and join the data again? (Because if I join the layer by attributes ID it works fine but is not optimal for me as every time I am updating I would have to repeat my process to multiple layers).
  • Am I having an incorrect workflow? I am trying to stay in Excel to be able to update it with new formulas, checking o every field is computed, etc... But I don't know if there is any proper method to do this and that QGIS can handle better than a 26k row join.
Vince
  • 20,017
  • 15
  • 45
  • 64
xavimuse
  • 1
  • 2
    Have you tried omitting the plugin by saving the excel to csv and loading/joining that csv? If so, how is the performance of that workflow? – Erik Jul 28 '21 at 07:59
  • Welcome to Geographic Information Systems! Welcome to GIS SE! We're a little different from other sites; this isn't a discussion forum but a Q&A site. Your questions should as much as possible describe not just what you want to do, but precisely what you have tried and where you are stuck trying that. Please check out our short [tour] for more about how the site works – Ian Turton Jul 28 '21 at 08:22
  • I agree with @Erik. I consider joining Excel files (in Arc or QGIS) to be one of those just-because-you-can-doesnt-mean-you-should sorts of things. If your concern is about updating new formulas and validating computations and you're going to be back and forth between qgis and your data....look into Python. – CreekGeek Jul 28 '21 at 08:36
  • Thanks for your answers. The performance in CSV is the same. The performance problem is when I perform the Join operation from the Properties menu. – xavimuse Aug 01 '21 at 15:20
  • @CreekGeek about the workflow, you mean like importing all the data via Python scripts in QGIS? Thanks – xavimuse Aug 01 '21 at 15:21
  • 1
    Consider the right tool for each workflow step. Spreadsheets are easy/good for 1) manual interaction and 2) convenience with poorly structured data. IMHO, spreadsheets are a QC risk. Higher manual edit frequency = greater error potential. IME, errors are easier to find, constrain, and correct in a scripted environment. The frequency and directionality of your data edits are important too (e.g. Excel>GIS vs. Excel>GIS>Excel). Approach incrementally. Start by automating your import/join. Work up to scripting data transformations, computations, etc. – CreekGeek Aug 01 '21 at 20:09
  • This post may be promising for the import/join portion of your workflow: https://gis.stackexchange.com/a/187067/17482 – CreekGeek Aug 01 '21 at 20:20
  • 1
    also, speed might improve if your Excel updates were pushed to a db (e.g. sqlite) with a standing connection to your QGIS data. e.g. https://www.devart.com/odbc/sqlite/docs/excel.htm or https://towardsdatascience.com/turn-your-excel-workbook-into-a-sqlite-database-bc6d4fd206aa The second link uses xlrd which isn't so great for newer excel formats and/or pandas versions....consider openpyxl instead. – CreekGeek Aug 01 '21 at 20:29
  • Thanks for your answers @CreekGeek. I'll take a look at the different methods. And of course, I think in a scripted environment, everything is easier, but I don't have the enough knowledge on scripting in QGIS and I am on a time constraint. Thanks again – xavimuse Aug 04 '21 at 10:08

0 Answers0