Finding and removing carriage returns in your SQLite table

The problem with copy data from e.g., an excel or calc sheet into an SQLite database (or any database I reckon) is that the data you copy might include an carriage return. I found this out the hard way after having copied data from a spreadsheet into a attribute table of a GRASS GIS vector layer (which is stored in a SQLite database). When later I opened the table with the Attribute table manager in GRASS GIS, I got a lot of error messages like the one in the screenshot below:

Screenshot error message from the GRASS GIS Attribute table manager

Going to the mentioned records (253 in this case) I found out that they all contained carriage returns in the just updated fields. A bit of googling learned that it is easy enough to select all records with carriage returns; you just include a carriage return in your code, e.g.:

SELECT *
FROM table
WHERE column LIKE '%
%'

Knowing this, it is easy to clean up your table by replacing the values. The probably easiest way is with the UPDATE function:

UPDATE table
SET column=REPLACE(column,'
', '')

I didn’t try to do this on the command line, but you probably need to use the hexadecimal representations of the carriage return, something in the line of:

REPLACE(field1, 0x0A,'')

I tried to use the hex notation instead of an actual carriage return in my SQLite manager, but it didn’t work. So if you know how to use the hex code, let me know.

About these ads