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.
It 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.
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.
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.
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.
The join definition window opens
- 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.
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.
I do not even know how I stopped up here, however I believed this post used to be great. I do not recognize who you are but certainly you’re going to a well-known blogger should you are not already Cheers!
Thank you very much for your comment, thing is I was just publishing in French. Now I have decided to include English as well Spanish. Hope to see you often here.
What your stating is absolutely correct. I know that everyone have to say the identical matter, but I just believe that you place it in a way that everyone can understand. I also love the photographs you put in the following. They match so effectively with what youre attempting to say. Im confident youll get to so many individuals with what youve acquired to say.
Glad you appreciate the effort I put in each and every article I write. Regards, Atilio Francois
Good post! Thanks!!
Thanks for the tutorial however i am having a problem in that after joining the excel sheet it is giving me “NULL” on the values of the excel sheet.
Verify that the two key fields match each other. If there is not a correspondence between the field of the geographical layer and a line of the Excel table, the corresponding field is displayed in the joined table with “NULL” value
Thankyou so much for this!
Thank you very much for this post. Very self explanatory.
I have a remaining question however, once I have join my CSV table and my layer, I cannot use the ‘Graduated’ symbology with the newly added fields. Does it mean the added fields are not recognized as integers?
That’s probably. You can check by clicking on layer properties ->fields
Hi François,
Thanks for this tuto, it helped a lot to solve some requirements.
I have a question regarding the use of Excel files as dataset for survey data collection.
Is there a way to use this Excel file as a survey template (loaded as per your method) to collect data and to save them (i.e the attributes table values) in it?
To summarize, does QGIS write to Excel files?
Thanks for any possible answer.
No, the link is only one way. This is normal because excel does not manage geometries or georeferencing.