CODEWITHSUNDEEP
postgresqlbashpsql
FeoJun
FeoJun

Reputation: 153

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

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

Upvotes: 0

Views: 219

Answers (1)

wildplasser
wildplasser

Reputation: 44250

  • 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

Upvotes: 1

Related Questions