I have always wondered how easy or difficult it is to import an excel document to MySQL using Python. Well after doing some research and code modifications I have finally managed to import an excel document to MySQL using Python’s xlrd library
Following are the steps I have followed in order to import the document
# Open the workbook and define the worksheet
# Establish a MySQL connection
# Get the cursor, which is used to traverse the database, line by line
# Create the INSERT INTO sql query
# Create a For loop to iterate through each row in the XLS file, starting at row 2 to skip the header
# Assign values from each row
# Execute SQL Query
# Close the Cursor
# Commit the transaction
# Close the database connection
# Print results
Code:
1. #Create a Excel document with data that provides a summary of the information about the project managers, clients and the projects they have worked on along with the status of the project whether it is closed or opened. The beginning date of the project and project code
Column Headers – Job Code, Date, Client, Description, Status, Project Manager
Save the excel file as “.xls”
2. Create Columns in MySQL
3. Code to import the data into MySQL.
4. Use any code editor and save the file as importExcel.py
Code
import xlrd
import MySQLdb
# Open the workbook and define the worksheet
book = xlrd.open_workbook(“importExcel.xls”)
sheet = book.sheet_by_name(“Client_Data”)
//sheet = book.sheet_by_index(0)
# 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 order (job_code, date, client, description, status, project_manager) VALUES (%s, %s, %s, %s, %s,%s)”””
# Create a for loop to iterate through each row in the xls file, starting from row 2
for r in range(1, sheet.nrows):
product = sheet.cell(r,0).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(r,5).value
open_opportunities = sheet(r,6).value
# Assign values from each row
values = (job_code, date, client, description, status, project_manager)
# 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 “Data Imported successfully!!!”
print “”
columns = str(sheet.ncols)
rows = str(sheet.nrows)
print “Summary of data imported: “ + columns + “ columns and “ + rows + ” rows”
Hope this helps in importing your spreadsheets 🙂
Planet Santosh 