1

I am struggling with copying the data from one attribute data table to another. I have a few columns in my data attribute table, which I want to update. I can do it one by one, but its a long process, as I have about 150 records.

Attribute data table content in QGIS

I decided, than it will be quicker if I do it in Excel instead, then import them into QGIS and replace the data in existing attribute data table.

Uploaded content in Excel

I decided to do it in the following steps: 1. Export attribute data table to .csv 2. Make autofill in Excel 3. Import the new attribute data table to the QGIS again As an import I used option: Layer-Add Layer - Add delimited text layer. After loading layer I have it own attribute layer, which I would like to copy to the attribute layer of my existing layer as per image below:

enter image description here

1 - my comma delimited layer imported 2 - my existing shapefile layer, where I would like to replace the attribute layer from this 1st one.

I found some solutions here:

Joining data from Excel to attribute table in QGIS without creating duplicates?

and here:

How to copy information from Excel into QGIS Attribute-Table?

but they don't really solve my issue. I wish I could use the table joining option, but it looks like the differ columns are to be added instead to replace all with the same names.

Can anyone help me to autofill the attribute data table or fetch it from Excel?

7
  • 2
    Please be more specific. What does "autofill" mean in this context? Can you provide a general concept or even formulas you use? Have you tried join attributes by field after importing the excel file? Have you tried exporting the data including WKT-geometries, which later can be loaded again omitting the need for any joins?
    – Erik
    Nov 13, 2019 at 10:41
  • I have the same columns in these 2 tables and I want to fill them. I can quickly fill them in Excel, but once I imported them I got the problem with joining attributes by field.
    – Geographos
    Nov 13, 2019 at 10:52
  • Fill them with what? We are trying to understand why you have to use Excel. we can't tell you how to do your autofill unless you tell us what you are autofilling (dates, sequential Ids???) Nov 13, 2019 at 10:53
  • Still not specific enough. We have no idea what "update" means in this context. Is it a simple autofill? Is it valuebased? Is it copy&paste from a pdf?
    – Erik
    Nov 13, 2019 at 11:04
  • I already updated my question. I have a data in the attribute data table, which must be updated. Because is too long to do it one by one I decided to export this data table to Excel, edit there and import to QGIS. Afterwsrds replace the excel data with an existing data in the attribute data table. The column names remain the same, only records change.
    – Geographos
    Nov 13, 2019 at 11:04

2 Answers 2

2

There are more elegant ways to do this with joins, etc., but as an ad-hoc one off, one simple approach that should work is:

  1. Export the original layer as a csv, but making sure under Layer Options you get the geometry exported AS_WKT. Your csv file should now consist of one line per feature, with the feature attributes as column, plus an extra first column called WKT representing the feature geometry as a text string in your chosen CRS.

  2. You or others can change, even add/subtract rows, in Excel. Keep the column names (=attribute names) unchanged. You can theoretically change the geometry by editing the WKT string, but this is unlikely to be useful.

  3. Import the edited csv as delimited text, making sure the importer Geometry Definition is set to WKT and pointed at the WKT field (which should be automatic). Make sure the geometry CRS is the same used for the export.

  4. Re-export the imported csv layer in your desired spatial file format (shapefile if you must, or into a geopackage or whatever), probably the same as the original format.

  5. Use the changeLayerSource plug-in to repoint the original layer at the new file from the previous step. If you indeed did not change the layer names, your styling etc will be preserved.

The above will implicitly (by sliding the new file in place of the old file) replace all of the old features with the edited ones, and so won't create duplicates. However, it also carries risks - you could munge stuff you didn't intend to touch if you're careless. If you need to be fancier, for instance you want to treat new features, updated features, and deleted features differently, then you will have to creatively use joins, or SQL.

The important elements for success above are to handhold QGIS to make sure the geometry is exported and then reimported, to make sure the CRS stays the same, and to ensure the data attribute names / column labels are unchanged.

2

Use a formula in QGIS to achieve the same thing. Select the features you want either in the map or in the table view and then click on the calculate field button (abbacus icon) and select the field you want to update then input your forumla in the expression field. This is MUCH easier and safer than fiddling about with Excel and table joins.

You can even select specific rows based on their current data to edit in this way. Use the little E button in the table view and write an expression to select (say all rows where "surveyor" is null)

1
  • 1
    Indeed, I agree with MappaGnosis that if you can use an expression and the field calculator in QGIS, it is easier and safer. My solution below is if you really prefer to do the field updates in Excel rather than QGIS (more complex than the example you've added in your question while we've been answering)
    – Houska
    Nov 13, 2019 at 11:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.