[Zope] SQL to Excel

Christian.Fissgus@haufe.de Christian.Fissgus@haufe.de
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>

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

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

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

<P><FONT SIZE=3D2>The way you described works fine for me...</FONT>
<BR><FONT SIZE=3D2>Does the first row of your worksheet show the =
columnnames? Any of them starting with blank ?</FONT>
</P>

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