[Zope] SQL to Excel Redux

Thomas B. Passin tpassin@mitretek.org
Tue, 7 May 2002 10:58:03 -0400


[Bruce Dykes]

> ----- Original Message -----
> From: "Thomas B. Passin" <tpassin@mitretek.org>
> To: <zope@zope.org>
> Sent: Monday, May 06, 2002 11:26
> Subject: Re: [Zope] SQL to Excel Redux
>
>
> > Hope you really used <dtml-var> and not <dtml_var> as you posted here!
>
> D'OH!
>
> Okay, now that's fixed, I'm getting the error I initially expected:
>
> Error Type: KeyError
> Error Value: Company_Name
>
> This doesn't surprise me, as the Browse tab on the ODBC connector comes up
> empty, and this being an Excel spreadsheet, not a database table proper,
> there are no field names specifically assigned to the data.
>
> Now, where should the field names be declared/assigned?
>

In the test tab for the database connection, type

select * from [table$]

(use the worksheet name instead of "table", and make sure to end with the
"$").  Then see what column names are displayed.  Then write a query using
them.  You may have to insert a header row with column names in the
spreadsheet, I'm not sure about that.

I noticed on the speadsheet that I tried that unlabeled columns ended up
named "F2", "F3", etc.  I tried select F2 from [table$], and that returned
the expected column.

> And as a more general question, in the ODBC connector docs, it says that
not
> all databases support browsing...what is the specific limitation? I've
been
> able to browse both Access and FoxPro databases, and I find it an
invaluable
> tool. The Excels spreadsheet is the first thing that's ever come up blank
> for me, and that's kind of an odd duck anyway...
>

I don't know what you mean by "browsing" here.  But I tried, as you know,
the select * from [table$] and it worked as expected.   So you should be
able to see the data as for any other database.

Cheers,

Tom P