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
– Lasse V. Karlsen, Commented Apr 6, 2016 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
– t3chb0t, Commented Apr 6, 2016 at 12:40
The problem is that it reads garbage like the file wasn't encoded with utf8. — t3chb0t
– t3chb0t, Commented Apr 6, 2016 at 12:42
I was able to import the file when I change the console's code page as suggested in this comment: stackoverflow.com/questions/20269224/… I tried to change it for powershell too but it didn't work there :( — t3chb0t
– t3chb0t, Commented Apr 6, 2016 at 12:56
This solution finally worked for me stackoverflow.com/a/21759264/235671 this was quite tricky — t3chb0t
– t3chb0t, Commented Apr 6, 2016 at 13:00

Community · Accepted Answer · 2017-05-23 12:16:02Z

1

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));

edited May 23, 2017 at 12:16

CommunityBot

11 silver badge

answered Apr 7, 2016 at 6:29

t3chb0t

19.3k15 gold badges94 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to execute SQLite script with UTF8?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related