Re: Excel and pg

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.

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.

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 think the rough path
>   should be use ODBC (OleDB?) Do I have to install anything more
>   other than postgresql?

Yes. The Pg ODBC driver.

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

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

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

--
Craig Ringer