I recently had to write a python script that was unzipping a an archive containing several excel files and then load the data of a specific sheet into a MySQL 5 database. Doing it with only SQL was not an option since it required some manual actions (saving the sheet as csv, which resulted in the loss the data from formulas including other sheets, corrected by copying/pasting the data beforehand and columns containing commas or other type of char).

I used xlrd as the API to read the excel files and the zipfile module to unzip.

My first thought was to parse each line and insert the whole file in a single really big INSERT. Needless to say it was really slow.
I then thought of converting the xls files to csv files and then use the LOAD INTO syntax from MySQL, which is quite fast (~3s for 30k lines).

I found a script doing close to what I wanted here so I took the core and tweaked it a bit : choose a sheet, a delimiter and remove unwanted characters.

For the example, let’s say we have 2 tables (table1 and table2) to load into (indentation are whitespace, not tab) :

#!/usr/bin/env python
# -*- coding: utf-8 -*-

import xlrd
import csv
import MySQLdb
import os
import re
import zipfile

#Gets and formats the data from the sheet
def sheet_to_dictionary(excel_file, sheet_number):
   book = xlrd.open_workbook(excel_file)
   formatter = lambda(t,v): format_excel_value(book,t,v)
   raw_sheet = book.sheet_by_index(sheet_number)
   data = []

   for row in range(raw_sheet.nrows):
      (types, values) = (raw_sheet.row_types(row), raw_sheet.row_values(row))
      data.append(map(formatter, zip(types, values)))

   return ({ 'sheet_data': data })

#Format Excel date to isodate
def exceldate_to_isodate(datetuple):
   (y,m,d, hh,mm,ss) = datetuple
   nonzero = lambda n: n!=0
   date = "%04d-%02d-%02d" % (y,m,d) if filter(nonzero,(y,m,d)) else ''
   return date

#Clean the excel formatting
def format_excel_value(book, type, value):
   if type == 2: # NUMBER
      if value == int(value): value = int(value)
   elif type == 3: # DATE
      datetuple = xlrd.xldate_as_tuple(value, book.datemode)
      value = exceldate_to_isodate(datetuple)
   elif type == 5: # ERROR
      value = xlrd.error_text_from_code[value]
   elif type == 1 and '\n' in value: #There was some \n characters in my files that i needed to remove
      value = value.replace('\n',' ')
   return value

#UTF-8 the strings
def utf8ize(l):
   return [unicode(s).encode("utf-8") if hasattr(s,'encode') else s for s in l]

#Creates the CSV
def create_csv(table, file_path, delimit):
   file = open(file_path, 'wb')
   csvout = csv.writer(file, delimiter=delimit, doublequote=False, escapechar='\\')
   csvout.writerows(map(utf8ize, table))

#Load the table1 CSV into the table1 table
def load_table1(file_path):
   sql = """LOAD DATA LOCAL INFILE '%s'
   INTO TABLE table1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES
   (col1, col2, col3)
   """ % (file_path)
   execute_sql(sql)

#Load the table2 CSV into the table2 table
def load_table2(file_path):
#Using ';' as a delimiter since there are ',' in the rows
   sql = """LOAD DATA LOCAL INFILE '%s'
   INTO TABLE table2 FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES
   (col1, col2, col3)
   """ % (file_path)
   execute_sql(sql)

def execute_sql(sql):
   connection = MySQLdb.connect(host = "127.0.0.1", user = "root", passwd = "password", db = "test")
   cursor = connection.cursor()
   cursor.execute(sql)
   cursor.close
   connection.commit()
   connection.close

#Unzip the archive containing the excel files
re_zip = re.compile(r'(\.zip)$')
zip_filename = filter(re_zip.search, os.listdir(".")) #Using the script directory, but you could easily change that

#Should have only one zip file in the directory
z = zipfile.ZipFile(zip_filename[0])
names = z.namelist()
z.extractall()
z.close()

#Loads the xls data into the database and removes the files when we're done with them
for filename in names:
   if "table1" in filename:
      csv_name = 'table1.csv'
      table = sheet_to_dictionary(filename, 3)
      create_csv(table['sheet_data'], csv_name, ',')
      load_non_mandate(csv_name)
      os.remove(filename)
      os.remove(csv_name)
   if "table2" in filename:
      csv_name = 'table2.csv'
      table = sheet_to_dictionary(filename, 9)
      create_csv(table['sheet_data'], csv_name, ';') #There was some commas in the columns, preventing me to use a basic CSV
      load_time_reporting(csv_name)
      os.remove(filename)
      os.remove(csv_name)

os.remove(zip_filename[0])
#When the script is finished, data is loaded in the tables and all the files, including the zip are deleted, you could also
#archive them if you want