How to generate table from csv in postgres sql

Question

I am new to database management and we are using psql. All I need to do is to migrate csv (around 200 tables) to our database. Manually creating tables for every csv file is bit tiresome so please help me out, Is there any way to generate table from csv file?

You can use [COPY](http://www.postgresql.org/docs/9.4/static/sql-copy.html) to do the job, see [this](http://stackoverflow.com/tags/postgresql-copy/info) — Vivek S., Sep 01 '15 at 09:09
[This](http://stackoverflow.com/a/30951435/3682599) is what you need to do — Vivek S., Sep 01 '15 at 09:12
thanks, does this copy command will populate the table from csv file??? — saniojoseph, Sep 01 '15 at 11:59
You're going to have to write a script that reads in the csv files and writes out create table statements. — user464502, Sep 01 '15 at 13:27
i would really appreciate if any one could help me out to find a script that will automatically populate the table from csv. its just am new to back end, please — saniojoseph, Sep 03 '15 at 04:58
finally i wrote a query that can populate the table from csv — saniojoseph, Sep 28 '15 at 07:05

score 1 · Answer 1 · answered Jun 24 '21 at 13:55

Answered at DBA Stackexchange by the OP. I'm copying the answer here because this was the first link returned by my search engine.

OP made a script like:

DATADIR='data'                      # this directory name


PREFIX='jobd'
DBNAME='divacsv'

function createSchema {
    COLUMNS=`head -n 1 $1 | 
        awk -F, '{for(i=1; i<=NF; i++){out=out $i" text, ";} print out;}' | 
        sed 's/ text, $/MYEXTRA text/' | 
        sed 's/"//g'`
    CMD_CREATE="psql $DBNAME -c \"CREATE TABLE $2 ($COLUMNS);\""

    echo $CMD_CREATE
    sh -c "$CMD_CREATE"

    CMD_COPY="psql divacsv  -c \"COPY $2 FROM '"`pwd`"/$1' DELIMITER ',' CSV;\""
    echo $CMD_COPY
    sh -c "$CMD_COPY"
}

for file in $DATADIR/*.csv; do
    table=$PREFIX"_"`echo $file | sed 's/.*\///' | sed 's/.csv//' `
    createSchema "$file" $table
done

Comments advise that HEADER might be needed to avoid loading first line with header texts, which is true.

I've tested this code but couldn't make it work under CentOS.

How to generate table from csv in postgres sql

1 Answers1