Excel to PostgreSQL using Python

Question

I'm new to Python and I'm trying to export excel directly into postgreSQL without CSV files.

I don't know if it's possible.

I keep running into the error 'column "daily_date" is of type date but expression is of type numeric'.

import psycopg2
import xlrd

book = xlrd.open_workbook("pytest.xlsx")
sheet = book.sheet_by_name("Source")

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

cursor = database.cursor()

query = """INSERT INTO orders (Daily_Date, Days, First, Second, Leader) VALUES (%s, %s, %s, %s, %s)"""

for r in range(1, sheet.nrows):
    Daily_Date = sheet.cell(r,0).value
    Days = sheet.cell(r,1).value
    First = sheet.cell(r,2).value
    Second = sheet.cell(r,3).value
    Leader = sheet.cell(r,4).value

    values = (Daily_Date, Days, First, Second, Leader)

    cursor.execute(query, values)

cursor.close()

database.commit()

database.close()

print ""
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported Excel into postgreSQL"

formatting improved

SashaZd
– SashaZd

2016-08-17 21:04:58 +00:00
Commented Aug 17, 2016 at 21:04 — SashaZd
– SashaZd, Commented Aug 17, 2016 at 21:04

mechanical_meat · Accepted Answer · 2016-08-17 20:05:49Z

2

Excel stores datetimes as numbers. You can use xlrd.xldate.xldate_as_datetime to convert:

    Daily_Date = xlrd.xldate.xldate_as_datetime(sheet.cell(r,0).value,book.datemode)

answered Aug 17, 2016 at 20:05

mechanical_meat

170k25 gold badges237 silver badges231 bronze badges

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

Collectives™ on Stack Overflow

Excel to PostgreSQL using Python

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related