insert from CSV file to PostgreSQL table with integer values type

Question

  I want to insert data from a CSV file into a PostgreSQL table. The table

structure is given below. But I am unable to give input of INTEGER type values. It is showing error like- DataError: invalid input syntax for integer: "vendor_phone" LINE 1: ...vendor_phone,vendor_address)VALUES ('vendor_name','vendor_ph...

It is working fine if I use VARCHAR type. But i need to use integer values.

   CREATE TABLE vendors (
        vendor_id SERIAL PRIMARY KEY,
        vendor_name VARCHAR(100) NOT NULL,
        vendor_phone INTEGER,
        vendor_address VARCHAR(255) NOT NULL 
    )

    import psycopg2
    import csv

    database = psycopg2.connect (database = "supplier", user="postgres", password="1234", host="localhost", port="5432")

cursor = database.cursor()

    vendor_data = csv.reader(open('vendors.csv'),delimiter=',')

    for row in vendor_data:

        cursor.execute("INSERT INTO vendors (vendor_name,vendor_phone,vendor_address)"\
            "VALUES (%s,%s,%s)",
           row)

    print("CSV data imported")

    cursor.close()
    database.commit()
    database.close()

Look at the error message carefully, it says you're inserting the word "vendor_phone" which clearly isn't an integer. It sounds like you have a header row which you need to skip. — IMSoP
– IMSoP, Commented Aug 10, 2017 at 8:22
Refer this links .. Might be helpful to you... (1) stackoverflow.com/questions/2987433/… (2)stackoverflow.com/questions/19400173/… — Keval Mehta
– Keval Mehta, Commented Aug 10, 2017 at 9:34
I need to give input of a integer value....Further I have some porthin of my code-- header = next(vendor_data) rows = [header] + [[row[0], int(row[1]), row[2]] for row in vendor_data] for row in rows: cursor.execute("INSERT INTO vendors (vendor_name,vendor_phone,vendor_address)"\ "VALUES (%s,%??,%s)", row) — Neepa
– Neepa, Commented Aug 10, 2017 at 10:08

Anuraag Veerapaneni · Accepted Answer · 2017-08-10 08:32:01Z

1

instead of cursor, you can use below statement to load data directly from CSV to table which skips Header of the CSV file

COPY vendors (vendor_name,vendor_phone,vendor_address) FROM 'vendors.csv' CSV HEADER;

answered Aug 10, 2017 at 8:32

Anuraag Veerapaneni

6795 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Neepa Over a year ago

I need to insert an integer value ....Further I have edited some portion of my code-- header = next(vendor_data) rows = [header] + [[row[0], int(row[1]), row[2]] for row in vendor_data] for row in rows: cursor.execute("INSERT INTO vendors (vendor_name,vendor_phone,vendor_address)"\ "VALUES (%s,%d,%s)", row) ----it is giving error -ValueError: unsupported format character 'd' (0x64) at index %Id

Neepa Over a year ago

From your suggestion I have tried the copy command- cursor.execute("COPY vendor_parts (vendor_id,part_book_id,time_id,total_sale) FROM 'D:\vendor_parts.csv' CSV HEADER;") ----- This query is running in shell prompt, but not running in my python code.Showing error SyntaxError: EOL while scanning string literal----

Collectives™ on Stack Overflow

insert from CSV file to PostgreSQL table with integer values type

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related