Обсуждение: excel and postgresql: tips and questions

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

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

--- Merlin Moncure <merlin.moncure@rcsonline.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

> Well, don't use Excel then ;-)

Unfortunately, that's not an option.  Our clients almost universally
have MS Office installed and have semi-professional office workers that
would like to cut their own reports to our database.  I'm basically
setting up example templates to show what the server can do. Why MS did
not set up sql-passthrough directly into excel completely blows my mind.
Excel 2003 Pro allows you to edit the spreadsheet properties but most of
our users are around the 2000/2002 level.

What's really surprising me is how much trouble I'm having getting
decent information on binding excel to a database.  I would have thought
this a more common approach to presenting data in an office.

> 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

That's will probably work, I'll take a look.  I wanted to avoid using a
coding approach to things but this be the only way.

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

Another good idea. Actually, I prefer this to cutting VB code into the
spreadsheet.  However, I'll still run into parameterization problems in
Excel, namely when using ~* and ilike in parameterized queries.

Another tricky solution would be to redefine some operators on the
server to expand what can be done inside ms query.  Maybe using
set-returning functions might also work.  My queries are generally
already wrapped in views so my problems are mostly with the various
operators.

Merlin

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

I would try using a web query instead of access.  Make a simple (password
protected!) cgi that executes arbitrary sql and returns a html table.

Then use a web query from excel.  From recording a macro, I see that you can
set the URL to be anything you want (from vb)

    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://www.otago.ac.nz/phonebook/surnamef.html",
Destination:=Range( _
        "A10"))
        .Name = "surnamef"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingRTF
        .WebTables = "6"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

You should thus be able to pop any parameters or sql you want in the query
string.

The advantage of this method is that excel handles getting the data into the
worksheet.

And, you don't need to install Postgres odbc drivers on your deployment
machines.

I did do this with 2003.  I'm not sure if you can do this with earlier Excel
versions.


-Tim