[python-win32] Running SQL queries on excel sheets
Rickey, Kyle W
Kyle.Rickey at bakerhughes.com
Mon Mar 17 14:40:45 CET 2008
Thanks John, I'll check that out.
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
> 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.
> 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.
More information about the python-win32