Re: excel and postgresql: tips and questions

--- Merlin Moncure <merlin(dot)moncure(at)rcsonline(dot)com>
wrote:

> I'm currently building a suite of excel spreadsheets
> to run against
> postgresql. Excel is a great tool, however they
> force you to use ms
> query to bind your spreadsheets to the database. I
> have concluded that
> ms query is garbage. It 'helps' you with your

Yep yep yep.

> queries by parsing them
> before they are sent to the database. If you try to
> use any features
> that ms query does not understand, for example the
> ~* operator for text
> searches, ms query will not allow you to return to
> the spreadsheet *if*
> you use the parameterized version.
>
> Example:
> select * from i_hate_ms_query where postgresql ~*
> 'great'
>
> Works fine but
>
> select * from i_hate_ms_query where postgresql ~*
> [param1]
>
> borks.
>
> Also, complex query forms with subqueries or inline
> views will of course
> completely blow the fuses of ms query. By the way,
> this problem is not
> limited to postgresql, trying to cust complex to sql
> server will give
> you similar headaches.

Well, don't use Excel then ;-)

You could just do the whole thing in code within
Excel, and avoid MS Query altogether. ADO is my
preferred choice for this. Parameters could be
captured by a dialog box that could run on opening the
spreadsheet. You would have to handle the laying out
of the data yourself, but there are various easy ways
to do this. Search the MS Knowledge Base for "getting
data from a database query into Excel" or some such,
and you will find articles which discuss this subject
pretty fully.

Alternatively you could try interposing MS Access,
i.e. define your queries in an Access database, and
use those queries as the datasource for the Excel
report. Note that I haven't done this myself, I just
know that it's doable, and I suspect that the tight
integration of MS Office products will allow you to
avoid using MS Query altogether. But you may find
yourself having to use pass-through queries, with the
need to code the capturing of parameters, so that may
not be a win in the end.

>
> That being said, I discovered that by saving the
> spreadsheet as xml you
> can edit the sql source inside the spreadsheet and
> do just about
> anything you want with it. I understand that office
> 2003 has some new
> ways to do this, but is there some simple thing that
> I am missing?
>
> Merlin
>
>
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>

__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com