How to convert an SQLite database to Excel

[hackerman1]

Here is how to convert an SQLite database to Excel

Note: this is just an example, you can use your own filenames and paths etc.

This conversion requires the command-line shell program SQLite: http://www.sqlite.org/2015/sqlite-shell-win32-x86-3080803.zip

Commands and examples: http://www.sqlite.org/cli.html

Info: http://www.sqlite.org/index.html

Conversion

Create a text file with the SQLite commands

.header off

.mode csv

.once "csv.txt"                                        (Enter the name of the ouput-file: "csv.txt")

select * from log;

save it as SQLite to CSV.txt

Create a bat-file with the commands

sqlite3 SQLite.db <  SQLite to CSV.txt       (Enter the name of the database-file: "SQLite.db")

Save it as SQLite to CSV.bat

Note: in this example the SQLite-database is located in the same folder as the SQLite commandline-tool (sqlite3).
If not, then you must enter the correct path in the bat-file above.

Run SQLite to CSV.bat

If you have a non-english version of the O/S you need to temporarily change the settings for “Decimal” to US-standard,
which can easily be done with a reg-script.

US-DECIMAL.reg

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERControl PanelInternational]

"sDecimal"="."

"sThousand"=","

You can run the reg-script and answer yes to the confirmations,
or use this bat-file which runs the script without asking any questions.

US-DECIMAL.bat

REGEDIT.EXE /S "US-DECIMAL.REG"     (Enter the correct path to the reg-script)

Run US-DECIMAL.bat

Start Excel
Select “Data”
“From Text”
Select the output-file ( “csv.txt” )
Select Filetype “Delimited”
Next
Mark “Comma”
Next
Finish
OK

When you have finished the conversion you have to restore the settings for “Decimal”.

SWEDISH-DECIMAL.reg

Windows Registry Editor Version 5.00

[HKEY_CURRENT_USERControl PanelInternational]

"sDecimal"=","

"sThousand"="."

SWEDISH-DECIMAL.bat

REGEDIT.EXE /S "SWEDISH-DECIMAL.reg"      (Enter the correct path to the reg-script)

You can run the reg-script and answer yes to the confirmations,
or use this bat-file which runs the script without asking any questions.

[Author]

Posts