For windows, I think the following would work: sqlite3 %DB% -batch "SELECT TBL_NAME FROM sqlite_master WHERE type=='table' order by tbl_name;" >%temp%\dump.tmp
for /f "usebackq" %%i in (`type %temp%\dump.tmp`) do sqlite3 %DB% -batch ".mode tabs" -batch "SELECT * FROM %%i " >%%i.tsv delete %temp%\dump.tmp >nul On Fri, Dec 8, 2017 at 10:19 AM, Luuk <luu...@gmail.com> wrote: > On 08-12-17 14:52, Luuk wrote: > >> On 08-12-17 14:13, Simon Slavin wrote: >> >>> >>> On 8 Dec 2017, at 7:02am, Peng Yu <pengyu...@gmail.com> wrote: >>> >>> I'd like to dump all the tables to separate files, one table one file. >>>> Each file should be in TSV format. >>>> >>>> Is there a convenient way to do so in sqlite3? >>>> >>> >>> which you might want to use with >>> >>> .mode tabs >>> >>> Simon. >>> >> On Windows you can use this batchfile: >> @echo off >> set DB=somedirectory\yourDBfile.sqlite >> sqlite3 %DB% -batch ".tables" >c:\temp\dump.tmp >> for /f "usebackq" %%i in (`type c:\temp\dump.tmp`) do sqlite3 %DB% -batch >> ".mode tabs" -batch "SELECT * FROM %%i" >%%i.tsv >> >> Let /me know if you do not use Windows, this can be rewritten to work on >> another OS too ;) >> >> >> > There's a bug in my script (Windows verion only, its solved in the > linux-version :-):-)!) > > when doing this: > sqlite3 db.sqlite -batch ".tables" > > My script 'assumes' the output gives 1 table per line, and now only the > first table on a line is exported to TSV > > in Linux writing scripts is more easy, so this problem can be dealt with: > > #!/bin/bash > > db=somedirectory/yourDBfile.sqlite > sqlite3 $db -batch ".tables" | sed -e 's/ */\n/g' | grep -v '^$' | while > read line > do > echo $line > sqlite3 $db -batch ".mode tabs" -batch "SELECT * FROM $line" > >$line.tsv > done > > > > For MacOS you have to replace the sed expression: > 's/ */\n/g' (BTW there are 2 spaces before the '*'!! > with: > 's/ */\$'\n/g' (still 2 spaces before the '*' :-) > > > > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
