sqlite3 and bash, database settings

Question

My bash script automates some data input into an sqlite database. The issue I encounter is that every time I change a database setting, the changes are lost when I run the next command. An example will explain this.

sqlite3 Correlate.db ".mode csv"
sqlite3 Correlate.db ".output Correlated.csv"
sqlite3 Correlate.db "SELECT * FROM ALL_Data;
sqlite3 Correlate.db ".show"

The end result of .show is as follows:

     echo: off
  explain: off
  headers: off
     mode: list
nullvalue: ""
   output: stdout
separator: "|"
    stats: off
    width: 

It seems that my changing the output mode to csv has been lost Also my output file has no data, even though stdout is printing the data I want to see. Is it resetting the database settings each time I call it? I'm rather confused!

Answer 1

I would try something like this to send multiple commands to one instance of sqlite3:

sqlite3 Correlate.db <<EOF
.mode csv
.output Correlated.csv
SELECT * FROM ALL_Data;
.show
EOF

You could also put these commands in a file and redirect it to the sqlite3, e.g.:

sqlite3 Correlate.db < commandfile