Sqlite - how to escape double quotes?

Question

I am having trouble importing into sql lite. I am exporting a table from Sql Server into a flat file encoded in UTF-8. And then trying to import the flat file into sqlite db. DB is UTF-8 encoded.

These lines are troublesome (tab delimited, line ends with CRLF):

ID  w   posid   def
1234    bracket 40  "(" and ")" spec...

1234    bracket 40  Any of the characters "(", ")", "[", "]", "{", "}", and, in the area of computer languages, "<" and ">".

Error:

unescaped " character

I have tried replacing the quotes " with double quotes "", still doesn't work.

Import settings: tab separator

.separator " "

.import data.txt words

sqlite Table Schema:

CREATE TABLE words (ID integer NOT NULL, w TEXT  NOT NULL, posid integer NOT NULL, def TEXT NOT NULL);

Update: Somehow, adding a hash at the beginning of the def field in Sql Server worked:

update words set def = '#' + def

Not sure why that is. This worked, but it added an unwanted character in the field.

Answer 1

It turned out import can mess up when there are new line characters, or quotes, or commas.

One solution would be to replace these characters with some other character sequences, or character codes (e.g. char(1), char(2)...) , and make sure fields don't contain these sequences or codes, before you run the import. For example, replace quotes with --, then import, then replace -- with quotes again. I have another table with some text fields that have new line characters, and this solution seems to work.

before import:

update [table] set comment = REPLACE(comment, CHAR(13), '-*-')
update [table] set comment = REPLACE(comment, CHAR(10), '%-$-%')
update [table] set comment = REPLACE(comment, '"', '%-&-%')

after import:

update [table] set comment = REPLACE(comment, '-*-', CHAR(13))
update [table] set comment = REPLACE(comment, '%-$-%', CHAR(10))
update [table] set comment = REPLACE(comment, '%-&-%', '"')

Answer 2

To do that without changing the input data, use ascii mode and set the column separator to tab and the row separator to CRLF.

.mode ascii
.separator "\t" "\r\n"

See my answer to this other question for an explanation of why.