Export from sqlite to csv using shell script

Question

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?

score 201 · Answer 1 · answered Feb 12 '14 at 22:38

201

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.

answered Feb 12 '14 at 22:38

Hollister

3,758
2
20
22

5

Much, much better... this should be the accepted solution! KISS :-) – pvandenberk Oct 05 '15 at 16:22
6

This solution will also work on windows, granted that sqlite3 is on your path or in the same directory. – amenthes Oct 11 '15 at 19:51

score 129 · Accepted Answer · edited Jun 20 '20 at 09:12

129

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.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 25 '11 at 08:54

tzot

92,761
29
141
204

Great, thanks, now it's working! (and it's needed ">>" instead of ">", but I'm a newbei in linux :D) – Rorro Apr 25 '11 at 11:15
1

If an answer here actually _answers_ your question, you should click the check mark below the total votes of the answer (it should appear light grey to you). This marks the question as answered. – tzot Apr 25 '11 at 15:13
Sorry for the delay, i'm a real real newbie :D – Rorro May 04 '11 at 14:45
`<<! ... !` is exactly what I needed. Thanks. – Rostyslav Druzhchenko Feb 04 '20 at 11:16

score 7 · Answer 3 · answered Feb 21 '17 at 21:56

7

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

answered Feb 21 '17 at 21:56

DarrenNEEDSHELP

148
2
9

4

This helped me. You should copy-paste your 10 lines of code into your answer, then the upvote I've already given would feel more justified. – mxmlnkn Feb 16 '18 at 14:32
The script is missing quotes around the name of the table (it throws an error if the table name has dashes) – Eric Ferreira Jun 12 '20 at 15:33

score 0 · Answer 4 · answered Aug 05 '17 at 19:12

Although the question is about shell script, I think it will help few of those who are just bothered about transferring the data from the sqlite3 database to a csv file.

I found a very convinient way to do it with the firefox browser using SQLite Manager extension.

Simply connect to your sqlite database file in firefox ( SQlite manager -> connect database ) and then Table -> Export table. You will be served with some more options that you can just click and try....

In the end you get a csv file with the table u have chosen to be exported.

score 0 · Answer 5 · answered Apr 05 '20 at 06:25

Using command line for Linux:

user@dell-Admin: sqlite3 #activate your sqlite database first
sqlite> .tables #search for tables if any available if already created one.
sqlite> .schema #if you want to check the schema of the table.

# once you find your table(s), then just do the following:

sqlite> .headers on   #export along with headers (column names)
sqlite> .mode csv     #file type is csv
sqlite> .output example.csv   #you want to provide file name to export
sqlite> SELECT * from events;    #If entire table is needed or select only required
sqlite> .quit    #finally quit the sqlite3

Now search in your system for example.csv file and you will get it.

score 0 · Answer 6 · answered Mar 05 '21 at 18:43

0

In one line is

sqlite3 -header -csv ./sys/xserve_sqlite.db "select * from eS1100_sensor_results;" >./out.csv

answered Mar 05 '21 at 18:43

aborruso

4,938
3
23
40

score 0 · Answer 7 · answered Apr 07 '21 at 20:24

A synthesis of the answers till now:

function sqlite2csv-table() {
    local db="${1}" table="${2}" output="${3}"
    if test -z "$output" ; then
        output="${db:r}_hi${table}.csv"
    fi
    [[ "$output" =~ '.csv$' ]] || output+='.csv'

    echo "$0: outputting table '$table' to '$output'"
    sqlite3 -header -csv "$db" "select * from ${table};" > "$output" || return $?
}
function sqlite2csv() {
    local db="${1}" o="${2}"

    tables=($(sqlite3 $db ".tables")) 
    local t
    for table in $tables[@] ; do
        sqlite2csv-table "$db" "$table" "${o}_${table}.csv"
    done
}

Usage:

sqlite2csv some.db [/path/to/output]

Export from sqlite to csv using shell script

7 Answers7

sqlite3

sh/bash methods

Linked