CODEWITHSUNDEEP
bashpostgresqlshellcsvinsert
Bertug
Bertug

Reputation: 5

Inserting a CSV file into PostgreSQL table using bash script

I'm trying to insert a CSV file into a PSQL table using a bash script. My first aim is parsing the CSV file then insert the datas, row by row. COPY command is not convenient for me. I've used the following script but it doesn't worked.

Edit: I didn't create the table by the way. Should I?

#!/bin/sh             
    while IFS=, read col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
    do
        echo "INSERT INTO table_name ("a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "l") VALUES ('$col1','$col2','$col3','$col4','$col5','$col6','$col7','$col8','$col9','$col10','$col11','$col12');"
    done < ppr.csv | sudo psql -U pg_user -d test;

Upvotes: 0

Views: 2221

Answers (2)

Laurenz Albe
Laurenz Albe

Reputation: 248215

Don't try to parse CSV with bash, that would be quite hard to get right.

You should use file_fdw to define a foreign table in the database. That allows you to access the data from the database without actually loading it. In a way, you turn PostgreSQL into a CSV parser.

Then you can select from that foreign table like from a normal table and for example insert parts of it into a proper PostgreSQL table.

Upvotes: 1

Matias Barrios
Matias Barrios

Reputation: 5054

You should escape inner double quotation marks :

#!/bin/sh             
while IFS=, read col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 col12
do
    echo "INSERT INTO table_name (\"a\", \"b\", \"c\", \"d\", \"e\", \"f\", \"g\", \"h\", \"i\", \"j\", \"k\", \"l\") VALUES ('$col1','$col2','$col3','$col4','$col5','$col6','$col7','$col8','$col9','$col10','$col11','$col12');"
done < ppr.csv | sudo psql -U pg_user -d test;

Upvotes: 0

Related Questions