How to dump all the tables into separate files?

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

How to dump all the tables into separate files?

Peng Yu
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?

--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Simon Slavin-3


On 8 Dec 2017, at 7:02am, Peng Yu <[hidden email]> 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?

There’s no direct output from the SQLite library to produce TSV format.

You could write one in your own programming language.

Alternatively, you could script the SQLite Command-line tool to produce your .tsv files for you.  Take a look at section 15 of

<https://sqlite.org/cli.html>

which you might want to use with

.mode tabs

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Luuk
On 08-12-17 14:13, Simon Slavin wrote:

>
> On 8 Dec 2017, at 7:02am, Peng Yu <[hidden email]> 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?
> There’s no direct output from the SQLite library to produce TSV format.
>
> You could write one in your own programming language.
>
> Alternatively, you could script the SQLite Command-line tool to produce your .tsv files for you.  Take a look at section 15 of
>
> <https://sqlite.org/cli.html>
>
> which you might want to use with
>
> .mode tabs
>
> Simon.
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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 ;)


_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Peng Yu
Could you provide the working code for bash (on Mac OS X or linux)? Thanks.

On Fri, Dec 8, 2017 at 7:52 AM, Luuk <[hidden email]> wrote:

> On 08-12-17 14:13, Simon Slavin wrote:
>>
>>
>> On 8 Dec 2017, at 7:02am, Peng Yu <[hidden email]> 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?
>>
>> There’s no direct output from the SQLite library to produce TSV format.
>>
>> You could write one in your own programming language.
>>
>> Alternatively, you could script the SQLite Command-line tool to produce
>> your .tsv files for you.  Take a look at section 15 of
>>
>> <https://sqlite.org/cli.html>
>>
>> which you might want to use with
>>
>> .mode tabs
>>
>> Simon.
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> 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 ;)
>
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users



--
Regards,
Peng
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Simon Slavin-3


On 8 Dec 2017, at 1:58pm, Peng Yu <[hidden email]> wrote:

> Could you provide the working code for bash (on Mac OS X or linux)? Thanks.

Something like this …

sqlite3 myDatabase.sqlite > myTable.tsv << EOS
.mode tabs        
select * from myTable;
EOS

The first EOS must be at the very end of the line — no whitespace.
The first EOS must be the only thing on the line — no whitespace — and followed by the return character.

Alternatively put the tool commands in a script file and do

sqlite3 myDatabase.sqlite > myTable.tsv < myScript.txt

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Luuk
In reply to this post by Luuk
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 <[hidden email]> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Donald Griggs
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 <[hidden email]> 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: How to dump all the tables into separate files?

Luuk
You are correct, but i'm not using sqlite enough to know (or remember)
it also has a sqlite_master :-):-)

On 08-12-17 16:48, Donald Griggs wrote:
> 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

The order by is not needed here (who cares about the order of exporting
tables??? :-P:-D

> 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 <[hidden email]> 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 <[hidden email]> 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
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users