Export from sqlite to csv using shell script

I'm making a shell script to export a sqlite query to a csv file, just like this:

 #!/bin/bash
./bin/sqlite3 ./sys/xserve_sqlite.db ".headers on"
./bin/sqlite3 ./sys/xserve_sqlite.db ".mode csv"
./bin/sqlite3 ./sys/xserve_sqlite.db ".output out.csv"
./bin/sqlite3 ./sys/xserve_sqlite.db "select * from eS1100_sensor_results;"
./bin/sqlite3 ./sys/xserve_sqlite.db ".exit"

When executing the script, the output apears on the screen, instead of being saved to "out.csv". It's working doing the same method with the command line, but I don't know why the shell script fails to export data to the file.

What am I doing wrong?

Solution 1:

Instead of the dot commands, you could use sqlite3 command options:

sqlite3 -header -csv my_db.db "select * from my_table;" > out.csv

This makes it a one-liner.

Also, you can run a sql script file:

sqlite3 -header -csv my_db.db < my_script.sql > out.csv

Use sqlite3 -help to see the list of available options.

Solution 2:

sqlite3

You have a separate call to sqlite3 for each line; by the time your select runs, your .out out.csv has been forgotten.

Try:

#!/bin/bash
./bin/sqlite3 ./sys/xserve_sqlite.db <<!
.headers on
.mode csv
.output out.csv
select * from eS1100_sensor_results;
!

instead.

sh/bash methods

You can either call your script with a redirection:

$ your_script >out.csv

or you can insert the following as a first line in your script:

exec >out.csv

The former method allows you to specify different filenames, while the latter outputs to a specific filename. In both cases the line .output out.csv can be ignored.

Solution 3:

I recently created a shell script that will be able to take the tables from a db file and convert them into csv files.

https://github.com/darrentu/convert-db-to-csv

Feel free to ask me any questions on my script :)