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

Question

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

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.

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.

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

Answer 1

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) (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.

this is the Field tab I am referring to

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

Answer 2

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.

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)
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.

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.

Answer 3

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())

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:

Answer 4

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:

Answer 5

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.

Answer 6

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)

Answer 7

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.