1

I am importing from a tab separated text file to sqlite3. In most cases, values import as TEXT, however occasionally a value will import as a BLOB. This usually happens when there is a slightly unusual symbol in the text, such as the | (pipe) symbol.

Why is this happening, and what can I do about it?

I am setting the sqlite .mode to tabs, and there is no additional information on the encoding of the text file.

Edit: Here's the code:

.open filename.sq3
.mode tabs
.import filename.tab tablename
DatsunBing
  • 8,684
  • 17
  • 87
  • 172
  • What is the encoding used in your TSV file? How are you importing the TSV file into Sqlite? Please show us your actual code. – Dai Aug 06 '20 at 02:17
  • Dai, per the OP, there is no additional information on the encoding. Also, see code example – DatsunBing Aug 06 '20 at 02:22
  • 1
    The file *should* be encoded in UTF-8 to use with `.import`. The code uses `sqlite3_bind_text()` to bind values to parameters in an insert statement. – Shawn Aug 06 '20 at 02:29
  • Does the table already exist before the import? If so, what is its definition? – Shawn Aug 06 '20 at 02:30
  • 1
    @KimPrince "there is no additional information on the encoding" - that's not true. You **can** tell us what character encoding is being used by the TSV files (Is it strict 7-bit ASCII? UTF-8? UTF-16? ISO 8859-1?) Are you sure it's an actual TSV file using `0x09` tab characters? Are string values enquoted? Do the columns of the TSV file match the destination table? – Dai Aug 06 '20 at 02:55

1 Answers1

0

I did run into the same problem. I can not answer Why is this TEXT imported as a BLOB but how i was able to fix it. In my case rows that did contain special characters like ä,ü,ö were imported as blob.

From the sqlite command prompt the commands are read from import_from_file.txt

sqlite> .read 'C:\data\rawtext\import_from_file.txt'

The file import_from_file.txt looks like this

CREATE TABLE "Extract" ("extract"   TEXT);
.mode ascii
.separator "@" "\n"
.import C:\\data\\rawtext\\rawdata.txt

According to the file rawdata.txt is encoded in ANSI. With the file was converted to utf8. After the file was converted to an UTF8 encoding the sqlite import worked without any blob rows. Every special character like ä,ü,ö was readable inside sqlite

surfmuggle
  • 5,527
  • 7
  • 48
  • 77