Reputation: 63
CSV date column is in following format 'MM/DD/YYYY' while loading the data into db Iam seeing following error: Oracle-Error-Code: 1843 Oracle-Error-Message: ORA-01843: not a valid month
Code:
csv_input=pd.read_csv(r"C:\python\test.csv",index_col=False,na_values=" ").fillna('')
try:
conn = orcCon.connect('scott/tiger@localhost:1521/orcl',encoding="UTF-8")
if conn:
print("cx_Oracle version:", orcCon.version)
print("Database version:", conn.version)
print("Client version:", orcCon.clientversion())
# Now execute the sqlquery
cursor = conn.cursor()
print("You're connected.................")
print("TRUNCATING THE TARGET TABLE")
cursor.execute("TRUNCATE TABLE TEST1")
print("Inserting data into table")
for i,row in csv_input.iterrows():
sql = "INSERT INTO TEST1(SAMPLE1,SAMPLE2)VALUES(TO_DATE(:1,'MM/DD/YYYY'),:2)"
cursor.execute(sql, tuple(row))
# the connection is not autocommitted by default, so we must commit to save our changes
conn.commit()
#print("Record inserted successfullly")
except DatabaseError as e:
err, = e.args
print("Oracle-Error-Code:", err.code)
print("Oracle-Error-Message:", err.message)
finally:
cursor.close()
conn.close()
CSV is in following format:
Sample1 Sample2
11/23/2022 abc
11/23/2022 bcd
Upvotes: 0
Views: 302
Reputation: 21170
You have only one problem, but is is serious. You use read_csv
but your input data is not a csv
file.
t="""Sample1 Sample2
11/23/2022 abc
11/23/2022 bcd"""
csv_input = pd.read_csv(io.StringIO(t))
for i,row in csv_input.iterrows():
print(f'reading: {tuple(row)}')
reading: ('11/23/2022 abc',)
reading: ('11/23/2022 bcd',)
So you are reading a tuple with one value which leads to the conversion problem.
You have to use read_fwf
- for fixed width files
fwf_input = pd.read_fwf(io.StringIO(t))
for i,row in fwf_input.iterrows():
print(f'reading: {tuple(row)}')
reading: ('11/23/2022', 'abc')
reading: ('11/23/2022', 'bcd')
Upvotes: 1