2

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

  • 1
    You must import the columns as "Text". Create a .csvt file to define that. – Mike Aug 18 '17 at 7:39
  • Thank you for your answer. But, as I said, the file has a lot of columns which makes it kind of tedious to work with .csvt. Furthermore, I have to work with several files with different columns and it takes also a lot of time to adapt the .csvt file to every table. Therefore, I am looking for another possibility – Hannah H. Aug 18 '17 at 7:58
  • Have you tried to put text fields between double quotes in CSV ,"00012344",? – user30184 Aug 18 '17 at 8:18
  • I have to add the leading zeros with custom anyways. But if I try to add the leading zeros here with "00..." I get only zeros – Hannah H. Aug 18 '17 at 8:31
  • What program generated the csv? Can you generate the csvt with a little program? – Mike Aug 18 '17 at 8:51
2

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.

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 quotas

"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.

1

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"

enter image description here

  • This works, but in QGIS, one mustn't choose to add the CSV via "Delimiter Text Layer" but directly via "Vector layer". Then you choose your CSV file (where you defined a accompagnying csvt file). To automatically generate a csvt file from your csv, you can use this nice web application : dogeo.fr/_apps/CSV4Bruch (didn't try it on a big file) – gisnside Aug 18 '17 at 14:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.