2

i have read most of the related postings and answers allready ... (e.g. http://anitagraser.com/2011/03/07/how-to-specify-data-types-of-csv-columns-for-use-in-qgis/ and Changing CSV layer attribute value from text to number in QGIS? ... among others)

but (!) unfortunately none of them solved my problem. as someone postet on 2012-01-04 12:27 definition of properly defined *.csvt doesn't effect field-import - they are expected to be numbers and still are string.

her is a sample of my csv
InputID,Mean,Stdev,min,max
1,334.7617168,36.87450474,294.4334857,383.5583136
2,446.3946568,207.949905,153.2494524,613.3182137

and the csvt
"Integer","Real","Real","Real","Real"

.. i have already:

  • checked field names (no blanks), checked separators, ... which are qgis' default because the csv is distance matrix calculation result

  • defined the csvt with simle "Integer","Real" and the precision-type-style "Integer(6)","Real(8.3)"

  • changed country settings in win7, win8, osx 10.9 due to known decimal separator issues

  • used csv as specified as well as "excel-style" csv
    (";"-separated)
  • checked all that in qgis 2.0, 2.2, 2.3(master)

and: at the beginning this problem was related to distance matrix output
... where qgis calculates the results (and writes the csv-outputs), which of course are numbers (mean, stdev, min, max)
so, initially i was just looking for a straight forward way to use qgis' own calculation results for mapping of distance-distribution ...

is there a way to solve or completely bypass the csv-t issue in a way, i can easily communicate to gis-beginners ?

Community
  • 1
robert tuw
  • 898
  • 1
  • 9
  • 20
  • I am not clear what the problem is that you are encountering. I don't use csv-t at all and successfully import csv. If I have troublesome files I view and edit them in Notepad++. – Willy Mar 11 '14 at 09:41

6 Answers6

4

By using "Add comma delimited file" instead of Vector layer button QGIS will read it with number columns as columns. A dialog window will open where you can change the delimiting character to colon, semicolon, tab or others. If the csv is intended to be used as a table (not an xy or event layer) you have to ask for No geometry. Here you have an example (sorry for the weird language).

enter image description here

I did it so (qgis 2.6), and everything worked smoothly. Hope this helps. Asier

Asier
  • 458
  • 4
  • 10
  • the problem is still existing in 2.18 ltr as well as in 3.4 ltr. and the answer is still valid (@Asier). use Open "Data Source Manager / Delimited Text" and specify the settings according to your file characteristics ! but (!) pay attention to a clean csv-file. i just had a 2 hour session because 1. one single row of my number-fields contained an empty (numeric)value (i.e. NoData) 2. the csv-export routine added 2 empty fields to my csv at the end of the table. ... resulting in a csv with string values instead of numbers within numeric fields – robert tuw Nov 08 '18 at 16:46
2

First save your excel file/data as .CSV file. Open QGIS and then load your Vector layer map that you wanted to join your .csv file. Then load your .csv file as Text Delimited Layer. Then Check box of the File Format as (CSV) Comma Separated values then Check box as No Geometry (attribute data only), Click OK This will add your CSV file. Double click on the CSV file, click Field and you can see the data as Integers, not as String. That is what you wanted.

Now you can join the CSV file into vector layer data by specifying your input layer, join layer and target layer. When click OK, your data is joined and you can again check the Field and you will see your intended columns as Integers. I have done this QGIS version 2.6

Tenzin
  • 21
  • 2
1

Sorin's answer (importing as Delimited Text File, with no X/Y coordinates) works fine in QGIS 2.8 (maybe later too) but not in QGIS 2.16. The numeric values are still wrongly treated as text. Alternative solutions I have seen (creating a CSVT file or using the Defactor tool) are VERY complicated and potentially time-consuming (for example, I have a public health table with a hundred columns - at varying intervals - am I really going to sit down and write a CSVT header with every one correctly labelled as 'text', 'int' or whatever?).

Importing tabular data is a basic operation and it needs to be as simple as possible to maintain QGIS's usability and attractiveness to the non-developer user who simply wants to spend as much time as possible with the actual data, not with workarounds needed to get at that data.

So can I please appeal to the QGIS development community to make simplifying this operation a priority?

I don't know how to 'raise a ticket', so, if that is necessary, I would appreciate some help.

Knightshound
  • 4,430
  • 18
  • 39
Roy Marsh
  • 11
  • 1
  • right - it still doesn't work correctly in 2.18 and in 3.4 (!) ... if you have excel on your machine you could open the csv with excel and save as xls or xlsx. if there are'nt any type-mismatches (like txt, NoData or blanks within numeric fields, empty fields or rows) you will get a xls(x) that will work as input in 2.18 and 3.4. and if you really really need a csv + csvt you can export that table with qgis export and create a valid csvt by setting the corresponding export option ;) – robert tuw Nov 09 '18 at 07:43
1

Indeed, converting a string field to real does not seem to work anymore. Is that a bug?

But you can create another field based on an existing field. Set type and precision (if real) and specify the format with the adequate function (here toreal()). See the screenshot below :

My string value is in test field and I convert it to real into myreal field.

to real - QGIS

simo
  • 8,580
  • 1
  • 28
  • 56
  • well - of course i've found some "working-workarounds" ... and finally managed to do an import, that is working (thx for the inputs to all ! ! ! - my fault had a leading return/break in the csvt-file :( ).
    but still - that isnt exactly my initial question. qgis produced a csv-output via "Distance Matrix" and i would be really happy if it could write a csvt too ... otherwise i have to teach my students how to deal with the above mentioned workarounds. which is ok - but not the easiest way, indeed.     – robert tuw Mar 11 '14 at 12:10
  • @roberttuw, perhaps you could edit your original question with this information, and/or select a best answer as accepted? – Simbamangu Feb 15 '15 at 10:17
1

If you drag and drop the csv file into the Layers panel, everything is working as expected! (Tested with QGIS 2.2.0, 2.0.1, 1.8.0 and 1.7.4)

enter image description here

Sorin Călinică
  • 5,118
  • 1
  • 17
  • 23
0

The correct format for the csv file is to have ; as the delimiter, with , as the delimiter of the csvt file. This seems to be what is wrong with your example.

BillW
  • 510
  • 2
  • 9