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