[Zope] SQL to Excel

Fri, 3 May 2002 18:17:52 +0200

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

--------------InterScan_NT_MIME_Boundary
Content-Type: multipart/alternative;
	boundary="----_=_NextPart_001_01C1F2BE.1381B460"

------_=_NextPart_001_01C1F2BE.1381B460
Content-Type: text/plain;
	charset="iso-8859-1"

> 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 spreadsheets aren't tables... You have several ways of telling,
which data you want:
[qas$] refers to the entire worksheet qas
[qas$B2:G20] refers to the unnamed range B2:G20
or you can use named ranges: then you can use the name of the range like any
"normal" table ( select * from myRangeInQas )

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

The way you described works fine for me...
Does the first row of your worksheet show the columnnames? Any of them
starting with blank ?

Maybe you tell us, what happens when "it doesn't work"...

Chris

------_=_NextPart_001_01C1F2BE.1381B460
Content-Type: text/html;
	charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META HTTP-EQUIV=3D"Content-Type" CONTENT=3D"text/html; =
charset=3Diso-8859-1">
<META NAME=3D"Generator" CONTENT=3D"MS Exchange Server version =
5.5.2653.12">
<TITLE>RE: [Zope] SQL to Excel</TITLE>
</HEAD>
<BODY>
<BR>

&gt; select URL from [qas$] where Month=3D'March' and =
Year=3D2000
 &gt; 
 &gt; Note that the table name is the name of the =
worksheet with a 
 &gt; $ appended to
 &gt; the end. You have to append the $ in order for =
the query to work. Why?

Because spreadsheets aren't tables... You have =
several ways of telling, which data you want:
 [qas$] refers to the entire worksheet qas
 [qas$B2:G20] refers to the unnamed range =
B2:G20
 or you can use named ranges: then you can use the =
name of the range like any &quot;normal&quot; table ( select * from =
myRangeInQas )

&gt; Because. The brackets are there because $ is a =
reserved 
 &gt; character in SQL.
 &gt; Life is never easy.
 &gt; -----------------------
 &gt; 
 &gt; Now all I want to do is SELECT *...I don't need =
anything 
 &gt; fancier than that,
 &gt; I'm just trying to publish the entire =
worksheet. No variation 
 &gt; on the above
 &gt; code snippet seems to work.
 &gt; 
 &gt; Has anybody gotten SQL queries into excel to =
work? And how 
 &gt; did you do it?

The way you described works fine for me...
 Does the first row of your worksheet show the =
columnnames? Any of them starting with blank ?

<P><FONT SIZE=3D2>Maybe you tell us, what happens when &quot;it doesn't =
work&quot;...</FONT>
</P>

<P><FONT SIZE=3D2>Chris</FONT>
</P>

</BODY>
</HTML>
------_=_NextPart_001_01C1F2BE.1381B460--

--------------InterScan_NT_MIME_Boundary--