Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Exports all tables in a sqlite database to CSV.
#!/usr/bin/env bash
# obtains all data tables from database
TS=`sqlite3 $1 "SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';"`
# exports each table to csv
for T in $TS; do
sqlite3 $1 <<!
.headers on
.mode csv
.output $T.csv
select * from $T;
!
done
@holgerjakobs
Copy link

holgerjakobs commented Oct 22, 2019

#!/usr/bin/env bash

A bash script to export all tables from an SQLite database

to TSV files in a directory named after the input database.

The directory name is the base name of the database (the last

dot and everything after it is discarded) with -tables

appended to it. The directory is created if it doesn't

already exist. Existing files named as tables from the

db plus the extension .tab are overwritten. Other files

won't be touched.

obtain names of all tables from database

TS=$(sqlite3 -noheader $1 "SELECT tbl_name FROM sqlite_master WHERE type='table' and tbl_name not like 'sqlite_%';")

dir=${1%.*}-tables
mkdir -p $dir

export tables in a loop to tab files

for T in $TS; do
echo exporting table $T ...
sqlite3 -header $1 <<EOF
.mode tab
.output $dir/$T.tab
select * from $T;
EOF

done

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment