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
Free and Open Source GIS Ramblings 
Pingback: [QGIS 1.x] Les fichiers csvt (GDAL) | Jeux De Fille
Pingback: Open Government Data Wien in QGIS | Free and Open Source GIS Ramblings
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.
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.
> 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.
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!
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?
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…
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!
Maybe the space in the file name causes confusions. Otherwise your data looks fine as far as I can tell.
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?
I’m afraid without seeing the files I cannot provide any more help.
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.
Pingback: Quantum GIS 1.7.3: Alteração no tipo de dado em arquivos CSV
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.
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. “