HomeХобби и стильRelated VideosMore From: Michael Herman

Import data from Excel into MySQL using Python

363 ratings | 54752 views
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 - http://mherman.org/blog/2012/09/30/import-data-from-excel-into-mysql-using-python/
Html code for embedding videos on your blog
Text Comments (36)
Mahmoud asy (22 days ago)
thank's yor example is simple and very powerful
Emery Premeaux (3 months ago)
Thank you for the easy to follow video. This worked perfectly for me.
Tariq Hassan (7 months ago)
Please share the video how to create a db? and the 2nd video. thanks
Haritha Jayagopi (7 months ago)
Thank you so much! Very helpful for my uni project
Cadence Kuan (8 months ago)
Thankyoy Michael!! But I have a question which is the 'date'. How can change the 'date code' to correct 'date'?
Varun Vittal Mahale (8 months ago)
hey the dates are showing in number format in your video after you have executed it.
Daniel Ale (1 year ago)
You just saved me lots of headache. Thanks
Nitin Upadhyay (1 year ago)
Stan Corpuz (1 year ago)
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.
Julio Gonzalez (1 year ago)
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.
sai abhishek (1 year ago)
i am getting few errors in it like Traceback (most recent call last): File "C:\Users\Vinod sanipina\Desktop\import.py", line 4, in <module> book=xlrd.open_workbook('C:\\Users\\Vinod sanipina\\Downloads\\download.xls') File "C:\Users\Vinod sanipina\AppData\Local\Programs\Python\Python35\lib\site-packages\xlrd\__init__.py", line 162, in open_workbook ragged_rows=ragged_rows, File "C:\Users\Vinod sanipina\AppData\Local\Programs\Python\Python35\lib\site-packages\xlrd\book.py", line 91, in open_workbook_xls biff_version = bk.getbof(XL_WORKBOOK_GLOBALS) File "C:\Users\Vinod sanipina\AppData\Local\Programs\Python\Python35\lib\site-packages\xlrd\book.py", line 1271, in getbof bof_error('Expected BOF record; found %r' % self.mem[savpos:savpos+8]) File "C:\Users\Vinod sanipina\AppData\Local\Programs\Python\Python35\lib\site-packages\xlrd\book.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'
sai abhishek (1 year ago)
Michael Herman (1 year ago)
What's the filename?
Sharan Kumar Vaddadi (2 years ago)
Sir iam getting following error please help me cursor.execute(query, values) line 179 error
Anna Crusio (2 years ago)
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.
john smith (2 years ago)
Great tutorial Is it possible to output the number of imported rows and columns to say a text file ?
Ryderr (2 years ago)
Great vid :) Really helped me.
Simona Sopagaite (3 years ago)
Thanks Michael for this great tutorial was really helpful. I think it's only one so far as i was looking for awhile . It's just to unzip/ install xlrd wasn't so simple, had to look for other tutorials
GateCrashers (3 years ago)
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 ...
Michael Herman (3 years ago)
+nick187able use `ncols` -> https://pythonhosted.org/xlrd3/sheet.html#Sheet.ncols
tjthigpen2 (3 years ago)
great video instructional video.  Took me about two day to get it to work but I wouldn't have had any idea without your help.  Thanks A lot!!!
Quan N (3 years ago)
Does this work for sqlite3?
Michael Herman (3 years ago)
+Tho Scho it does
Tho Scho (3 years ago)
+Michael Herman Thanks, but i think the win32com does not work with python2.7 :/
Michael Herman (3 years ago)
+Tho Scho http://www.saltycrane.com/blog/2007/11/migrating-excel-to-sqlite-using-python/
Tho Scho (3 years ago)
+Quan N Hey Quan, do you know how to import Excel into a sqlite ?
Jonathan Sitruk (4 years ago)
Nice video, thx :-D 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
Zak Weinberg (5 years ago)
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!
Michael Herman (5 years ago)
yes, search for "MySQL for Excel". there is a program directly from oracle that you can use.
Michael Herman (6 years ago)
working directory*
Michael Herman (6 years ago)
I use easy_install myself. I'll throw up a video of easy_install today or tomorrow.
Michael Herman (6 years ago)
exactly- 1) download and unzip 2) Open Command Prompt 3) CD to the working 4) Run command - python setup.py install
Charles Haughey (6 years ago)
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.
Charles Haughey (6 years ago)
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 ''Python27\include" then "Python27\include\xlrd" +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.
Michael Herman (6 years ago)
Thank you. Have you tried python-excel (dot) org ?
Charles Haughey (6 years ago)
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

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.