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
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.