csv - Check the encoding of text in SQlite

Question

I'm having a nightmare dealing with non Eurpean texts in SQlite. I think the problem is that SQlite isn't encoding the text in UTF8. So I want to check what the encoding is, and hopefully change it to utf8. I encoded a CSV in UTF8 and simply imported it to SQlite but the non-roman text is garbled.

I would like to know: 1)how to check the encoding. 2)How to change the encoding if it is not utf8. I've been reading about Pragma encoding, but I'm not sure how to use this.

I used OpenOffice 3 to create a spreadsheet with half ENglish and half Japanese text. Next I saved the file as a CSV using utf8. This part seems to be ok. I also tried to do it using Google Docs and it worked fine. Next I opened SQlite Browser and did CSV import. The ENglish text shows up perfectly, but the Japanese text is garbled symbols. I think sqlite is using a dfferent encoding (perhaps utf16?).

Answer 1

You can test the encoding with this pragma:

PRAGMA encoding; 

You cannot change the encoding for an existing database. To create a new database with a specific encoding, open a SQLite connection to a blank file, run this pragma:

PRAGMA encoding = "UTF-8"; 

And then create your database.

If you have a database and need a different encoding, then you need to create a new database with the new encoding, and then recreate the schema and import all the data.

However, if you have a problem with garbled text it's pretty much always a problem with one of the tools being used, not SQLite itself. Even if SQLite is using a different encoding depending, the only end result is that it will cause some extra computation as SQLite converts from stored encoding to API-requested encoding constantly. If you're using anything other than the C-level API's, then you should never care about encoding--the API's used by the tool you're using will dictate what encoding should be used.

Many SQLite tools have shown issues mangling text into our out of SQLite, including command line shells. Try running SQLite from a command line and tell it to import the file itself instead of going through SQLite Browser.