Skip site navigation (1) Skip section navigation (2)

Re: excel and postgresql: tips and questions

From: "Tim Bishop" <tbishop(at)pooka(dot)otago(dot)ac(dot)nz>
To: "'Jeff Eckermann'" <jeff_eckermann(at)yahoo(dot)com>,"'Merlin Moncure'" <merlin(dot)moncure(at)rcsonline(dot)com>,<pgsql-odbc(at)postgresql(dot)org>
Subject: Re: excel and postgresql: tips and questions
Date: 2004-11-09 22:48:55
Message-ID: 1B2042CF7F6F@pooka.otago.ac.nz (view raw or flat)
Thread:
Lists: pgsql-odbc

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


In response to

pgsql-odbc by date

Next:From: David BearDate: 2004-11-10 06:21:17
Subject: import limited to 1000 tuples
Previous:From: Alexander CohenDate: 2004-11-09 18:38:47
Subject: new data types

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group