0

here am trying to import data from excel to mysql table, but am getting error like: not all arguments converted during string formatting

import xlrd
import MySQLdb,os,os.path,datetime as dt
os.chdir("file path")
filename = raw_input("Enter the file name: ",)
workbook = xlrd.open_workbook(filename)
sheet = workbook.sheet_by_index(0)
date = dt.datetime.today().strftime("%d/%m/%y")
fname = filename
user = 'UMD'
database = MySQLdb.connect (host="localhost", user = "root", passwd = "psswd", db = "MRKT_DATA")
cursor = database.cursor()
for r in range(0, sheet.nrows):
    NAME      = sheet.cell(r,0).value
    EMAIL_ID      = sheet.cell(r,1).value
    ALT_NUMBR      = sheet.cell(r,2).value
    DOB      = sheet.cell(r,3).value
    MOBILE      = sheet.cell(r,4).value
    FUNCTIONAL_AREA      = sheet.cell(r,5).value
    SPECIALIZATION      = sheet.cell(r,6).value
    INDUSTRY      = sheet.cell(r,7).value
    RESUME_TITLE      = sheet.cell(r,8).value
    KEYSKILL      = sheet.cell(r,9).value
    WORK_EXP      = sheet.cell(r,10).value
    CURNT_EMPLYR      = sheet.cell(r,11).value
    PREV_EMPLYR      = sheet.cell(r,12).value
    CURNT_SAL      = sheet.cell(r,13).value
    LEVEL      = sheet.cell(r,14).value
    CURNT_LOC      = sheet.cell(r,15).value
    PREF_LOC      = sheet.cell(r,16).value
    COURSE_PRI_HIGH_EDU      = sheet.cell(r,17).value
    SPLZTN_PRI_HIGH_EDU      = sheet.cell(r,18).value
    INST_PRI_HIGH_EDU      = sheet.cell(r,19).value
    COURSE_SEC_HIGH_EDU      = sheet.cell(r,20).value
    SPLZTN_SEC_HIGH_EDU       = sheet.cell(r,21).value
    INST_SEC_HIGH_EDU       = sheet.cell(r,22).value
    LAST      = sheet.cell(r,23).value
    ACTIVE      = sheet.cell(r,24).value
    DATE      = sheet.cell(r,25).value
    GENDER      = sheet.cell(r,26).value
    AGE      = sheet.cell(r,27).value 
    ADDRESS      = sheet.cell(r,28).value
    RESUME_ID      = sheet.cell(r,29).value
     query =  INSERT INTO DATA (NAME,EMAIL_ID,ALT_NUMBR,DOB,MOBILE,FUNCTIONAL_AREA,SPECIALIZATION,INDUSTRY,RESUME_TITLE,KEYSKILL,WORK_EXP,CURNT_EMPLYR,PREV_EMPLYR,CURNT_SAL,LEVEL,CURNT_LOC,PREF_LOC,COURSE_PRI_HIGH_EDU,SPLZTN_PRI_HIGH_EDU,INST_PRI_HIGH_EDU,COURSE_SEC_HIGH_EDU,SPLZTN_SEC_HIGH_EDU,INST_SEC_HIGH_EDU,LAST,ACTIVE,DATE,GENDER,AGE,ADDRESS,RESUME_ID,MARKET_TYPE,DATE_LOGGED,SOURCE) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"
    values = (NAME,EMAIL_ID,ALT_NUMBR,DOB,MOBILE,FUNCTIONAL_AREA,SPECIALIZATION,INDUSTRY,RESUME_TITLE,KEYSKILL,WORK_EXP,CURNT_EMPLYR,PREV_EMPLYR,CURNT_SAL,LEVEL,CURNT_LOC,PREF_LOC,COURSE_PRI_HIGH_EDU,SPLZTN_PRI_HIGH_EDU,INST_PRI_HIGH_EDU,COURSE_SEC_HIGH_EDU,SPLZTN_SEC_HIGH_EDU,INST_SEC_HIGH_EDU,LAST,ACTIVE,DATE,GENDER,AGE,ADDRESS,RESUME_ID,user,date,fname)
Cursor.execute(query, values)
cursor.close()
database.commit()
database.close()
print "no of rows : ", sheet.nrows
print ""
print " Done! "
print ""


Error:
     Traceback (most recent call last):
     File "Excel2Mysql.py", line 60, in <module>
     cursor.execute(query, values)
     File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 159, in execute
     query = query % db.literal(args)
     TypeError: not all arguments converted during string formatting

1 Answer 1

1

You have 33 items you want to insert, but only 32 places to insert them. Try the below code. (I added another %s)

 query =  INSERT INTO DATA (NAME,EMAIL_ID,ALT_NUMBR,DOB,MOBILE,FUNCTIONAL_AREA,SPECIALIZATION,INDUSTRY,RESUME_TITLE,KEYSKILL,WORK_EXP,CURNT_EMPLYR,PREV_EMPLYR,CURNT_SAL,LEVEL,CURNT_LOC,PREF_LOC,COURSE_PRI_HIGH_EDU,SPLZTN_PRI_HIGH_EDU,INST_PRI_HIGH_EDU,COURSE_SEC_HIGH_EDU,SPLZTN_SEC_HIGH_EDU,INST_SEC_HIGH_EDU,LAST,ACTIVE,DATE,GENDER,AGE,ADDRESS,RESUME_ID,MARKET_TYPE,DATE_LOGGED,SOURCE) VALUES ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');"
Sign up to request clarification or add additional context in comments.

6 Comments

yeah that worked fine but now am having new issue. mysql_exceptions.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Name'',''Email Id'',''Alternate Number'',''Date of Birth'',''Mobile No.'',''Func' at line 1") Any Idea..?
Not sure, I would try to manually run the insert against the database. Also make sure that all the column types are strings in the database. (I am assuming that sheet.cell(r,#).value always returns a string)
sheet.cell(r,#) always returns a string.
OK, then make sure FUNCTIONAL_AREA and the rest of the columns are strings (varchars) in the database.
name, email_id , alt_numbr , functional_area are defined as text in my table.I find error for these column's only. Is there anyway now to import data
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.