[python-win32] Running SQL queries on excel sheets

[Rickey, Kyle W]

I've got several excel sheets I would like to run SQL queries on and
I've worked out a couple of ways, but I'm not sure what the best way
would be. I've also got a problem where I can't connect to the excel
file if someone is currently editing it. Given the following to code
samples, which one is better? So I guess I'm asking if an ODBC or and
ADO connection is better.

import odbc, win32com.client
file = "C:\\file.xls"
search_term = "test"

##EXAMPLE 1##
conn_string = "Driver={Microsoft Excel Driver
(*.xls)};Dbq="+file+";READONLY=true;IMEX=1;"
conn = odbc.odbc(conn_string)
cur = conn.cursor()

cur.execute(""""SELECT F5, F6, F7, F1 FROM
[Excel 8.0;HDR=YES;IMEX=1;Database="+file+";].[Sheet1$B:H]
WHERE LCASE(F6) LIKE '%"+search_term.lower()+"%'"""")

rec = cur.fetchall()
##EXAMPLE 1##

##EXAMPLE 2##
try:
	conn = win32com.client.Dispatch("ADODB.Connection")
	DSN = r""""PROVIDER=Microsoft.Jet.OLEDB.4.0;
DATA SOURCE="+file+";
Extended Properties='Excel 8.0;
READONLY=true;
IMEX=1';"""
	conn.Open(DSN)
	
except win32com.client.pywintypes.com_error:#Someone editing file
	file2 = "C:\\temp.xls"
	if os.path.exists(file2):
		os.remove(file2)
	shutil.copy2(file, file2)#Copy file to C:\\ and read it from
there
	file = file2
	
	conn = win32com.client.Dispatch("ADODB.Connection")
	DSN = r"""PROVIDER=Microsoft.Jet.OLEDB.4.0;
DATA SOURCE="+file+";
Extended Properties='Excel 8.0;
READONLY=true;
IMEX=1';"""
	conn.Open(DSN)
	
else:
	rec = []
	rs = win32com.client.Dispatch("ADODB.Recordset")
	rs_name = "MyRecordset"
	rs.Open("""SELECT * FROM
[Excel 8.0;
HDR=YES;
IMEX=1;
Database="+file+";].[Sheet1$B:H]
WHERE LCASE(F6) LIKE '%"""+search_term.lower()+"""%'""", conn)
	while not rs.EOF:
		lst = []
		lst.append(rs.Fields.Item("F5").Value)
		lst.append(rs.Fields.Item("F6").Value)
		lst.append(rs.Fields.Item("F7").Value)
		lst.append(rs.Fields.Item("F1").Value)
		rec.append(lst)
		rs.MoveNext()
##EXAMPLE 2##

-Kyle Rickey