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, Aug 10 '17 at 08:22
Refer this links .. Might be helpful to you... (1) https://stackoverflow.com/questions/2987433/how-to-import-csv-file-data-into-a-postgresql-table (2)https://stackoverflow.com/questions/19400173/how-should-i-import-data-from-csv-into-a-postgres-table-using-pgadmin-3 — Keval Mehta, Aug 10 '17 at 09: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, Aug 10 '17 at 10:08

score 0 · Answer 1 · answered Aug 10 '17 at 08:32

0

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 '17 at 08:32

Anuraag Veerapaneni

521
2
13

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 Aug 10 '17 at 10:21
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---- – Neepa Aug 11 '17 at 11:31

insert from CSV file to PostgreSQL table with integer values type

1 Answers1