Re: Excel and pg

On Mon, May 18, 2009 at 11:01:15AM +0200, Ivan Sergio Borgonovo wrote:
> On Mon, 18 May 2009 09:14:41 +0800 Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:
> > Ivan Sergio Borgonovo wrote:
> > > I'd like to know if:
> > > - it is possible to "load" in an Excel sheet a table (view, query
> > > result) coming from postgresql and to use those data to do
> > > further computation/presentation work on Excel?
> >
> > Certainly. You can do it through the ODBC interface via VB, and I
> > think Excel also has some kind of "data browser" that lets the
> > user pull data from ODBC-accessed databases interactively.
>
> You can import tables and view too and it seems you can apply a SQL
> filter on them.

Last time I tried doing this you get to write your own SQL if you want;
no need to limit yourself to pulling a "table" out.

> pg -> Excel worked perfectly. I hope Excel -> csv -> pg will be
> equally painless. I'm a bit worried considering the limited toolset
> I can rely on I may have some localisation problems when people will
> try to save Excel -> csv.
> COPY may not support all the flexibility I need if Chinese localised
> Excel/OS will output some strange stuff in csv.

The correct place to solve this would seem to be in Excel; write some VB
code to pull out things in the correct format and put the resulting file
somewhere appropriate.

> - I've found something really weird. People say SQL is hard (yeah it
> may be...) but that S really shine once you compare it with the
> way to operate of a spread sheet ;)

They're different tools, designed to solve different problems.
Spreadsheets are wonderful for making small ad-hoc changes to small
datasets, databases are good when you're working on larger or better
defined problems (i.e. where there's some routine that can be optimised
by moving some of it into code).

> - Office SQL is a PITA. I gave up once I saw they don't implement
> COALESCE.

It's spelled Nz in MS Access, but its semantics leave a little to be
desired and it doesn't seem to exist in Excel for some strange reason.

> I was thinking about opening another ODBC connection and using
> dblink to import data from Excel to pg... but somehow it doesn't
> look it is going to really improve the procedure of importing data
> from a csv.

Depends on the problem you're trying to solve; but I've had code
uploading binary blobs into large objects into PG and then writing a set
of functions that pull the data out of these blobs into nice relational
tables. My files were somewhat more structured than Excel files so it
may not help much.

> I mean... someone doesn't do it properly (eg. some kind of
> lock/whatever on the Excel file) people won't be able to understand
> the problem.
> Saving the Excel file to csv seems something could be handled easier
> by the people that will do the job.

Doing that in code in Excel would allow you to throw up better errors
earlier.

> > You have no idea how much pain you are letting yourself into.
>
> ;)
> Right now it looks as a lesser pain than eg. trying to use aggregates
> and grouping on Excel.

Grouping and aggregates are spelled "pivot tables" in Excel and they
work well for a single level, they don't scale to more than one level
though and they require considerably more manual housekeeping than SQL.
As always, it's about picking the right tools for the job!

--
Sam http://samason.me.uk/