Join data from a CSV file

In a previous article I have shown how data from a text file with points can be displayed as an additional layer on top of a layer consisting of areas. There was no connection between de data from both layers. This article explains how areas in one layer can be coloured based on data from a text file in another layer. As an example the proximity of department stores in Dutch municipalities in 2010 is used.

Not all layers are equal

First it is necessary to discuss a regularly occurring problem in QGIS. Not all layers are treated equally. Layers can have different sources, like a database, shape file or text file. After opening those layers you don’t see any difference at first. The origin of a layer does matter, however. If you join two layers and want to visualise the joined data, you will be confronted with the way QGIS handles data types. Numerical fields are sometimes seen as strings, which can’t be used to visualize data.

Data preparation

From version 1.7, it is easy to join two layers, by means of the ‘Joins’ tab on the ‘Layer properties’ dialogue window. A layer added with ‘Add delimited text layer’ cannot be joined directly. It is possible to use an additional step, by saving the layer as a shapefile. In this article another method is used.

There is another way to import a text file in QGIS. It’s a bit confusing that you need to use the butten ‘Add vector layer’ for this. The preparation of the data consists of two steps.

The first step is to make a CSV file, containing at least a column to be used to join both layers (in this case a municipality code). Further, you need one or more columns with the data you want to display (in this case the proximity of supermarkets and department stores). Co-ordinates are not required.

The second step is to make a CSVT file, in which the data types in the CSV-file are specified. This is a text file containing just one line. For each column the data type is put between double quotes. Specifying field width and precision is optional. The CSVT file forces QGIS to treat numerical fields really as numerical.

Now the CSV file can be opened in QGIS. Click on the button ‘Add vector layer’. Check if the right encoding is used for your language. If not, special characters will not be displayed properly. Next click on ‘Browse’.

Choose the right filter (‘Comma Separated Value’ or ‘All files’), in order to see your file. You have to select the file with the extension ‘.csv’.

Join layers

The new layer will appear in the layer list. The symbol indicates it contains only non-spatial data. Now open the shapefile holding your areas (in this case the municipalities of 2010). Double click on the layer, go to the ‘Joins’ tab en click on the plus sign. Select the right layer and pick the fields to be used for the join.

After clicking ‘OK’, the layer will appear on the list. The data types of the joined layer can be checked on ‘Fields’ tab. The values can be examined in the attribute table.

Now the data are joined to the municipality map. The map file itself is unchanged. (If you want to add certain data to your area shapefile permanently, you can save it with a different name.)

Colouring areas

Colours can be applied to the map with the usual method. Automatic classification doesn’t always work. In that case you have to add the lower and upper values for each category yourself

Other presentations

There are more ways to present the joined data, but not all options will work. Pie charts will be no problem if you use the ‘Diagrams’ tab, but the ‘Overlay’ tab ignores the joined data. In order to display circles, pie charts or bar charts, joining layers isn’t really necessary. ‘Add delimited text layer’ is a better option for that. In that case your text file must contain co-ordinates. An additional advantage of that is more influence on the position of the symbols.

11 thoughts on “Join data from a CSV file”

  1. Since QGis 1.8.0, you can directly open XLS files from QGis. Just open an XLS file as a vector (using the All files filter). You do not need to convert those files to CSV format. Data types from XLS files are also well managed by QGis (means integers are found as integers, string as strings, etc.) and you do not need to build a CSVT file anymore.

  2. Thanks, this works fine. However if you update the CSV file with new data is there anyway to get the project to refresh, reread the original csv file etc. Otherwise the only way around this seems to be to edit the joined attribute table if you do not wish to recreate the labels colour schemes etc.

    Jeff Wilson

    1. If I change a value in a CSV file and open the project again, the area gets a different colour, as I would expect.

  3. Ik heb met de bovenstaande info een joint gemaakt, ik heb een database met stage adressen van studenten, echter er zijn studenten die in dezelfde gemeente stagelopen, bij de joint zie ik de dubbelingen niet. Hoe kan ik dit beter doen?

    1. Bij het koppelen van gegevens aan een gemeentekaart is het de bedoeling dat iedere gemeente maar een keer voorkomt. Je tekstbestand moet dus geen individuele studenten bevatten, maar per gemeente een getal voor het aantal studenten.

      QGIS is overigens sinds ik dit artikel schreef sterk verbeterd. Om een csv-bestand direct te kunnen koppelen, is geen csvt-bestand meer nodig. Ik werk nu zelf altijd met tabs als scheidingsteken. Dat is ook wat je krijgt als je vanuit bijv. Excel een selectie in een tekst editor zoals Kladblok/Notepad plakt.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>