Thread: Exporting 1 text column from all rows from a table to a file per row?
Hi, Is it possible, and if so how, to export a single column of a table into a separate file per row? I have a table with ~21000 rows that have a column "body1" containing ASCII text and I want to have 21000 separate ASCII files, each containing that column "body1". The name of the file does not matter, although it would be nice if they had the extension "txt". TIA -- Groeten, Joost Kraaijeveld Askesis B.V. Molukkenstraat 14 6524NB Nijmegen tel: 024-3888063 / 06-51855277 fax: 024-3608416 web: www.askesis.nl
On 21 November 2011 19:10, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote: > Hi, > > Is it possible, and if so how, to export a single column of a table into > a separate file per row? I have a table with ~21000 rows that have a > column "body1" containing ASCII text and I want to have 21000 separate > ASCII files, each containing that column "body1". The name of the file > does not matter, although it would be nice if they had the extension > "txt". Does the data contain newlines? If not, you can just export it to a single file then use: split -l 1 exportedfile.txt -- Thom Brown Twitter: @darkixion IRC (freenode): dark_ixion Registered Linux user: #516935 EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl> writes: > Hi, > > Is it possible, and if so how, to export a single column of a table > into a separate file per row? I have a table with ~21000 rows that > have a column "body1" containing ASCII text and I want to have 21000 > separate ASCII files, each containing that column "body1". The name > of the file does not matter, although it would be nice if they had > the extension "txt". run a script like this and save to a file that you will later run sh on... psql -Atqf thatscript >sh_commands create temp sequence s; select 'cat <<! >' || nextval('s')::text || '.txt ' || somefield || ' !' from sometable; > TIA > > -- > Groeten, > > Joost Kraaijeveld > Askesis B.V. > Molukkenstraat 14 > 6524NB Nijmegen > tel: 024-3888063 / 06-51855277 > fax: 024-3608416 > web: www.askesis.nl > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Jerry Sievers Postgres DBA/Development Consulting e: postgres.consulting@comcast.net p: 305.321.1144
Hi, On 22 November 2011 06:10, Joost Kraaijeveld <J.Kraaijeveld@askesis.nl> wrote: > Is it possible, and if so how, to export a single column of a table into > a separate file per row? I have a table with ~21000 rows that have a > column "body1" containing ASCII text and I want to have 21000 separate > ASCII files, each containing that column "body1". The name of the file > does not matter, although it would be nice if they had the extension > "txt". Something like this could do the job: for i in `psql -a -t -c 'select id from T'`; do psql -a -t -c "select body1 from T where id = $i" -o $i.txt; done; where 'T' is your table and 'id' is primary column in that table. I've omitted connection options (-h -U ...) from psql commands. -- Ondrej Ivanic (ondrej.ivanic@gmail.com)