CODEWITHSUNDEEP
databasepostgresqlimport-from-csvpostgresql-copy
saniojoseph
saniojoseph

Reputation: 21

How to generate table from csv in postgres sql

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?

Upvotes: 2

Views: 3118

Answers (1)

EAmez
EAmez

Reputation: 895

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.

Upvotes: 1

Related Questions