while inserting data to oracle from csv file using python getting error ORA-01722: invalid number

Question

I am trying to load data to oracle table. here is my previous question where i have posted whole code how I am doing that .

this is the code:

def Create_list():
reader = csv.reader(open("Query_result_combined.csv","r"))
lines=[]
print("Creating a list")
for line in reader:
    lines.append(line)
return lines


def Insert_data():
#connection logic goes here 
print("Connecting Now!!")
#conn = cx_Oracle.connect(connstr)
con = cx_Oracle.connect(db_user,db_password,db_connection_name)
print("Connected to Oracle!!")
lines=Create_list()
cur=con.cursor()
print("Inserting data")
for line in lines:
    cur.execute("INSERT INTO A608232_QUERY_RESULT (InteractionId,QueryId,Score,StartOffsetInMs,EndOffsetInMs,SpeakerRole,QueryIdentity,SpeakerId) VALUES(:1,:2,:3,:4,:5,:6,:7,:8)",line)
con.commit ()
cur.close()
print("completed")

So i rectified all aproches suggested in answer to my question so that error has been solved now i am getting this new error ORA-01722: invalid number.

when i loaded the data directly in to oracle using import option that data is getting loaded. When i am trying to read the same file in this code and trying to push the data i am getting this error.

I print lines[:1] and lines[:2] this is the output I get:

[['InteractionId', 'QueryId', 'Score', 'StartOffsetInMs', 'EndOffsetInMs', 
'SpeakerRole', 'QueryIdentity', 'SpeakerId']]
[['InteractionId', 'QueryId', 'Score', 'StartOffsetInMs', 'EndOffsetInMs', 
'SpeakerRole', 'QueryIdentity', 'SpeakerId'], ['34118470', '27', '45.63345', 
'89900', '90980', 'U', 'e54fd492-8877-4534-997b-9dbe9a8fbd74', '']]
 Inserting data

can someone please point out the mistake that i am doing in the code

Jean-François Fabre · Accepted Answer · 2017-08-02 20:40:31Z

1

You have a title line in your csv file, which is not part of the numerical data. You have to skip it using next(reader) just after mapping the csv.reader on the file handle.

Aside: use a context manager to ensure the file will be closed, and, no need for a loop, just convert the row iterator to a list (when the first line has been skipped) to read all the lines (will be faster)

def Create_list():
    with open("Query_result_combined.csv","r") as f:
       reader = csv.reader(f)
       next(reader)  # skips title line
       return list(lines)  # directly iterate on the rest of the lines

answered Aug 2, 2017 at 20:40

Jean-François Fabre♦

141k24 gold badges179 silver badges246 bronze badges

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

3 Comments

PriyalChaudhari Over a year ago

I didnt the part where you said for loop is not needed. ? How to do that exactly

Jean-François Fabre Over a year ago

list(lines) just creates the list of lines, like if you did a loop + append. But faster and shorter. I rewrote my version of your Create_list

PriyalChaudhari Over a year ago

Thanks the problem solved. I was reading my headers as well hence data was not inserted in to csv

Collectives™ on Stack Overflow

while inserting data to oracle from csv file using python getting error ORA-01722: invalid number

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related