Re: Excel and pg

On Mon, 18 May 2009 09:14:41 +0800
Craig Ringer <craig@postnewspapers.com.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.

This is working. I had a bit of fun trying to find the right menu in
the Chinese version.
For the uninitiated/the ones that don't have fresh memory of working
in a MS environment:
- Install Postgresql and ODBC drivers and create a DB/user etc...
- Create a system wide DSN connection. In XP is a bit hidden
  compared to my memories of W2K (Control Panel -> Performance an
  Maintenance -> Administrative Tools -> Data Source)
- Open Excel, there should be a Data Menu... I can't go further
  since the Excel was localised in Chinese. There are some menu
  that follow but we weren't able to read them maybe due to the
  mismatch of OS and Excel localisation.

You can import tables and view too and it seems you can apply a SQL
filter on them. Dates seems to be imported correctly and I think
localised according the setting of the OS (I'd ask, I think in
mainland China data should follow the European format and not the
UK/US format).

> Beware, though. Excel has funny ideas about dates and has some
> baked-in bugs in some of its functions. It doesn't know about or
> respect the foreign key relationships and constraints in the
> database, either.

Fortunately I won't delegate anything related to data integrity to
Excel. I'll keep an eye on dates.
Having had some experience with MS SQL I had some difficulties with
converting in and out dates from Postgresql at times. I know it
shouldn't be the responsibility of the DB to convert between
formats... but for some dirty works it can really comes handy.
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.

I chose to pass through pg just because I have to help to write down
some business logic for reporting and I bet I'll have to get back at
what I wrote in 1-6 months. I tried to implement some of the
reporting logic in Excel but:
- Something really fit SQL
- I don't want to relearn how an IF works, especially if I have to
  switch back and forward to Polish notation
- 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 ;)
- Office SQL is a PITA. I gave up once I saw they don't implement
  COALESCE.

If people would like to elaborate further on data coming from pg
using Excel functions they will be on their own.

> If you really must pull data into Excel, consider giving users an
> account in PostgreSQL that _ONLY_ has access to read-only views of
> the data. Those views should denormalize the data significantly and
> otherwise make it as Excel-friendly as possible. Pull the data in
> using a Visual Basic script that "protects" the data as soon as
> it's been placed on the sheets, so the user can't accidentally
> change it, just reference it.

I've to import data in Postgresql... that comes in other Excel files.
I can't rely on a complete programming environment.

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

I think that somehow "refreshing" data imported by Excel is going to
run the associated query... so if I write a function that run
\copy
and place a "select * from importdata()" in Excel... everything
people should do is save the excel as csv somewhere.

> > - can postgresql load data from an Excel sheet? Or Excel write
> > data to postgresql from an excel sheet? dblink?

> The easiest way is via CSV. You could probably also do it with some
> Visual Basic running in Excel that pushes the data via ODBC.

> If you're going to even vaguely consider putting data from a
> user-modifiable spreadsheet back in the DB, make sure to protect
> every cell the user isn't explicitly meant to be able to modify.
>
> > - am I going to incur in any localisation problem if the Windows
> >   stuff is localised in Chinese? I see I can chose the "language
> > to be used during installation". I'd prefer localization to be in
> >   English but still let people that will use the front-end to use
> >   Chinese. What about the encoding (client/server)?

> Use UTF-8 for the client and server encodings. Excel should
> convert that to/from UTF-16 ("Unicode") just fine if you use the
> Unicode ODBC driver for PostgreSQL.

That worked. Default is UTF8 server, WIN encoding Client. I didn't
even try to see if default worked and went directly to UTF8/UTF8.

> > - are there tools to make backup/restore very easy even for
> >   "point&click" kind of users?

> Make a batch file / script that runs pg_dump. Alternately, use
> PgAdmin III.

PgAdmin III seems the tool.
What are the minimum GRANT someone have to have to make a backup?

> > - anything that a non "desktop" oriented guy like me have to
> > realise before promising to put up something that will have to
> > be used by "desktop/GUI" people?

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

BTW installing postgresql on Windows XP was painless and it seems
that it is pretty light on resources too...
I got the impression that packing Postgresql and some "client" app
on Windows could be interesting for SME that can't afford MS SQL.
I'm still surprised to see even large companies putting business
logic in Excel files...

thanks

--
Ivan Sergio Borgonovo
http://www.webthatworks.it