Import Data from Excel to Mysql Python

Question

This code work well for String but the float column is not the same in Database, I don't understand how it is working, For Example in Excel file the value "215,325" in database "254.0835" and there is many other value changed.

import MySQLdb
import xlrd

list= xlrd.open_workbook("prod.xls")
sheet= list.sheet_by_index(0)

database = MySQLdb.connect (host="localhost" , user="root" , passwd="" ,db="table")
cursor = database.cursor()

query= """INSERT INTO produits (idProduit, idCategorie, LibelleProduit, PrixProduit) VALUES (%s, %s, %s, %s)"""



for r in range(1,sheet.nrows):
    idProduit = sheet.cell(r,0).value
    categorie = 999
    libelle=sheet.cell(r,1).value
    prix=sheet.cell(r,3).value #>>>>> HERE THE PROBLEM the Imported Value <<<<


    values = (idProduit,categorie,libelle,prix)

    cursor.execute(query,values)



cursor.close();
database.commit()

database.close()

print""
print "All done !"

columns= str(sheet.ncols)
rows=str(sheet.nrows)
print "i just import "+columns+" columns and " +rows+ " rows to MySQL DB"

also ,i tried to change the SQL Type to Varchar it was changed also.

Try print prix value where the problem is occurring. Meaning before inserting it into DB. What is the value then? Try narrowing down the issue whether the issue is occurring while reading the excel sheet or inserting into mysql? — planet260
– planet260, Commented Feb 16, 2015 at 11:50
he is changed before inserting into DB . so the problem when i read the value from excel . — Elyes
– Elyes, Commented Feb 16, 2015 at 11:52
I have posted my answer about cleaning the data before inserting. See if it helps. — planet260
– planet260, Commented Feb 16, 2015 at 11:58

planet260 · Accepted Answer · 2015-02-16 12:50:49Z

1

The issue is occurring while reading data from Excel. If you know its float data then you can clean it before inserting it into MySQL.

import re
prix=sheet.cell(r,3).value
prix = str(prix)
prix = re.sub('[^0-9.]+', '', prix )
print float(prix)

This way only numbers and . will be kept all other junk will be discarded.

import MySQLdb
import re
database = MySQLdb.connect (host="localhost" , user="test" , passwd="" ,db="test")
cursor = database.cursor()
query= """INSERT INTO test (id, num) VALUES (%s, %s)"""

prix = "215,325"
prix = str(prix)
prix = re.sub('[^0-9.]+', '', prix )
prix = float(prix)
values = (23,prix)

cursor.execute(query,values)
cursor.close();
database.commit()
database.close()
print "Done"

edited Feb 16, 2015 at 12:50

answered Feb 16, 2015 at 11:57

planet260

1,5041 gold badge16 silver badges32 bronze badges

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

4 Comments

Elyes Over a year ago

same problem , well " prix=sheet.cell(r,3).value " return FLOAT , when i try ur code i got TypeError: expected string or buffer ;so i do prix = str(prix) then ur code work good , but the same result

planet260 Over a year ago

I am unable to track down what is the issue exactly. I just ran a test it worked fine for me. i inserted a bunch of different cases. Can you please look at my code and see if you are skipping anything.

planet260 Over a year ago

You problem lies when reading the data from Excel. Maybe do a deep dive into xlrd.

Elyes Over a year ago

Yes ur code work fine cause you are in use of a static int , but the problem is the value getted from Excel by xlrd ! how this xlrd convert this float :( anyway thank you man ^^ i will look in xlrd documentation , i'm sure there is something hidden

Collectives™ on Stack Overflow

Import Data from Excel to Mysql Python

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related