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?

• Vivek S.: You can use COPY to do the job, see this
• Vivek S.: This is what you need to do
• saniojoseph: thanks, does this copy command will populate the table from csv file???
• user464502: You're going to have to write a script that reads in the csv files and writes out create table statements.
• saniojoseph: 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

Answer 1

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.