[python-win32] Running SQL queries on excel sheets

[Rickey, Kyle W]

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: python-win32-bounces at python.org
[mailto:python-win32-bounces at python.org] On Behalf Of Tim Golden
Sent: Friday, March 14, 2008 9:56 AM
Cc: python-win32 at 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 at python.org
http://mail.python.org/mailman/listinfo/python-win32