Import data from Excel into MySQL using Python

Video is ready, Click Here to View ×


In this video I demonstrate how to create a Python script that will import data from Excel into MySQL using the xlrd library.

Blog article –

27 Comments

  1. Thanks, for putting this vid up. I learned a bunch. I'll look through your other vids to see how I can create and use a config file to hide the database properties as well as the path for the *.xls file.

  2. Grate video thanks a lot!! Few errors I ran into and solutions I found, for anyone else that does too. In order to read a date you have to convert it since excel saves dates as a float number, here is what I used to convert it https://stackoverflow.com/questions/32430679/how-to-read-dates-using-xlrd
    I also had to create my own table first and give it its values for the columns since the original example uses "INSERT TO orders"
    Executing a query that creates a table using "CREATE TABLE…" before the "INSERT" query.

  3. i am getting few errors in it
    like
    Traceback (most recent call last):
    File "C:UsersVinod sanipinaDesktopimport.py", line 4, in <module>
    book=xlrd.open_workbook('C:\Users\Vinod sanipina\Downloads\download.xls')
    File "C:UsersVinod sanipinaAppDataLocalProgramsPythonPython35libsite-packagesxlrd__init__.py", line 162, in open_workbook
    ragged_rows=ragged_rows,
    File "C:UsersVinod sanipinaAppDataLocalProgramsPythonPython35libsite-packagesxlrdbook.py", line 91, in open_workbook_xls
    biff_version = bk.getbof(XL_WORKBOOK_GLOBALS)
    File "C:UsersVinod sanipinaAppDataLocalProgramsPythonPython35libsite-packagesxlrdbook.py", line 1271, in getbof
    bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8])
    File "C:UsersVinod sanipinaAppDataLocalProgramsPythonPython35libsite-packagesxlrdbook.py", line 1265, in bof_error
    raise XLRDError('Unsupported format, or corrupt file: ' + msg)
    xlrd.biffh.XLRDError: Unsupported format, or corrupt file: Expected BOF record; found b'<html xm'

  4. Hi Michael, Thank you for the video but it's not working for me yet. I get an error for the c.execute(query, values') statement and I can't see the problem is. Any insights are greatly appreciated.

  5. how can i just read all the contents of an excel file without knowing the number of columns ?
    In the for loop above you created; you specify the number of columns … but what if I do not know the number of columns..
    I guess I should know because when creating the mySQL table I should specify what are the column names …

  6. Nice video, thx 😀

    I have a question nonetheless. I am kind of a newb, so please be patient :-).
    How would you import an excel to MySQL with a db that has more than one table?

    For e.g.: 
    1. I have the following tables: person, job, country
    2. person and job are many2many (1 person can have many jobs, and vice-versa)
    3. country is 1 to many (1 country can have many people in it)

    How can I run the queries to enter the excel row (person, job, country) while insuring the above stated relationships?

    Thank you so much for your help 🙂 and keep up the nice vids!

    Jonathan

  7. This is awesome, any way to search an entire folder for excel sheets, rather than naming a specific doc? I use cron to download new excel docs everyday, with random names, so I can really do it file by file. Any help would be much appreciated!

  8. Hi Michael,
    Found and teased out an explanation.
    1. Unzip anywhere, then copy the new folder into the Python folder, mine is C://Python27
    2. Open a cmd prompt and type cd/ to get back to C:>:
    3. Then cd/Python27/xlrd-0.8.0 Enter (if your Python is in the C:/ folder, most are).
    4. Then setup.py. build Enter
    5. Then setup.py install Enter
    That's it, plus the method should work for any add in for Python. Substiute your folder name for your version of Python. Please Pass ON. Thank you.

  9. Michael,
    Yes, but for what I can see there is no explanation on how to install.
    When unpacked from the zip file to a location on my desktop, I tried running 'setup.py',
    That bring up errors associated with __file__ not being named.
    I then give that a path value 'Python27' then ''Python27include" then "Python27includexlrd" +back slashes different ways.
    I can't find a clear method of how to install please help
    I also copied the downloaded xlrd files to where Python27 is on my computer.

  10. Fantastic expanation Michael,
    I love your calm manner, dignified too.

    I have been hoping to try the excel to mysql out but can't get xlrd to be available to python.
    I've tried all the google searches and tried their suggestion but still it wont import xlrd
    Could you help, perhap list how to install or compile the download into python,

    thank you
    Victor

Leave a Reply

Your email address will not be published.


*