CODEWITHSUNDEEP
sqlite
Gilles San Martin
Gilles San Martin

Reputation: 4370

export SQLite empty tables to csv with headers

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

Upvotes: 4

Views: 930

Answers (2)

Adobe
Adobe

Reputation: 13467

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')"

Upvotes: 0

cody
cody

Reputation: 11157

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

Upvotes: 2

Related Questions