Keeping leading zeroes in QGIS in big files

Question

I am trying to load a file with many columns into QGIS. With both .txt and .csv format the leading zeroes of some data values get lost. However, I need the leading zeroes to merge the file with a map.

Using .csvt does not make much sense here, as I have too many columns and will use different files with different numbers of columns and therefore I would have to adapt the .csvt file everytime.

I am also not able to use a field calculator to adapt the columns once loaded into QGIS as the field calculator is inactive for .csv or .txt files.

Is there another way to ensure that the leading zeroes do not get lost? The file is generated with R. I don't know if this is relevant

Answer 1

When importing CSV files, QGIS is able to find the type of column used and suggest the best format.

The problem is when the first values are "numbers as string" like in your case, QGIS changes them automatically to numbers. It happens also when you have empty fields at the beginning of the file, making QGIS confused and displaying the bad field type.

In this case, you have to manually edit the field type in QGIS to get the right format, which can be very long to do if you have a big number of columns.

A little trick to force QGIS to set things the way you want : insert in the first line of your csv file one record with something he can't mistake (dummy values).

For example, the first line below is a looking like number like yours + an empty field that should be float with 2 decimals :

ID,Temperature, city
0001, NULL, New York 

QGIS might convert the first field as an integer (removing the zeros), then the second might be a string, erasing all your floating values starting at the 150th row)

Insert a line just after the field names and put in values he can't mistake (A for a string field, 0.01 for a float with 2 decimals, etc) :

ID,Temperature, city
A,0.01,A
0001, NULL, New York

QGIS will read the first line and suggest or display directly the right field type.

The downside is that you have one line with dummy values.

Answer 2

Use ogr2ogr http://www.gdal.org/ogr2ogr.html for making the conversion. The CSV driver http://www.gdal.org/drv_csv.html has options for automatic detection of the field types.

AUTODETECT_TYPE=YES/NO (defaults to NO). Setting it to YES will enable auto-detection of field data types. If while reading the records (beyond the records used for autodetection), a value is found to not correspond to the autodetected data type, a warning will be emitted and the field will be emptied.

QUOTED_FIELDS_AS_STRING=YES/NO (default NO). Only used if AUTODETECT_TYPE=YES. Whether to enforce quoted fields as string fields when set to YES. Otherwise, by default, the content of quoted fields will be tested for real, integer, etc... data types.

Insert your string attribute between double quotes

"001234",123,456,789

Make a test with ogrinfo

ogrinfo my_csv.csv -al -oo AUTODETECT_TYPE=YES -oo QUOTED_FIELDS_AS_STRINGS=YES
INFO: Open of `my_csv.csv'
      using driver `CSV' successful.
Layer name: my_csv
Geometry: None
Feature Count: 1
Layer SRS WKT:
(unknown)
field_1: String (0.0)
field_2: Integer (0.0)
field_3: Integer (0.0)
field_4: Integer (0.0)
OGRFeature(csvtext):1
  field_1 (String) = 001234
  field_2 (Integer) = 123
  field_3 (Integer) = 456
  field_4 (Integer) = 789

Finally convert you data into some other format that QGIS can open

ogr2ogr -f "ESRI Shapefile" output.shp my_csv.csv -oo AUTODETECT_TYPE=YES -oo QUOTED_FIELDS_AS_STRINGS=YES

It is possible to let QGIS to open data with GDAL though Layer-Add Layer-Add Vector Layer, but unfortunately there is no place in the user interface for giving the open options. Therefore you must use command line and some interim file format.

Answer 3

The QGIS CSV import sets the data format of the individual columns independently.

The first column is a string. The rest are integers.

If you want to define the first column as a string and the remaining columns automatically, create a *.csvt file (same file name as the CSV) with the following content.

"string"