Psql output csv file formatting

Question

Trying to make batch file that will get query from script file and put results into csv. Batch file code:

psql -h host -d test -U test -p 5432 -a -q -f C:\Users\test\Documents\my_query.sql TO STDOUT WITH CSV HEADER DELIMITER ';' > C:\Users\test\Documents\res.csv

In result file I'm getting result like this:

select *

from public.test

limit 3

    id    |    name     | count_01
----------+------------+---------------+
 11021555 | a       |             1 |
 39534568 | b       |             2 |
 11695210 | c       |             3 |

(3 rows)

How to get only script results without rows count and symbols like '|' or '+' and using ';' delimetres as in the usual csv file?

Working script:

psql -h host -d test -U test -p 5432 -q --quiet --no-align --field-separator=';' --file=C:\Users\test\Documents\my_query.sql --output=C:\Users\test\Documents\res.csv

Have you try "--no-align" and "--field-separator=" options? — Daemon-5
– Daemon-5, Commented Oct 5, 2021 at 15:15
Pretty sure you are getting the output of whatever is my_query.sql and never getting to the COPY. Add the contents of my_query.sql to your question. — Adrian Klaver
– Adrian Klaver, Commented Oct 5, 2021 at 15:34
Have you used the site search facility at the top of the page, when seeking examples of the use of the available options, you should have read? The first hit I found was this. — Compo
– Compo, Commented Oct 5, 2021 at 15:35
Using --field-separator=, will only work reliably if the data contain no commas... — Laurenz Albe
– Laurenz Albe, Commented Oct 6, 2021 at 7:18

Laurenz Albe · Accepted Answer · 2021-10-06 07:14:51Z

2

From PostgreSQL v12 on, you can use the CSV output format of psql:

psql --quiet --csv --file=my_query.sql --output=res.csv

--quiet suppresses the psql welcome message.

edited Oct 6, 2021 at 7:14

answered Oct 5, 2021 at 16:05

Laurenz Albe

257k22 gold badges313 silver badges389 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

FeoJun Over a year ago

When trying to add '--csv' getting an error 'psql: illegal option -- csv'

Laurenz Albe Over a year ago

Then you are using a version older than v12 of psql.

Ftisiot · Accepted Answer · 2021-10-05 15:33:50Z

0

Should work with

psql -h host -d dbname -U user -p port -a -q -f my_query.sql -o res.csv --record-separator=',' --csv

answered Oct 5, 2021 at 15:33

Ftisiot

1,8981 gold badge9 silver badges14 bronze badges

Collectives™ on Stack Overflow

Psql output csv file formatting

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related