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.
9
  • 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 7: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 8: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 8:36
  • 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 1 '21 at 20:09
  • 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. devart.com/odbc/sqlite/docs/excel.htm or towardsdatascience.com/… The second link uses xlrd which isn't so great for newer excel formats and/or pandas versions....consider openpyxl instead.
    – CreekGeek
    Aug 1 '21 at 20:29

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.