Okay, I'm trying to publish the data from an excel spreadsheet via an ODBC connection. The connection starts okay, and I found this SQL snippet from a Java site: select URL from [qas$] where Month='March' and Year=2000 Note that the table name is the name of the worksheet with a $ appended to the end. You have to append the $ in order for the query to work. Why? Because. The brackets are there because $ is a reserved character in SQL. Life is never easy. ----------------------- Now all I want to do is SELECT *...I don't need anything fancier than that, I'm just trying to publish the entire worksheet. No variation on the above code snippet seems to work. Has anybody gotten SQL queries into excel to work? And how did you do it? thanks in advance bkd As an aside, I'm currently publishing this document via Apache, with an excellent program, xlhtml set up as a handler....but that's proven less than useful as of late.
[Bruce Dykes]
Okay, I'm trying to publish the data from an excel spreadsheet via an ODBC connection.
The connection starts okay, and I found this SQL snippet from a Java site:
select URL from [qas$] where Month='March' and Year=2000
Note that the table name is the name of the worksheet with a $ appended to the end. You have to append the $ in order for the query to work. Why? Because. The brackets are there because $ is a reserved character in SQL. Life is never easy. -----------------------
Now all I want to do is SELECT *...I don't need anything fancier than that, I'm just trying to publish the entire worksheet. No variation on the above code snippet seems to work.
Has anybody gotten SQL queries into excel to work? And how did you do it?
thanks in advance bkd
As an aside, I'm currently publishing this document via Apache, with an excellent program, xlhtml set up as a handler....but that's proven less than useful as of late.
I just tried in using your syntax. It worked fine. By that, I mean that the Test tab of the connection page returned a reasonable-looking result when I typed select * from [land$] into the query box (here, "land" is the name of the worksheet). I am using Zope 2.3.3 on Win2000. Maybe you better say what you mean by "[doesn't] seem to work". Cheers, Tom P
----- Original Message ----- From: "Thomas B. Passin" <tpassin@mitretek.org> To: <zope@zope.org> Sent: Thursday, May 02, 2002 11:24 Subject: Re: [Zope] SQL to Excel
I just tried in using your syntax. It worked fine. By that, I mean that the Test tab of the connection page returned a reasonable-looking result when I typed
select * from [land$]
into the query box (here, "land" is the name of the worksheet).
I am using Zope 2.3.3 on Win2000.
Maybe you better say what you mean by "[doesn't] seem to work".
I was getting SQL syntax errors, but my personal entropy field seems to have abated, and it's working fine now. One possible difference is that I was using the test tab of the ODBC connection page instead of testing from the Z SQL method. thanks to all who replied. bkd
----- Original Message ----- From: "Bruce Dykes" <bkd@graphnet.com> To: <zope@zope.org> Sent: Monday, May 06, 2002 07:02 Subject: Re: [Zope] SQL to Excel
I was getting SQL syntax errors, but my personal entropy field seems to have abated, and it's working fine now. One possible difference is that I was using the test tab of the ODBC connection page instead of testing from the Z SQL method.
The SQL works. I know this from the test tab of the Z SQL object I created. So now to build a page from the query. I adapted this bit of code from the Zope Bible: <dtml-var standard_html_header> <h2><dtml-var title_or_id> <dtml-var document_title></h2> <p> This is the <dtml-var document_id> Document in the <dtml-var title_and_id> Folder. </p> <table> <tr><td>Company Name</td><td>TechName1</td><td>TechName3</td><td>Contact</td><td>First Name</td><td>Contact</td><td>Last Name</td><td>Company/Department</td><td>Contact</td><td>Title</td><td>Phone Number</td><td>PHONENUMBER2</td><td>PhoneNumber3</td><td>Fax Number</td><td>Notes</td><td>Rtenum</td><td>ShortName</td></tr> <dtml_in carrier_list> <tr> <td><dtml_var Company_Name></td> <td><dtml_var TechName1></td> <td><dtml_var TechName3></td> <td><dtml_var Contact></td> <td><dtml_var First_Name></td> <td><dtml_var Contact></td> <td><dtml_var Last_Name></td> <td><dtml_var Company_Department></td> <td><dtml_var Contact2></td> <td><dtml_var Title></td> <td><dtml_var Phone_Number></td> <td><dtml_var PHONENUMBER2></td> <td><dtml_var PhoneNumber3></td> <td><dtml_var Fax_Number></td> <td><dtml_var Notes></td> <td><dtml_var Rtenum></td> <td><dtml_var ShortName></td> </tr> </dtml_in> </table> <dtml-var standard_html_footer> I took all the table head names from the results of the SQL test, as well the dtml_var's. But the dtml_var's aren't being rendered out...is there a place I need to declare them, such as in the carrier_list SQL method? Here's the carrier_list SQL method: select * from [Contacts$] Everything, the ODBC connection, the SQL method, and the DTML page method are all in the same folder. thanks again bkd
[Bruce Dykes]
The SQL works. I know this from the test tab of the Z SQL object I created.
So now to build a page from the query. I adapted this bit of code from the Zope Bible:
<dtml-var standard_html_header> <h2><dtml-var title_or_id> <dtml-var document_title></h2> <p> This is the <dtml-var document_id> Document in the <dtml-var title_and_id> Folder. </p>
<table> <tr><td>Company Name</td><td>TechName1</td><td>TechName3</td><td>Contact</td><td>First Name</td><td>Contact</td><td>Last
Name</td><td>Company/Department</td><td>Contact</td><td>Title</td><td>Phone
Number</td><td>PHONENUMBER2</td><td>PhoneNumber3</td><td>Fax Number</td><td>Notes</td><td>Rtenum</td><td>ShortName</td></tr> <dtml_in carrier_list> <tr> <td><dtml_var Company_Name></td>
Hope you really used <dtml-var> and not <dtml_var> as you posted here! Tom P
----- 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? 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... thanks again bkd
[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
----- Original Message ----- From: "Thomas B. Passin" <tpassin@mitretek.org> To: <zope@zope.org> Sent: Tuesday, May 07, 2002 10:58 Subject: Re: [Zope] SQL to Excel Redux
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.
That's what I started working from...but between my personal entropy field, and several DTML unfriendly column names, it didn't exactly work as hoped. So I'll make a determination as to whether a Zope address book product may be more useful for this task.
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.
Using the 'Browse' tab on the ODBC Connector page. With an actual database, the Browse tab will let you see the database schema for all the constituent tables of the database...most useful for SQL creation.... bkd
participants (2)
-
Bruce Dykes -
Thomas B. Passin