Re: [sqlite] How to import data from stdin?
On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin wrote: > > On 15 Aug 2010, at 11:31pm, Peng Yu wrote: > >> $ cat file.txt >> 1 eenie >> 2 meenie >> 3 miny >> 4 mo >> $cat main.sql >> #!/usr/bin/env bash >> >> rm -f main.db >> sqlite3 main.db < file.txt < > That cannot work: it would require a file with commands in and your file has > data in. Now, I understand. It is because both the input file and the here document use the same stdin. I'll have to use the .read command like the following. Then the problem is solved. Thank you for everybody that helped me! $ cat main.sql create table test (id integer primary key, value text); .separator "\t" .import /dev/stdin test .headers on .mode column select * from test; $ cat main.sh #!/usr/bin/env bash rm -rf main.db cat file.txt | sqlite3 main.db '.read main.sql' $ cat file.txt 1 eenie 2 meenie 3 miny 4 mo $ ./main.sh id value -- -- 1 eenie 2 meenie 3 miny 4 mo >> create table test (id integer primary key, value text); >> .separator "\t" >> .import /dev/stdin test >> >> .headers on >> .mode column >> select * from test; >> >> EOF > > Some of those lines are commands to your Unix shell and others are commands > for the sqlite3 program. You cannot mix a shell script and SQL commands like > that. Try these three files: > > $ cat file.tsv > 1 eenie > 2 meenie > 3 miny > 4 mo > > $cat importfile.sql > create table test (id integer primary key, value text); > .separator "\t" > .import file.tsv test > .headers on > .mode column > select * from test; > > $cat importfile > #!/usr/bin/env bash > rm -f main.db > sqlite3 main.db '.read importfile.sql' > > I have not tested this, but it should point you in the right direction. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to import data from stdin?
On 16 Aug 2010, at 12:40am, Peng Yu wrote: > This is called here document in bash. You can mix sqlite3 code with bash code. Thank you. I did not know about this function. I assume this page http://tldp.org/LDP/abs/html/here-docs.html describes what you're doing. > But my question on how to read from stdin is still not answered. Make the following two changes sqlite3 main.db
Re: [sqlite] How to import data from stdin?
On Sun, Aug 15, 2010 at 4:40 PM, Peng Yu wrote: > On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin wrote: >> >> On 15 Aug 2010, at 11:31pm, Peng Yu wrote: >>> sqlite3 main.db < file.txt <> Some of those lines are commands to your Unix shell and others are commands >> for the sqlite3 program. You cannot mix a shell script and SQL commands >> like that. Try these three files: > > This is called here document in bash. You can mix sqlite3 code with bash code. Aren't you setting stdin for the sqlite3 utility to file.txt, AND to the contents of the here document. How is sqlite3 supposed to distinguish between the two types of data coming into it on stdin? ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to import data from stdin?
On Sun, Aug 15, 2010 at 5:58 PM, Simon Slavin wrote: > > On 15 Aug 2010, at 11:31pm, Peng Yu wrote: > >> $ cat file.txt >> 1 eenie >> 2 meenie >> 3 miny >> 4 mo >> $cat main.sql >> #!/usr/bin/env bash >> >> rm -f main.db >> sqlite3 main.db < file.txt < > That cannot work: it would require a file with commands in and your file has > data in. > >> create table test (id integer primary key, value text); >> .separator "\t" >> .import /dev/stdin test >> >> .headers on >> .mode column >> select * from test; >> >> EOF > > Some of those lines are commands to your Unix shell and others are commands > for the sqlite3 program. You cannot mix a shell script and SQL commands like > that. Try these three files: This is called here document in bash. You can mix sqlite3 code with bash code. > $ cat file.tsv > 1 eenie > 2 meenie > 3 miny > 4 mo > > $cat importfile.sql > create table test (id integer primary key, value text); > .separator "\t" > .import file.tsv test > .headers on > .mode column > select * from test; > > $cat importfile > #!/usr/bin/env bash > rm -f main.db > sqlite3 main.db '.read importfile.sql' I didn't know .read command. I'll try it. But it seems that it is functionally equivalent to bash here document. But my question on how to read from stdin is still not answered. > I have not tested this, but it should point you in the right direction. > > Simon. > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Peng ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] How to import data from stdin?
On 15 Aug 2010, at 11:31pm, Peng Yu wrote: > $ cat file.txt > 1 eenie > 2 meenie > 3 miny > 4 mo > $cat main.sql > #!/usr/bin/env bash > > rm -f main.db > sqlite3 main.db < file.txt < create table test (id integer primary key, value text); > .separator "\t" > .import /dev/stdin test > > .headers on > .mode column > select * from test; > > EOF Some of those lines are commands to your Unix shell and others are commands for the sqlite3 program. You cannot mix a shell script and SQL commands like that. Try these three files: $ cat file.tsv 1 eenie 2 meenie 3 miny 4 mo $cat importfile.sql create table test (id integer primary key, value text); .separator "\t" .import file.tsv test .headers on .mode column select * from test; $cat importfile #!/usr/bin/env bash rm -f main.db sqlite3 main.db '.read importfile.sql' I have not tested this, but it should point you in the right direction. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] How to import data from stdin?
Hi, I have the following data file and sql script. But I get the following error. I think that I should be able to import the data from from /dev/stdin. I replace /dev/stdin with an ordinary file and it works. Could anybody let me know what is the correct way to do so? Thank you very much! .import /dev/stdin main Error: /dev/stdin line 1: expected 2 columns of data but found 1 $ cat file.txt 1 eenie 2 meenie 3 miny 4 mo $cat main.sql #!/usr/bin/env bash rm -f main.db sqlite3 main.db < file.txt
