3

This question already has an answer here:

I have long integers (11 characters) stored as text in a shapefile that I need to convert to integers, but when I attempt to convert them in the QGIS field calculator, I get the following error:

Eval Error:
Cannot convert '26001980100' to int

The value in the error code above is the value from the first row in the field. I though this might be due to a field width constraint in shapefiles for integer columns, so I then imported the shapefile into a spatialite database and tried again without success. In both cases I was trying to create a new field populated by the converted values. How can I convert this field to integer?

The problem appears to goes deeper: after creating a new field and populating it with the integer values via the spatialite command line tool, QGIS does not then "see" the new field in the updated table. Am I out of luck with long integers in QGIS?

QGIS 2.12.3

Edit: I'm not sure whether this is a duplicate because the error appears specific to cases of converting strings having a length greater than 10 to integer. I tried the answer supplied here to convert to integer without luck. However, I was able to use that answer to convert the field to REAL, which is adequate for my purposes. The field in question is a foreign key, and the tables containing the primary key all store the values as INT, so I wanted to be consistent, but I can still join INT to REAL.

marked as duplicate by Simbamangu, Andre Silva, John Powell, Fezter, Devdatta Tengshe Feb 15 '16 at 11:23

This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.

2

In spatialite you should be able to do a cast. Assuming a table "numbers" with one TEXT column, "number_str" (that represents integers) and a second column "number_ints" that was defined as INTEGER, then

UPDATE numbers SET number_ints=(CAST(number_str AS INTEGER));

More details on the sqlite concept of "affinity" and the CAST function are here

Regarding your "disappearing" column, this may be the same bug reported in the QGIS issue tracker. Something weird with creating columns in a spatialite DB, not through the QGIS interface, but it will get solved eventually. Here's the post on the spatialite-user's maillist explaining the workaround. It involves running

SELECT InvalidateLayerStatistics();
SELECT UpdateLayerStatistics(); 
  • thanks, but I tried to say that I had already been able to cast the string field as integer in a new field via spatialite. The problem then was that I couldn't see it in QGIS in the attribute table. Casting as REAL took care of the issue, but bothers me a bit because it is inconsistent with my other data, i.e., the field is a foreign key and the primary keys I want to join to are stored as INT. – eric s Feb 14 '16 at 17:43
  • The workaround on the spatialite-users maillist involved just two SQL commands: SELECT InvalidateLayerStatistics(); (*) SELECT UpdateLayerStatistics(); – Micha Feb 15 '16 at 10:40

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