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 _______________________________________________ python-win32 mailing list python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32