Reading and Storing csv data line by line in a postgres

I want to copy csv data from different files and then store in a table. But the problem is, the number of column differes in each csv files, So some csv file have 3 columns while some have 4. So if there are 4 columns in a file, I want to simply ignore the fourth column and save only first three.

Using following code, I can copy data into the table, if th开发者_如何转开发ere are only 3 columns,

CREATE TABLE ImportCSVTable (
          name varchar(100),
                  address varchar(100),
                  phone varchar(100));




COPY ImportCSVTable (name , address , phone)
         FROM 'path'
        WITH DELIMITER ';' CSV QUOTE '"';

But I am looking forward to check each row individually and then store it in the table.

Thank you.

Since you want to read and store it one line at a time, the Python csv module should make it easy to read the first 3 columns from your CSV file regardless of any extra columns.

You can construct an INSERT statement and execute it with your preferred Python-PostGreSQL module. I have used pyPgSQL in the past; don't know what's current now.

#!/usr/bin/env python
import csv
filesource = 'PeopleAndResources.csv'
with open(filesource, 'rb') as f:
    reader = csv.reader(f, delimiter=';', quotechar='"')
    for row in reader:
        statement = "INSERT INTO ImportCSVTable " + \
        "(name, address, phone) " + \
        "VALUES ('%s', '%s', '%s')" % (tuple(row[0:3]))
        #execute statement

Use a text utility to chop off the fourth column. That way, all your input files will have three columns. Some combination of awk, cut, and sed should take care of it for you, but it depends on what your columns look like.

You can also just make your input table have a fourth column that is nullable, then after the import drop the extra column.