4

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?

6

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
  • This answer to a similar question on Stack Overflow gives more details on how to use PgFutter – user8378 Jun 15 '20 at 15:24
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
1
  • 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
0

There's some ways to workaround this issue. You can find some scripts around the internet which will do what you're looking for, but the best way I know is using this Data Mover Project. Besides, it's already published to docker as techindicium/spark-datamover:v0.1. You can call it from the command line:

docker run --network host techindicium/spark-datamover:v0.1 -s /home/path/your_file.csv --filetype csv --destination "jdbc:postgresql://localhost:PORT/DATABASE?user=USERNAME&password=PASSWD" --destination-table MY_DEST_TABLE

0

cvssql from csvkit does the same. It analyses the data, so proper date fields will end up as date field, integer to integer etc. And you can choose to what type of db flavour: firebird, mssql, mysql, oracle, postgresql, sqlite & sybase.

As example the below command analysis the csv file and imports it into postgresql:

csvsql --tabs --quoting 3 --no-doublequote --escapechar \\ --skipinitialspace --db postgresql+psycopg2://@localhost/[DATABASENAME] --insert --create-if-not-exists --verbose --overwrite [CSV FILENAME]

with bash you can create a loop over all csv files and execute the csvsql command.

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.