14

I have an attribute table with two empty fields in QGIS.

Example_1

I want to import an excel database to fill my empty fields in QGIS. My excel file matches the columns in my QGIS attribute table.

Example_2

I am able to properly join my excel (.CSV) to my shapefile. However,the process, instead of filling the blank fields as I wish, created duplicates. With the help of « Table Manager » I am able to correct the situation, but it requires a lot of time. I am looking for a more efficient way to join my excel data.

Example_3

How can I join my excel file to my attribute table without creating duplicates?

12

I would do the following things to make life easier:

Before you do this, make a backup of your shapefile.

  1. In your shapefile, go to the layer properties / fields and turn on edit mode.
  2. select all Field except the ID field
  3. Delete all fields except the ID Field
  4. add your csv file as a layer in QGIS (Main menu/Layer/Add Layer/ Add delimited text layer)enter image description here (choose no geometry)
  5. in your shapefile, select properties/joins and choose both ID fields for source and target. As described in the other answer to this question.
  6. Save your modified Shapefile.

enter image description here

this is the Field tab I am referring to

enter image description here

Don't forget to toggle editing before and after deleting unnecessary field

12

You will want to join the excel file to the shapefile. You will join them on a common attribute and the result will be a joined layer where each record hold the attributes of both the shapefile and the excel file.

Ok first you load your excel file and your vector later into the layers. I used some test data I made but your setup should look similar to below. enter image description here

Now right click on the layer (in the layers panel) and choose properties, then choose join. First hit the green + sign in the bottom left (Big Red Arrow in the picture below) and it will bring up a new Add Vector Join menu (like below) Here your join layer will be the excel file (so choose the proper excel file & sheet) the join field is the field in the excel file that holds the common attribute to the shapefile. The target field is the matching field in the shapefile. (in my case both common fields happened to be named unit, but if the field was called UNIT_ in the shapefile i would have used that instead of UNIT for my target field)
enter image description here There are a few other options you can mess with on the join, like if you only want to see certain fields, etc... anyway so now they are joined and if you go back and look at the attributes of the shapefile you will see it now has the corresponding attributes of the excel file like below.

enter image description here

Here are a few handy tutorials to show you step by step how to do it:

https://www.mapbox.com/tilemill/docs/guides/joining-data/

http://maps.cga.harvard.edu/qgis/wkshop/join_csv.php

http://qgis.spatialthoughts.com/2012/03/using-tabular-data-in-qgis.html

How to join external tables with a shapefile's attribute table in QGIS?

http://www.digital-geography.com/qgis-tutorial-ii-how-to-join-data-with-shapefiles/#.Vs9vpmHXKUk

and the tutorial ArMoraer mentioned in the comments.

  • I looked at the tutorial mapbox.com/tilemill/docs/guides/joining-data wich was, by the way, very usefull. However, i am still unable to join my DBF file to my shapefile. It suprises me because both of my table structure perfectly match. I seem to missunderstand the « Join field » and the « Target field ». Could you add more details please ? – Laurent Robitaille-Lainesse Feb 26 '16 at 16:46
  • 1
    @Laurent Robitaille-Lainesse I updated my post with a more step by step guide. I joined a test excel file to a polygon shapefile just to make sure my steps were correct. – ed.hank Feb 27 '16 at 0:48
  • I suceeded to properly join my excel file to QGIS. I observe that the « join » option add a new field in the attribute table. Correct me if i am wrong, but it seems impossible to add the data from my excel file to the empty field in my attribute table. – Laurent Robitaille-Lainesse Feb 29 '16 at 13:50
  • 1
    If you have a blank field, then you use the field calculator to populate from the newly joined field to the field have in your table. – ed.hank Feb 29 '16 at 14:39
8

Just to add another method, you can set up a Project macro which when loaded:

  1. Automatically joins your shapefile to your csv
  2. Updates the IP1 and IP2 fields
  3. Removes the joined fields leaving only the shapefile's fields (i.e. no dulplicates)

