Import data from Excel into MySQL using Python

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/im...

---

Closed Caption:

Today I’m going to show you how to import data from Excel into MySQL using Python.
Start by installing the xlrd library, which is used for extracting data from Excel spreadsheets.
Keep in mind, that xlrd only supports the classic XLS file
not the XML-format XLSX file.
You also need to install the MySQLdb module in order to connect to the database.
Both are easy to install.
Links to both of their download pages are available on my blog
which you can find a link to in the description of this video.
After installing xlrd and MySQLdb, create the table that you’d like to use in your database if it’s not already created.
My table looks like this.
The primary key is an auto-incremented id. The other 13 columns are a mixture of integers and strings
which will be populated by the Excel data.
Now to create the Python code, I like to start with writing out pseudo code, in plain English
detailing what it is that I’d like the code to do.
Then I turn the pseudo code into comments and write the actual code.
For time’s sake, I already wrote out the pseudo code and commented it out, which you can see here.
And the code is written as well, so I will just be copying and pasting it over, explaining each part.
Start by importing xlrd and MySQLdb to make them available.
To open the workbook, set a variable, book, and set it equal to xlrd.open_wordbook
and then enter the path to and file name of your XLS file.
Then to access a sheet
you can either use its location in the Excel file with the sheet_by_index method
in the Excel file with the sheet_by_index method
or by the sheet’s name with the sheet_by_name method.
Next you need to establish a MySQL connection.
To do that set a variable, database, equals MySQLdb.connect.
Enter your full host name, username, password, and then specify the database.
There are several other parameters that can be used as well like port if it's different than the default MySQL port.
You can also set the path to configuration file using read_default_file
so you don’t have to expose your username or password in the code.
Next you want to get the cursor.
Basically that's used to read the database, line by line, so that you can perform a query.
And so you just want to set a variable, cursor, equals database.cursor().
Then you want write out the INSERT INTO SQL statement .
Alright now you need to create a FOR loop.
Which will iterate through each row in the XLS file.
This creates an array called r.
And then you establish the cells here that you want to use for the iteration
Now you want to assign values to match the placeholders.
And this is placed in the loop as each row in the Excel file is different.
Then you want to execute the SQL query.
Enter cursor.execute
then enter the variable names for the query
and assigned values.
Now exit the loop
and close the cursor.
cursor.close closes the previously opened cursor. Make sure to do this before you close the database connection
so you can still commit the data manipulations to the database
using database.commit
Finally, close the the connection to the database .
And then print results.
Here I'm just saying goodbye.
And then I have variables that will calculate the number of columns and the number of rows in the Excel file .
Now let’s test this out.
I’m going to open up the MySQL console.
So you can see what data is already in the table.
So it's empty.
Let's run the file now.
Open up Command Prompt.
CD on over to where the newly created Python file is, and then just run the file.
And I just imported 13 columns and 3160 rows of data from Excel to MySQL.
Let’s just verify that the table is now populated with data.
Which it is.
Alright more fun stuff with MySQL and Python coming soon.
Thanks for watching.