I just finished a basic Python script for a client that I’d like to share with you. He needed an easy means of moving data back and forth between MySQL and Excel, and sometimes he needed to do a bit of manipulation between along the way. In the past I may have relied solely on VBA for this, but I have found it to be much easier with Python. In this post and the accompanying video, I show just part of the project - importing data from Excel into MySQL via Python. Let’s get started.

Be sure to check out the accompanying video!

Download the dependencies

Assuming you have Python installed (I’m using version 2.7), download and install the xlrd library and MySQLdb module-

Develop the script

Then tailor the following script to fit your needs:

import xlrd
import MySQLdb

# Open the workbook and define the worksheet
book = xlrd.open_workbook("pytest.xls")
sheet = book.sheet_by_name("source")

# Establish a MySQL connection
database = MySQLdb.connect (host="localhost", user = "root", passwd = "", db = "mysqlPython")

# Get the cursor, which is used to traverse the database, line by line
cursor = database.cursor()

# Create the INSERT INTO sql query
query = """INSERT INTO orders (product, customer_type, rep, date, actual, expected, open_opportunities, closed_opportunities, city, state, zip, population, region) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""

# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the headers
for r in range(1, sheet.nrows):
		product		= sheet.cell(r,).value
		customer	= sheet.cell(r,1).value
		rep			= sheet.cell(r,2).value
		date		= sheet.cell(r,3).value
		actual		= sheet.cell(r,4).value
		expected	= sheet.cell(r,5).value
		open		= sheet.cell(r,6).value
		closed		= sheet.cell(r,7).value
		city		= sheet.cell(r,8).value
		state		= sheet.cell(r,9).value
		zip			= sheet.cell(r,10).value
		pop			= sheet.cell(r,11).value
		region	= sheet.cell(r,12).value

		# Assign values from each row
		values = (product, customer, rep, date, actual, expected, open, closed, city, state, zip, pop, region)

		# Execute sql Query
		cursor.execute(query, values)

# Close the cursor
cursor.close()

# Commit the transaction
database.commit()

# Close the database connection
database.close()

# Print results
print ""
print "All Done! Bye, for now."
print ""
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print "I just imported " %2B columns %2B " columns and " %2B rows %2B " rows to MySQL!"

Hope this is useful. More to come!