SQLite Forum

sqlite3.exe export with bom

sqlite3.exe export with bom

(1) By Horst (neubauer) on 2020-04-17 12:13:16 [link] [source]

I suggest a new commandline option for sqlite3.exe to prefix an exportfile with an utf bom. In doing so there is no more guessing for consuming processes which encoding might the file have.

For example: Creating an .csv export on MS Windows and doubleclick on the file opens an installed Excel app. But it guesses to load an ansi-encoded file if there is no leading bom. I can add this prefix in a script - but it would be neat to get it just out of the box.

Thx for considering


(2) By Warren Young (wyoung) on 2020-04-17 18:31:07 in reply to 1 [link] [source]

I can see the point of this request, but I don't see a reason to make it a command line option: it's only useful with UTF-16 output.

(And technically UTF-32, but where do you find that use for on-disk storage?)

Therefore, it only needs to happen in that condition.

Further, any program that deals in UTF-16 should be able to cope with a BOM, so why not make it do that always if the output format is UTF-16?

(3) By Richard Hipp (drh) on 2020-04-17 18:55:34 in reply to 2 [link] [source]

It turns out that Excel on Windows interprets CSV files as if they were encoded using some codepage unless the file begins with a UTF8-encoding BOM (0xef 0xbb 0xbf). So if your CSV file contains some text fields that have non-ASCII characters in them, Excel will mangle them. I verified this experimentally.

This seems to be an Excel-only thing. Other spreadsheet programs I tried always assume UTF8 for CSV files. On the other hand, every spreadsheet I have access to was able to silently ignore a UTF8 BOM at the beginning of the file.

(4.1) By Simon Slavin (slavin) on 2020-04-18 15:09:46 edited from 4.0 in reply to 1 [link] [source]


(5) By Richard Hipp (drh) on 2020-04-18 14:13:49 in reply to 1 [source]

On trunk is an enhancement to the CLI that adds the --bom option to the ".excel", ".once", and ".output" commands.

Please rebuild using the latest trunk sources and report back whether or not this new feature is working for you.

(6) By Horst (neubauer) on 2020-04-18 22:21:40 in reply to 5 [link] [source]

I don't have an enviroment to build the sqlite3.exe.

(7) By Horst (neubauer) on 2020-04-21 09:50:08 in reply to 6 [link] [source]

Found a colleague who's done it for me. Works as expected. I'm abolutely suprised how quick You did this. Great team. Great tool. Thank You very much! Horst

(8) By Horst (neubauer) on 2020-04-21 13:22:28 in reply to 5 [link] [source]

After looking over my SQL-file I wonder if --bom is the best idea for option setting in an .sql file?

.output --bom 

looks like You've commented a bom parameter.

(9) By Larry Brasfield (LarryBrasfield) on 2020-04-21 14:30:25 in reply to 8 [link] [source]


.output --bom

considered as an option setting in "an .sql file":

I would see that kind of extension, for something that is not legal SQL, to be a step or more along the road to insanity. It's a fine option name for what it does, and per the syntax of SQLite shell input, it is obviously a shell command rather than SQL. So, my suggestion is: Wonder no more!

(10) By VĂ­ctor (wzrlpy) on 2024-05-31 20:51:33 in reply to 5 [link] [source]

For the benefit of the people enduring the idiosyncrasies of Excel, may you please mention the --bom option into the shell docs?