[python-win32] Running SQL queries on excel sheets

[Tim Golden]

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