PLearner PLearner - 1 month ago 10
Python Question

N/A in integer field

I am importing Excel into postgreSQL using Python. Below is the field I am having problem with. Actual Sales Price had been an integer data type value which for a while but now this column contains an N/A value which is blowing up my Python script. Is there anything I can add to this script which will tell it to bring in N/A without changing the data type to varchar.

import psycopg2
import xlrd
book = xlrd.open_workbook("T:\DataDump\8888.xlsx")
sheet = book.sheet_by_name("ProjectConsolidated")
database = psycopg2.connect (database = "***", user="****")
cursor = database.cursor()
delete = """drop table if exists "Python".ProjectConsolidated"""
print (delete)
mydata = cursor.execute(delete)

cursor.execute('''CREATE TABLE "Python".ProjectConsolidated
(DCAD_Prop_ID VARCHAR(25),
Actual_Close_Date date,
Actual_Sales_Price integer,
);''')
print "Table created successfully"

query = """INSERT INTO "Python".ProjectConsolidated (DCAD_Prop_ID,
Actual_Close_Date, Actual_Sales_Price)
VALUES (%s, %s, %s)"""
for r in range(1, sheet.nrows):
DCAD_Prop_ID = sheet.cell(r,0).value
Actual_Close_Date = None if not sheet.cell(r,1).value else xlrd.xldate.xldate_as_datetime(sheet.cell(r,1).value,book.datemode)
Actual_Sales_Price = None if not sheet.cell(r,2).value else sheet.cell(r,2).value
values = (DCAD_Prop_ID,
Actual_Close_Date, Actual_Sales_Price)
cursor.execute(query, values)
cursor.close()
database.commit()
database.close()
print ""
print "All Done! Bye, for now."
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported Excel into postgreSQL"

Answer
Actual_Sales_Price = None if not sheet.cell(r,61).value else sheet.cell(r,61).value

try:
    float(Actual_Sales_Price)
except (ValueError, TypeError):
    Actual_Sales_Price = None

If python fails to convert your actual sales price into a float (presumably, because it's not a number), we change the ASP to Null.

Your DB driver should know how to translate a python None into Postgres.

Whether you actually want the Actual Sales Price to be Null in your DB is up to you, although it sounds wrong from the limited info provided.

Comments