First, create a project if you haven't already done so and then go to the toolbar:

Project > Project Properties... > Macros

Then use the following code in the def openProject(): function and enter the names of your layers and the fields you want joined. I used "Example" and "spreadsheet" for my shapefile and csv file respectively with the field ID:

from PyQt4.QtCore import *
import qgis
from qgis.core import QgsMapLayerRegistry, QgsVectorJoinInfo

for layer in QgsMapLayerRegistry.instance().mapLayers().values():
    # Change to your shapefile name
    if layer.name() == "Example":
        qgis.utils.iface.setActiveLayer(layer)
        shp = qgis.utils.iface.activeLayer()

for layer in QgsMapLayerRegistry.instance().mapLayers().values():
    # Change to your csv name
    if layer.name() == "spreadsheet":
        qgis.utils.iface.setActiveLayer(layer)
        csv = qgis.utils.iface.activeLayer()

# Set up join parameters
shpField='ID'
csvField='ID'
joinObject = QgsVectorJoinInfo()
joinObject.joinLayerId = csv.id()
joinObject.joinFieldName = csvField
joinObject.targetFieldName = shpField
shp.addJoin(joinObject)

# Define fields to update and joined fields to copy values from
ip1 = shp.fieldNameIndex('IP1') 
ip1_join = shp.fieldNameIndex('spreadsheet_IP1')
ip2 = shp.fieldNameIndex('IP2') 
ip2_join = shp.fieldNameIndex('spreadsheet_IP2')

shp.startEditing()
for feat in shp.getFeatures():
    shp.changeAttributeValue(feat.id(), ip1, feat.attributes()[ip1_join])
    shp.changeAttributeValue(feat.id(), ip2, feat.attributes()[ip2_join])
shp.commitChanges()

# Remove join 
shp.removeJoin(csv.id())

Project macro

Make sure that the layers are not joined, save the project and enable macros by going to the toolbar:

Settings > General > Enable macros


Now when you close the project and edit the csv file, the next time you load the project, the fields should automatically be updated:

Editing csv file

Results

6
+50

My suggestion is to use open source LibreOffice / Open Office to edit your Excel file and make .dbf file. I prepared test workbook for you. Test workbook - link

  1. Open file in Libre Office / Open Office.
  2. Paste to "Excel" sheet data from your excel file.
  3. Paste to "DBF" sheet data from your .dbf file (paste only ID, X, Y) (IP1, IP2 values will be added automatically).
  4. Save as name.dbf file (where name is the same like your shape file name).

Result table without duplicates in QGIS: enter image description here

4

I dont know if there is a direct way to join without duplicate since that .shp file support by attribut in .DBF (database file). This DBF has declaration of column type such as integer, real, string ,etc with detail of its length and precision. The CSV file only has normal column without any type declared. I dont know how large is your file. For me, i will keep the duplicate Fields and then using field caluclator with general formula :

Original Field_x = Duplicate Field_x

Then delete all those duplicated fields (through QGIS or database program)

4

Why are the empty fields there to begin with? Can you start without the empty fields and just use the two columns produced in the join? I think this is the simplest approach. Either that or find a tool similar to the "Load" tool in ArcGIS.

https://desktop.arcgis.com/en/arcmap/latest/extensions/production-mapping/loading-data-into-a-geodatabase.htm

If you are not using a geodatabase, I highly recommend this.

  • I am not using a geodatabase. In fact, i am not familliar with it. – Laurent Robitaille-Lainesse Mar 28 '16 at 14:11
  • 1
    @LaurentRobitaille-Lainesse I highly recommend creating a new file geodatabase, then a new feature class within that, and use this to store your data. I also recommend doing some brief research on geodatabases and feature classes and why we use them, what they offer beyond shapefiles. – Stella Mar 28 '16 at 17:29
3

I believe the easiest way to fix this would be to simply delete the two columns in question in the QGIS table before the join. Then, when you join the shapefile, the two columns you want will not be duplicates and will keep their original column names.

Your Answer

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

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