export SQLite empty tables to csv with headers

Question

When I export tables from an SQLite database to csv files with headers, the tables that are empty return an empty csv file.
I would like to obtain a csv file with just the header in that case.

Here is an example.

Create a data base with tblA and tblB where table A has no data.

sqlite3 test.sqlite

CREATE TABLE tblA (
  ID LONG,
  Col01 TEXT
 );

CREATE TABLE tblB (
  ID LONG,
  Col01 TEXT
 );

INSERT INTO tblB (ID, Col01)
VALUES
(1, "AAA"), 
(2, "BBB");

.quit

Export all tables in csv :

# obtain all data tables from database
tables=`sqlite3 test.sqlite "SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';"`

for i in $tables ; do 
    sqlite3 -header -csv test.sqlite "select * from $i;" > "$i.csv" ;
done

Check the content of these csv files :

[tmp] : head *csv
==> tblA.csv <==

==> tblB.csv <==
ID,Col01
1,AAA
2,BBB

I would like to obtain this instead :

[tmp] : head *csv
==> tblA.csv <==
ID,Col01

==> tblB.csv <==
ID,Col01
1,AAA
2,BBB

cody · Accepted Answer · 2018-12-16T21:04:03.713

2

One option is to utilize pragmatable_info to get the column names, and then just append the rows' content:

for i in $tables ; do
    sqlite3 test.sqlite "pragma table_info($i)" | cut -d '|' -f 2 | paste -s -d, > "$i.csv"
    sqlite3 -csv test.sqlite "select * from $i;" >> "$i.csv"
done

Result:

$ cat tblA.csv
ID,Col01
$ cat tblB.csv
ID,Col01
1,AAA
2,BBB

edited Dec 16 '18 at 21:04

answered Dec 16 '18 at 02:36

cody

11,045
3
21
36

2

You can also use `SELECT group_concat(name, ',') FROM pragma_table_info('table_name');` to get comma separated names directly. – Shawn Dec 16 '18 at 02:58
@Shawn Nice, that is cleaner. Updated answer. – cody Dec 16 '18 at 03:05
Thanks for the answer. I don't have any pragma_table_info. My version of sqlite is 3.8.8. Do you think that this might be the reason? – Gilles San Martin Dec 16 '18 at 21:00
@Gilles Hmm, strange. My original answer used the `pragma table_info` statement rather than the `pragma_table_info` table recommended by the above commenter. I've rolled back my answer to the original version, if you would like to try that. – cody Dec 16 '18 at 21:05
Yes it works perfectly now. Both solutions might probably be useful. Thanks ! – Gilles San Martin Dec 16 '18 at 22:26

score 0 · Answer 2 · answered Jul 07 '22 at 11:55

Combining @Shawn comment with https://stackoverflow.com/a/27710284/788700

# do processing:
sqlite3 -init script.sql test.sqlite .exit

# if processing resulted in empty csv file, write header to it:
test -s tblA.csv || sqlite3 test.sqlite "select group_concat(name, ',') from pragma_table_info('tblA')"

export SQLite empty tables to csv with headers

2 Answers2