Batch file for reading sql scripts from file and export results to csv

Question

I want to make a batch file that will get query from .SQL script from the directory and export results in .csv format. I need to connect to the Postgres server.

So I'm trying to do this using that answer https://stackoverflow.com/a/39049102/9631920. My file:

#!/bin/bash
# sql_to_csv.sh
echo test1
CONN="psql -U my_user -d my_db -h host -port"
QUERY="$(sed 's/;//g;/^--/ d;s/--.*//g;' 'folder/folder/folder/file.sql' | tr '\n' ' ')"
echo test2
echo "$QUERY"
echo test3
echo "\\copy ($QUERY) to 'folder/folder/folder/file.csv' with csv header" | $CONN > /dev/null
echo query in progress

It shows me script from query and test3 and then stops. What am I doing wrong?

edit. My file:

#!/bin/bash

PSQL = "psql -h 250.250.250.250 -p 5432 -U user -d test"

${PSQL} << OMG2

CREATE TEMP VIEW xyz AS
`cat C:\Users\test\Documents\my_query.sql`
        ;

\copy (select * from xyz) TO 'C:\Users\test\Documents\res.csv';

OMG2

But it's not asking password, and not getting any result file

It's reading and creating the result file now. But the result file is empty. Can't understand why, tried on 3 different scripts — FeoJun
– FeoJun, Commented Oct 4, 2021 at 14:45
What is in folder/folder/folder/file.sql ? Just a single query? — wildplasser
– wildplasser, Commented Oct 4, 2021 at 14:46
Put it into a TEMP view and `\copy (select * from the_view) TO 'the_file.csv'; (no need to remove comments, or put everything on a single line!) — wildplasser
– wildplasser, Commented Oct 4, 2021 at 14:49
So I need to create temp view for every script? I think it's not possible because every time I need a new script to complete — FeoJun
– FeoJun, Commented Oct 4, 2021 at 14:56

wildplasser · Accepted Answer · 2021-10-05 12:15:10Z

1

a shell HERE-document will solve most of your quoting woes
a temp view will solve the single-query-on-a-single line problem

Example (using a multi-line two-table JOIN):

#!/bin/bash

PSQL="psql -U www twitters"

${PSQL} << OMG

        -- Some comment here
CREATE TEMP VIEW xyz AS
SELECT twp.name, twt.*
FROM tweeps twp
JOIN tweets twt
        ON twt.user_id = twp.id
        AND twt.in_reply_to_id > 3
WHERE 1=1
AND (False OR  twp.screen_name ilike '%omg%' )
        ;

\copy (select * from xyz) TO 'omg.csv';

OMG

If you want the contents of an existing .sql file, you can cat it inside the here document, using a backtick-expansion:

#!/bin/bash

PSQL="psql -X -n -U www twitters"

${PSQL} << OMG2

        -- Some comment here
CREATE TEMP VIEW xyz AS
-- ... more comment
-- cat the original file here
`cat /home/dir1/dir2/dir3/myscript.sql`
        ;

\copy (select * from xyz) TO 'omg.csv';

OMG2

edited Oct 5, 2021 at 12:15

answered Oct 4, 2021 at 15:03

wildplasser

44.5k9 gold badges72 silver badges116 bronze badges

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

6 Comments

FeoJun Over a year ago

CREATE TEMP VIEW xyz AS \i folder/folder/folder/file.sql?

wildplasser Over a year ago

Yes, but inside the HERE-document, and on a separate line.

wildplasser Over a year ago

Sorry, psql won't allow \include accross statement boundaries.

FeoJun Over a year ago

Tried to make it like your last edit, and can't get any result using .sh file. It's creating some postgress command in bash and closing it in a moment

wildplasser Over a year ago

Maybe you missed the backtics inside the here-document? (it worked here)

|

Collectives™ on Stack Overflow

Batch file for reading sql scripts from file and export results to csv

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related