How to Specify Data Types of CSV Columns for Use in QGIS

Foreword (added 2015-04-11)

There are two main options to load .csv files into QGIS

  • “Add delimited text layer” will guess the column data types. Use the “no geometry” option to load CSVs without coordinates.
  • “Add vector layer” by default interprets all columns as strings.

The following post describes how to change the default behavior of “Add vector layer”.

If you load .csv files through “Add vector layer”, all columns are interpreted as strings. That’s most likely not what you want, but it’s OGR’s default behaviour:

The OGR CSV driver returns all attribute columns with a type of string if no field type information file (with .csvt extension) is available.

Let’s create a .csvt file then!

The .csvt file has to have the same name as the .csv file it describes. (The same concept is used for Shapefiles.) It enables definition of the following data types: Integer, Real, String, Date (YYYY-MM-DD), Time (HH:MM:SS+nn) and DateTime (YYYY-MM-DD HH:MM:SS+nn).

A .csvt file contains only one line and the types for each column have to be quoted and comma separated, e.g.

"Integer","Real","String"

You can even specify width and precision of each column, e.g.

"Integer(6)","Real(5.5)","String(22)"

Read more at: www.gdal.org/ogr/drv_csv.html

Advertisements
19 comments
  1. Alister said:

    That could be an idea for a plugin: generating .csvt files.
    It’s a pity csv layers are read-only.
    N.B. that the delimited text plugin automatically guesses data types, but it is for tables with coordinates.

    • underdark said:

      In my opinion it would be best to improve OGR to guess data types too. But then I don’t know the reason why they have solved the data type problem this way.

  2. Alister said:

    > But then I don’t know the reason why they have solved the data type problem this way.

    Presumably just to avoid the problem of it guessing wrong.

  3. Fabrizio said:

    Hello,
    let’s start saying that i am not expert at all…but i still di dnot figure it out how this .csvt file generatinon works…first of all what is a.csvt file? I do not find this extension in any place…through notepad I created a .txt file with same name like the .csv file but it doesn’t work…how can I eventually change the .txt into a .csvt? As far as I know I cannot simply rename the file…

    Any help, for beginners :), would be more than welcomed!

    • underdark said:

      Of course you can simply rename the file. If you are on Windows (I guess you are, otherwise you could edit the extensions easily), you have to first tell Windows to “show file extensions for known file types”.

      • I am having a problem loading the .csvt file into QGIS. I created it in a text editor and named the file with a .csvt extension, but when I add it as a vector layer, it says: /home/nate/GIS/parcels1.csvt is not a valid or recognized data source

        What could I be doing wrong?

      • underdark said:

        You have to load the .csv file, not the .csvt file.

      • Thanks! I’m still having trouble getting my data to sync up but I suspect that’s a different issue…

  4. Hendrik said:

    Hello,

    it still doesn’t work when i try it. :-(

    my files look like this:

    EW_B_2006 B.csv

    RAUMID;NAME;ZEIT;ALTERSGR;ANZAHL
    040617;F G;2006; unter 3;81
    110512;R B;2006; unter 3;2089
    110101;M, W F;2006; unter 3;4007

    EW_B_2006 B.csvt

    “Integer”,“String”,“Integer”,“String”,“Integer”

    when i import the csv-file to qgis all column types are set to “String” and i don’t know why.

    what could I be doing wrong?

    Thank you!

    • underdark said:

      Maybe the space in the file name causes confusions. Otherwise your data looks fine as far as I can tell.

      • Hendrik said:

        I tried it again with exemple-files from http://gothos.info/2011/04/joining-csv-files-in-qgis/ but qgis sets all types to “String” :/

        maybe i make a mistake creating the files?
        I have an excel xls-file and i save this with excel as an csv-file.
        then I open it with the editior an save it as a csvt-file by replace the preset “.txt” from the editor with “.csvt”.

        then i load the csv with qgis 1.7.3 by add it as vector layer and join it with my shapefile.

        i thought that import and joining tables is one of the simplest and basic functions a gis program should be able to run easily. and this is not what i call easy :(

        anyway where is my mistake?

      • underdark said:

        I’m afraid without seeing the files I cannot provide any more help.

      • Hendrik said:

        Ok, I didn’t solve the problem but i find another way.
        I find an old spss version on my old laptop so a can create a dbf-file and it works fine with qgis.

  5. Matthew Sussman said:

    How would you suggest doing this with 250 columns of data? QGIS seems to be picky about the csvt file and a text file with text surrounded by quotes and separated by commas but no spaces is not what you get when you save something in CSV format from excel.

    • I’d probably use “Add delimited Text Layer” plugin instead because it guesses the data type. You’ll have to set some dummy coordinate columns though. The alternative would be to write a small script to create the .csvt.

  6. pieter said:

    hi,

    about the problem of creating/opening a CSV with Excell (I’ve got this information from http://www.baruch.cuny.edu/geoportal/practicum/gis_prac_4.html):

    “You should be careful when opening csv files, or any delimited text files, in Microsoft Excel. Excel imports the CSV and automatically saves any value that looks like a number as a number. This has the unintended effect of rendering identifiers like FIPS codes and ZIP Codes useless, as zeros are dropped from preceding values. Even if you open a CSV file in Excel and don’t save it, the file is still altered. In order to convert values back to their original form you would have to use the concatenate formula on any values that are less than their expected length and pad them with zeros. You can avoid these problems by working with CSV in text editors, or by using other spreadsheet programs. For example, when you open a csv file in Open Office Calc you are prompted to designate the data type for each field. Designate your identifiers as text / strings and they will be preserved. “

%d bloggers like this: