CODEWITHSUNDEEP
pythonoracle-databasecx-oracle
nikhilesh kumar
nikhilesh kumar

Reputation: 63

ORA-01843: not a valid month error while loading data from csv to oracle db using python

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

Answers (1)

Marmite Bomber
Marmite Bomber

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

Related Questions