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.
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"""
mydata = cursor.execute(delete)
cursor.execute('''CREATE TABLE "Python".ProjectConsolidated
print "Table created successfully"
query = """INSERT INTO "Python".ProjectConsolidated (DCAD_Prop_ID,
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,
print "All Done! Bye, for now."
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported Excel into postgreSQL"
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.