Tim, thanks for your response. I've got 7 excel files that need reading containing a total of ~6100 rows. I agree, about this code making me sick :)
In a perfect world I would get all the data into our SQL server and write a front end for everyone that needs to access/modify the data, but that's a ways down the road. I've still got to convince people in the company that excel is NOT a good way to store database info! I don't have a problem extracting all the data and throwing it into sqlite, but the problem is I would need to extract all the data every time I wanted to run my query. I've also got some code laying around to COM into excel and read data, but it's not very pretty either. Now that I think of it, a good temporary solution might be to have an excel macro that runs whenever the file is open, then whenever someone saves changes to the excel file, it would dump all that data into our SQL server. Then whenever I get around to making that frontend, the data will already be in SQL. Thanks for your help. -Kyle Rickey -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tim Golden Sent: Friday, March 14, 2008 9:56 AM Cc: python-win32@python.org Subject: Re: [python-win32] Running SQL queries on excel sheets Rickey, Kyle W wrote: > 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. [... snip code ...] <unhelpful stuff> To be frank, they both make me say "Uuughh" (which I think translates into American as "Eeewww"). I did try access Excel via SQL some years ago, but gave up in disgust. It just seemed to be a half-hearted addon to prove the power of ODBC in its early days by connecting to a system which wasn't really designed to support it. But maybe it's improved. </unhelpful stuff> <more helpful> Couple of things worth bearing in mind. The odbc module from pywin32 is, I think, effectively unsupported. There are several more modern and maintained alternatives. The option "par excellence" is the mxODBC module, but it's commercially licensed only, which may or may not be a problem. The other two are pyodbc and ceodbc. I've heard good things of both: the sqlalchemy guys use pyodbc as their preferred connection to SQL Server. Secondly, there is an adodbapi module which wraps the messy ADO plumbing into a Python dbapi-compliant module. It was unmaintained for a long while but has recently been picked up again (Vernon Cole?) and is due to be included in the next pywin32 release, I think. Obviously, all that is only helpful up to a point. Is there some reason why you can't just pull the data out straight into Python structures and take it from there (or even push it into a Sqlite memory database)? Perhaps you've got absolutely tons of data so extracting it would be a pain? If not, consider using one of the COM packages (win32com.client or comtypes) or something like pyExcelerator to pull it out. </more helpful> TJG _______________________________________________ python-win32 mailing list python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32 _______________________________________________ python-win32 mailing list python-win32@python.org http://mail.python.org/mailman/listinfo/python-win32
