You seem to assert that the sqlite3.exe shell will display unicode characters as BLOB when somehow imported. If that actually happens as your posts vaguely suggests, it would be a bug. Can you please provide verbatim input to the sqlite3.exe shell which reproduces this behavior? That will make clear what you're talking about here, and should allow either: a diagnosis of what you are doing that can be done better in another way; or the misbehavior to be fixed.

Is there anything that I can add that will make sqlite3 give the same result as DB Browser for SQLite?

I cannot say until "the same result" is more specifically described. Further, I would not go off experimenting with "DB Browser for SQLite" on such a vague quest.

Hi Greg,

I (at least) understand so much better with your rewording of your question.

Your question might better have been posed to a DB Browser forum, because it seems that it is DB Browser that has code in its import function that treats such characters differently on import than when it encounters them in an existing database.

Sqlite is performing exactly as designed -- it's importing exactly what you provided it. It would upset a lot of folks if it behaved any other way.

If your issue hadn't become moot, you could have modified your script to perform changes you might like. For example, just after the ".import" (and assuming your text field was named "title") you could have:

   UPDATE Markers SET title  = replace(title, x'92', '''' );

If you had gobs of text fields, or if they might vary, you could even use readfile() and writefile() to read the entire csv file into a blob field of a temporary table, perform the UPDATE command, and write it back out before your .import command.

0x92 is not UTF-8 for any kind of quote. It appears to be some kind of PostScript encoding.

I did not delve into the details, but this article on ASCII and Unicode quotation marks may give more insight.

-- 
Regards,
Kees Nuyt

0x92 as a quote character is Windows-1252.

Is it the separator line that needs to be fixed?

Here is what the CLI says when asked for help on .import: sqlite> .help import .import FILE TABLE Import data from FILE into TABLE Options: --ascii Use 037 and 036 as column and row separators --csv Use , and n as column and row separators --skip N Skip the first N rows of input --schema S Target table to be S.TABLE -v "Verbose" - increase auxiliary output Notes: * If TABLE does not exist, it is created. The first row of input determines the column names. * If neither --csv or --ascii are used, the input mode is derived from the ".mode" output mode * If FILE begins with "|" then it is a command that generates the input text.

So, I would say it's the (not shown) import line that is fix-worthy. Note in particular the command's --csv option.

As Larry said, it's hard to say without seeing the .import line that you used. What I find really odd is that you specify the column separator as a semicolon, while your data is comma separated. What do you expect to happen? Another oddity: The ascii mode does not treat quote characters specially, yet in your example, something happens to quotes.

Perhaps you could post a complete transcript showing what you did, and the result? Like the below, where I tried (and failed, as I fully expected) to replicate your result.

⬥ cat foo
"data1","data2","data3"
⬥ sqlite3
SQLite version 3.41.0 2023-01-26 11:12:47
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> create table foo(x,y,z);
sqlite> .mode ascii
sqlite> .separator ";" "\n"
sqlite> .import foo foo
foo:1: expected 3 columns but found 1 - filling the rest with NULL
sqlite> .mode qbox
sqlite> select * from foo;
┌───────────────────────────┬──────┬──────┐
│             x             │  y   │  z   │
├───────────────────────────┼──────┼──────┤
│ '"data1","data2","data3"' │ NULL │ NULL │
└───────────────────────────┴──────┴──────┘

(Note that the qbox output mode added the extra single quotes, to more clearly see the exact value being shown.)

Whoever wrote that stackoverflow post was dead wrong (or possibly partially right in whatever special circumstance the post was about). It's dangerous to rely on stackoverflow answers without proper understanding.

The shell has a nice help feature:

sqlite> .help separator
.separator COL ?ROW?     Change the column and row separators

The file you tried on is a classic csv file. The easy way to import it, is like this:

sqlite> .import --csv FFIListExample.csv example
sqlite> .mode qbox
sqlite> select * from example limit 4;
┌───────────────────────┬──────────────────────┬──────────────────┐
│         GIIN          │         FINm         │    CountryNm     │
├───────────────────────┼──────────────────────┼──────────────────┤
│ '98Q96B.00000.LE.250' │ 'Test Bank One, LLP' │ 'France'         │
│ '98Q96B.00000.BR.826' │ 'Branch'             │ 'United Kingdom' │
│ '98Q96B.00000.BR.036' │ 'Branch'             │ 'Australia'      │
│ '98Q96B.00000.BR.076' │ 'Branch'             │ 'Brazil'         │
└───────────────────────┴──────────────────────┴──────────────────┘

That is when you import into a new table. To import into an existing table with three columns, use .import --csv --skip 1 … instead. .help import is your friend.

Is there a recommended way to do this import using the Perl DBI?

This is not the Perl forum. Questions regarding Perl should be directed there.