3

I am new to database management and we are using PostgreSQL. All I need to do is to migrate CSV files (corresponding to around 200 tables) to our database. Manually creating tables for every CSVfile is a bit tiresome, so please help me out. Is there any way not only to import data, but also to generate tables from CSV files?

5

You might look at PgFutter to help you out. It sounds like it would fit your use case well, because it creates tables for CSV formatted data based on the header line, and then loads the data into the tables.

1

Finally, I wrote a script that can populate the table from CSV.

This is it:

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
  • 2
    What is 'MYEXTRA' doing there? Also, it looks like you are inserting the header into the table, too. I think you need the HEADER option to COPY, too. Furthermore, instead of the sed magic for removing the filename extensions, you could use basename. – dezso Sep 28 '15 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.