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

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

About pvanb

I am a tropical forest ecologist with a focus on spatial and temporal patterns and processes at population and ecosystem level. I am furthermore very interested in issues related to conservation and sustainable use of biodiversity and natural resources under current and future climates. I have worked in the Middle East (Syria and Lebanon) and South America (Brazil) and in Eastern Africa (Kenya).
This entry was posted in Data handling, GRASS GIS, SQLite and tagged , , , . Bookmark the permalink.

3 Responses to Finding and removing carriage returns in your SQLite table

  1. Pingback: Finding and removing carriage returns in your SQLite | GIS Tutorial

  2. Martin Schneeweis says:

    I found a solution at
    http://sqlite.1065341.n5.nabble.com/replacing-all-newlines-in-a-field-td33024.html
    and
    http://stackoverflow.com/questions/4642535/how-to-remove-carriage-returns-in-a-text-field-in-sqlite

    Linux-New-Line (LF):
    > select replace(field_containing_new_line, x’0A’, ‘ // ‘) from table_containing_field

    Windows-New-Line (CR-LF) should be:
    > select replace(field_containing_new_line, x’0D0A’, ‘ // ‘) from table_containing_field

    Where ” // ” is the replacement-string for the new line.

  3. Robert says:

    Thanks a bunch. I actually copied data between MySQL and Sqlite. The export contained ‘\r\n’ and I needed to translate that into the Sqlite equivalent.
    Update table set column=REPLACE(column, ‘\r\n’, ‘
    ‘);

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s