Importing .CSV data using QGIS

From TOI-Pedia


Introduction

Figure 1. Example of CSV layer in QGIS.
Figure 2. Example of CSV file in Excel.

CSV, or Comma-separated value, data files are a compact form of delimited text files. These files are widely used as a main output for databases as it is readable in most applications. One of the advantages of the CSV structure is the efficient way of data storage as it just uses plain text, lines and commas making it an ideal file format for large datasets. The format can be seen as a simple version of the Excel format.

Example of .CSV file:

Studentnumber,Name,Age,Faculty,Average grade 1,Jan de Vries,22,Architecture,6.1 2,Maria Bovendonck,24,Physics,7.2 3,Tom van Vliet,19,Computer Science,8.2 4,Annabelle de Groot,20,Architecture,6.3 5,Willem van Delft,22,Mechanical Engineering,7.9

Importing CSV data into QGIS

Figure 3. Importing CSV file into QGIS using the Data Source Manager.
Figure 4. Changing delimiter settings in QGIS.
Figure 5. Altering layer properties to make layer more readable.
Figure 6. Reprojection of layer to correct CRS.

This chapter will step-by-step discuss (with the use of an example) the process of importing a .CSV dataset from the municipality of Amsterdam to QGIS. If you want to follow this step-by-step tutorial you can download the dataset (KLIMAAT_OVERSTROMING) from here: https://maps.amsterdam.nl/open_geodata/?k=253.

  • Step 1: Open QGIS and go to your research location. In QGIS go to to the Data Source Manager found under the Layer tab and go to Delimited Text. Check if the Sample Data looks correctly (must look similar as Excel, see figure 3), if this is not the case follow step 2. Next, make sure you have selected the right Geometry CRS, most datasets mention the used coordinate system on their website or download page. Finally, add the CSV file as a layer.


  • Step 2 (optional): In some cases the .CSV file uses a different delimiter. This result in an erroneous reading for applications. Luckily QGIS has a built-in function to select the used delimiter. But first it is important to know which delimiter is used. This could be discussed at the download source or manually be checked. To check what delimiter is used, simply open your .CSV file in a text reading application like notepad (or even better notepad++) and check at which symbol is used to split the table information. Most used delimiters are: ,, ;, :, or even a space or tab. You can also directly import your file in QGIS as discussed, but use the additional import option under the File Format tab called Custom delimiters, illustrated in figure 4. As mentioned before look at the Sample Data tab to make sure your file is split correctly.


  • Step 4: Edit your layer properties to make your import more readable. In this example the Symbology of the layer was changed to Categorized and classified by probability, as seen in figure 5.


  • Step 5: Check the coordinate system of the import and reproject the layer if it is not 28992 as this is the main CRS used in the Netherlands. You can reproject the layer by clicking on the globe next to the CRS number in the far right bottom of your screen, see figure 6.

Corrupted CSV files - quick fixes

Figure 7. CSV translation error in Excel.
Figure 8. Correctly importing CSV into Excel.
  • Step 1: Make sure to check if your .CSV file is correctly opening in Excel (or Apache OpenOffice [free-to-use]). If the file is correct you will see a normal Excel table as seen in figure 2. Sometimes CSV files use different separators (normally used: , or ;) making it more difficult for applications to read the content. If the file still shows errors, like figure 7, make sure to follow the additional step described in step 2.


  • Step 2: To convert your corrupted CSV file to a correctly formatted CSV format you need to work in Excel (or an open-source alternative). Create a new empty Excel file and go to the Data tab. Search for the From Text / CSV function to import your corrupted CSV data. As seen in figure 8, the function finds the used separator and creates a correct output table. Save the new Excel file as a .CSV (use save as function).

Useful Dataset websites

To find an useful datasets for the Netherlands, go to Useful Datasets. The page contains datasets on the main municipalities and provinces.

Personal tools
Actions
Navigation
Tools