QGIS3 - how to define/change field type for non-spatial data table?

Question

I am playing with joining data in QGIS. I previously did it only with two spatial layers and now I am trying to connect one spatial layer (polygons) and "non-spatial" data in a CSV table using a text field. I have a polygon layer of administrative units and CSV file with this:

join,Region,Island,Total_sheep2012,Total_sheep2017,sheep_perc_change,Total_dairy_cattle2012,Total_dairy_cattle2017,dairy_cattle_perc_change,Total_beef_cattle2012,Total_beef_cattle2017,dairy_cattle_perc_change,Total_deer2012,Total_deer2017,deer_perc_change
Northland Region,Northland,North Island,441000,328000,-25.6085088047533,398000,379000,-4.61655655112077,381000,383000,0.587835122491917,5000,5000,-4.74990306320279
Auckland Region,Auckland,North Island,205000,253000,23.2883519267306,117000,132000,12.825606875794,117000,112000,-4.69103849886768,13000,11000,-10.7772594291136
Waikato Region,Waikato,North Island,1777000,1479000,-16.752920276672,1832000,1872000,2.14005828485358,506000,488000,-3.5258139953229,81000,63000,-22.5934657302608
Bay of Plenty Region,Bay of Plenty,North Island,323000,282000,-12.8056070019388,312000,325000,4.11397065886286,93000,105000,12.2047623638292,42000,33000,-22.5614384666477
Gisborne Region,Gisborne,North Island,1547000,1412000,-8.74100203322704,17000,9000,-44.9722140976894,268000,247000,-7.60877282800011,17000,12000,-31.541880736593
Hawkes Bay Region,Hawke's Bay,North Island,3262000,2794000,-14.3520488170305,93000,88000,-5.77342633292852,471000,421000,-10.5830024840237,70000,52000,-26.1000042871229
Taranaki Region,Taranaki,North Island,434000,498000,14.5264064161767,604000,591000,-2.23980489193111,104000,118000,13.914588685222,4000,4000,6.46964856230032
Manawatu-Wanganui Region,Manawatu-Wanganui,North Island,5613000,5062000,-9.81814061324383,475000,463000,-2.60986064198071,580000,568000,-2.0203083346993,74000,55000,-25.7560817473289
Wellington Region,Wellington,North Island,1665000,1512000,-9.17855332357895,108000,97000,-10.5108436408009,140000,135000,-3.91397236860846,14000,11000,-23.6560683269935
Tasman Region,Tasman,South Island,277000,262000,-5.41720691859562,72000,66000,-8.11885040858302,40000,40000,-0.856808388751899,16000,10000,-33.4105534105534
Nelson Region,Nelson,South Island,6000,,,,3000,,,,,,0,
Marlborough Region,Marlborough,South Island,547000,469000,-14.3431777477247,33000,25000,-23.7913179601421,60000,52000,-12.7713007467527,,6000,
West Coast Region,West Coast,South Island,58000,40000,-30.4743048979943,174000,156000,-10.0471635636996,29000,27000,-5.4479001448176,34000,28000,-17.0763108614232
Canterbury Region,Canterbury,South Island,5348000,4474000,-16.3442850705216,1200000,1308000,8.97822448352194,471000,468000,-0.678922391268327,292000,239000,-18.2155917239867
Otago Region,Otago,South Island,5343000,4587000,-14.1509787105973,336000,334000,-0.72202166064982,290000,263000,-9.49558881259513,153000,116000,-24.4700569101446
Southland Region,Southland,South Island,4356000,3987000,-8.47329703906435,671000,681000,1.55536765879141,172000,175000,1.77229160615742,238000,192000,-19.262422295115
Area Outside Region,Chatham Islands,South Island,59000,69000,16.0676390339206,,,,,11000,,,,

I was able to simply drag and drop it in QGIS (latest 3.8. Zanzibar) and perform the join operation but finally found out that the joined fields and also the fields of the added table were recognized as strings.

How can I change the field type or make QGIS to recognize the numbers as numbers?

My country uses decimal comma but this never caused any problems with CSV import (Add delimited text layer).

Answer 1

This excellent tutorial by Anita Graser explains how QGIS handles CSV files:

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.

When you drag and drop a CSV into QGIS, QGIS handles it as though you added it through the "add vector layer" method, which interprets all columns as strings.

Instead, add the CSV through the "add delimited text layer" method (in the Manage Layers toolbar, click the "comma" button.)

Choose the option to "detect field types." Hopefully QGIS will automatically choose the correct field types. If not, you'll have to create a .csvt file that defines each field type.

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"

(Source: the same tutorial. See the tutorial for full instructions.)