How to join data from Excel to an attribute table in QGis

The present topic is meant as a brief review since the possibility of loading and attach Excel tables was introduced by QGis 1.8.
As we do not follow the general logic, for new users it is not easy to find the answer. So, step by step, how to make a join between a spatial layer (we’ll take an example here with a shapefile) and a non-spatial table like Excel (with or without XY fields). To load an Excel table in QGis it is not necessary to go through a plugin (XY Tools, MMQGIS, …). You will use XY Tools if you want to transform the Excel table with X and Y fields as a spatial layer of points.

By cons if what you want is to join the Excel table to an existing spatial layer through a key (plot number, name of the owner, …) it’s quick and easy.

1- Load the Excel table in QGis

This is the trap. To do this, you will use the Load Vector Layer button.
bouton ajouter une couche vecteurIt must be known because the Excel table is not a vector layer.

Check that “ All files ” is selected, otherwise you will not see Excel files.

sélection du fichier excel à charger

The Excel table then appears in the list of layers. You can open the table in the same way as other tables in the spatial layers loaded in QGis. You can also switch to edit mode, edit the table and save it. You will have updates to your Excel table, in the same way as if you had done them with Excel.

table excel chargée en tant que couche dans qgis

2- Load the spatial layer

Now load the spatial layer to which you want to attach the information contained in the Excel table.
For our example we will load a shapefile with oyster farms.

table d'un shapefile chargée dans qgis

We will now join the Excel table using the ZTABISSEM field of the shapefile layer and the STATION field of the Excel table.

Joining Shapefile – Excel

Double-click the shapefile layer in the Layers panel
The properties window of the layer opens. Select Joints tab and click on the green cross to add a join.

onglet jointure des propriétés de la couche

The join definition window opens

dialogue ajouter une jointure vecftorielle

  • In Join Layer select the Excel table </ li>
  • In join field in the attached layer select the key field of your Excel table
  • In Join field in the target layer select the key of your shapefile
  • Click OK to close the join definition window
  • Click OK to close the Layer Properties window

If you now open the shapefile layer table, you will see that the fields in the Excel table appear after the fields that were already present.

résultat de la jointure excel-shapefile

If you save your project now, you can continue to update your Excel table without QGis, but when you open the project again you will find all the changes made to the Excel table. The join is a dynamic link and the attached table is calculated on the fly at each opening.

Leave a Reply

Your email address will not be published. Required fields are marked *