How to execute SQLite script with UTF8?

Question

I'm trying to .read a SQLite script that contains some German characters (umlaute äöü and ß):

sqlite>
.open test.db
.read test.sql

I've saved the sql file as utf8 without BOM because sqlite3 wouldn't read it with BOM and complains about an error in line 1 bug still, sqlite3 doesn't seem to read it as utf8 because when I select a row with a ü all I get are question marks ?. If I however add a ü in the console and select the same row the correct character is showed.

I also tried it with powershell:

Get-Content test.sql -encoding utf8 | .\sqlite3.exe test.db

but the result was the same (question marks).

What do I have to do to execute a ut8 sql script?

(I deleted the database for each test)

If the file is without BOM and Sqlite won't read a file *with* BOM, what is the problem here? Can you clarify what that part of the question means? — Lasse V. Karlsen, Apr 06 '16 at 12:37
@LasseV.Karlsen I've rewritten the sentence: _I've saved the sql file as utf8 without BOM because sqlite3 wouldn't read it with BOM and complains about an error in line 1._ — t3chb0t, Apr 06 '16 at 12:40
The problem is that it reads garbage like the file wasn't encoded with utf8. — t3chb0t, Apr 06 '16 at 12:42
I was able to import the file when I change the console's code page as suggested in this comment: http://stackoverflow.com/questions/20269224/how-to-print-sqlite-to-file-in-utf-8#comment30867086_20274260 I tried to change it for powershell too but it didn't work there :( — t3chb0t, Apr 06 '16 at 12:56
This solution finally worked for me http://stackoverflow.com/a/21759264/235671 this was quite tricky — t3chb0t, Apr 06 '16 at 13:00
Please note that with most versions of `sqlite3.exe`, UTF-8 I/O in the console does not work correctly. To check whether the DB contains correct UTF-8 data, better open it in any GUI tool. — CL., Apr 07 '16 at 06:36

score 1 · Accepted Answer · edited May 23 '17 at 12:16

As it turns out, to import via powershell a utf8 encoded file that contains national characters like the German äöüß is with this command Running a Sqlite3 Script from Command Line:

.\sqlite3 test.db ".read test.sql"

the other popular solution Redirecting a .sql file to .db file in Windows does not work in this case and the result is question marks:

Get-Content test.sql -encoding utf8 | .\sqlite3.exe test.db

To view the results it's also important to change the encoding for the console with

chcp 65001

or otherwise you will see only garbage although the file has been correctly imported.

If you are working with C# you still won't see the correct string and you need to kind a re-encode it with:

value = Encoding.UTF8.GetString(Encoding.Default.GetBytes(value));

How to execute SQLite script with UTF8?

1 Answers1