Changing CSV layer attribute value from text to number in QGIS?

Question

I read in a CSV file for linking to a .shp file.

All the variables that are just numbers came in as text.

How do I change the text variables to numeric in QGIS?

Answer 1

I once had the same question. The answer is that you put a file in the same folder with the ending csvt. For example your file name is xyzdata.csv you add the file xyzdata.csvt

This one you can edit with editor for example. And in it you set the data type like this. "Integer","String","Integer","Integer","String","Real"

Integer is now the variable for the first column, string for the second and so on... Make sure the files are named identically.

Edit: See here too if needed: http://underdark.wordpress.com/2011/03/07/how-to-specify-data-types-of-csv-columns-for-use-in-qgis/

• HealthMaps: Thanks a lot1 so is it true that all CSV files will be read in as text? Maybe I imagined this but when I was getting started with QGIS a little while ago I linked a CSV file and numbers were numbers. Seems that this info should be in the users manual. (maybe I missed it)
• Shepherdjo: Yea, that was pretty weird for me too. But if you have only a few columns, this method is easy to perform. So...did it actually work out for you?
• HealthMaps: Yes, it works perfectly! Watch out setting up your CSVT file in Excel; extra quotation marks get stuck in etc. I use SAS or R to make the files I want to link so it is not too much trouble to make a CSVT file along with the CSV file. Seems this needs to be fixed. Another GIS I use see numbers in a CSV column, it reads them in as numbers.
• HealthMaps: I found a plugin MMQGIS that will change a column from text to float or float to text. Works only for a shape file, not a linked file or an imported csv file. See michaelminn.com/linux/mmqgis
• Shepherdjo: Good to hear that. And thx for the plug-in hint. I would be grateful too, if you accept my answer then, will you?

Answer 2

Rather than messing with the input file, trying to get everything picture perfect, just use the field calculator to convert the text to a number / real. See the examples below:

Field Calculator

Field Calculator to Number

Field Calculator to Real

Attribute Table After Function

• HealthMaps: When I brought the CSV file in I could not edit it. The edit button was greyed out. How can I get it to be editable?
• nagytech: I'm not sure you can edit a csv through qGIS. I think I misread your question in that you had already imported the csv data across to the shapefile.
• HealthMaps: I did the link, .shp to a csv file which had number columns but were really text. Apparently one cannot do any editing either on a CSV file on its own or if it is linked to a SHP file. SO I saved the linked file and then reloaded it and was able to edit the text number files to an integer format as you suggested. Seems using the .csvt file works best if you have a lot of text columns that need to be converted and then use the field calculator method if there are not so many. It would be useful to be able to edit the CSV file on its own, not linked to anything.

Answer 3

Instead of loading the .csv using Add vector layer, use the Delimited Text layer tool. It will try to detect the most fitting data type for each column.

For more details see the QGIS user guide.

enter image description here

• dez93_2000: @underdark just in case I missed it, there's no way to tell QGis which cell values are NA, right? When exporting from R, these are "NA", but it doesn't look like you can flag that for QGis, which means fields with any NA values are imported as text. The easiest way to deal with this seems to be to find+replace NA with blank in excel/libreoffice beforehand, but it would be nice not to have to do this...

Answer 4

In QGIS 3.x a common error source is to choose the wrong decimal separator. Check or Uncheck the box "Decimal separator is comma" before loading your CSV into QGIS:

enter image description here

Answer 5

MMQGis caused this problem, and, as it turns out, it can fix it to.

Once you've joined your Shapefile & CSV Data, use MMQGis's Modify> Text to Float tool.

As usual, it leaves a trail of junk intermediate shapefiles but what else is new

Answer 6

I stumbled upon this and it took me a while to find out that there's a much better, more up-to-date answer to this problem: nowadays, you can use the Table Refactor Field Tool from QGIS standard toolbox, it works much better and it's easier.

Basically you go in the toolbox, find the Refactor Fields tool, and the popup menu allows you to change the datatype of each field

Table Refactor Fields Tool

See this link for a step-by-step explanation

Answer 7

Another alternative in case of large datasets to be managed and obviously time consuming to to proceed with the field calculator (without looping) or within a text editor : - save your data as CSV + XY + variable for the joint with our polugone shapefile - upload your CSV with the plug in (import csv) then save as shapefile (point) - then close q gis - back to the folder with the shapefile point, duplicate the corresponding dbf table / rename - open Qgis - upload the new dbf and join to shapefile (polygone features)

One stone, two pigeons : you have potentally 1) a shapefile point with all the data and all the possibility of represention 2) and independant shapefile with dbf table joined (editable !) linked to polygone for thematic cartography

This is a bit rude , but quite straightforward ...

bs_epidemio

• Get Spatial: While this answer discusses how to get the .csv file into an editable format, it does not address the original question of how to convert an attribute value from a text to a number. While it is important to have quality, focused questions, it is equally critical that the answers to those questions be as focused and relevant as possible to what was asked originally.