[python-win32] Running SQL queries on excel sheets

[Rickey, Kyle W]

Thanks John, I'll check that out.

-Kyle Rickey

-----Original Message-----
From: John Machin [mailto:sjmachin at lexicon.net] 
Sent: Sunday, March 16, 2008 4:40 AM
To: Rickey, Kyle W
Cc: python-win32 at python.org
Subject: Re: [python-win32] Running SQL queries on excel sheets

Rickey, Kyle W wrote:
> 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 :)

That's not exactly "absolutely tons of data" :-)

One problem with the ODBC approach to reading spreadsheets (at least 
with the MS Excel ODBC driver) is that it examines a limited number 
(e.g. 8 or 10) of rows to determine what type a column is. So the first 
10 rows have floats in column X then Noddy's managed to get some text in

cell X11 which is a fatal error for ODBC -- you can't continue.

[snip]

> 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>

Consider the xlrd package (http://www.lexicon.net/sjmachin/xlrd.htm). 
It'll tell you what type the data is cell-by-cell -- you can then 
compare that with your expectations.

HTH,
John