Thread: Excel and pg

I've to deal with a volunteer pet project and I wouldn't like to get
crazy fighting with the idiosyncrasies of Access but still I've no
time to build up an interface that will be enough user friendly to
overcome the resistance of something new.

So I thought just to use Excel 2003 as the front-end to postgresql,
everything on Windows XP.

I'm not any more (if I have ever been) comfortable with MS Office
stuff and Windows.
I know using postgresql is like using an elephant to fight a
microbe here, but still since I'll have to write the business logic
and I don't want to spend hours understanding why I can't make a
join or what is the equivalent of coalesce etc... I'd like to know
if I'm getting into more trouble just to set the things up.


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? I think the rough path
  should be use ODBC (OleDB?) Do I have to install anything more
  other than postgresql?
- can postgresql load data from an Excel sheet? Or Excel write data
  to postgresql from an excel sheet? dblink?
- 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)?
- are there tools to make backup/restore very easy even for
  "point&click" kind of users?
- 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? I can't think about anything else other than
  backup they will have to deal with once they see their data in
  Excel and they can backup/restore easily.

thanks

BTW I saw a lot of nice things on the pg package for Windows...
especially for debugging that I'm not sure I can find for Linux or
can be as easily installed as with Stack Builder:
- debugger
- Tuning Wizard
- replication solution (I wonder if it is easier to have a working
  solution with aptitude or Stack Builder)

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

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

Sergio-

the idea to accomodate mandarin audience is a good one..china represents a vital growing economy and will appreciate the fact you are writing in their lang

There are about 12 different ways of accomplishing this featureset in J2EE
TC would be the lightest implementation
GF or WL would be the more heavyweight J2EE AppServer offerings
Resin and Websphere would be middle category offering
You can make this as simple as possible..a few jsp pages and/or complicated
via SSO Portal which governs accesses to resources based on supplied Signon principal
all of your resources (pages) would accomodate language of choice English, Mandarin, German French or Italian or Hungarian..mandarin is far tougher conversion as most ideas are represented by <picto/ideo>graphs vs typical Indo-European language..a good point is once you achieve 1 far-eastern lang it is a far easier conversion to go into Japanese Korean as CJK are generally considered to use a common base character set

Excel/VB is specific to windows GUI desktop and has little flexibility for conversion to webapp which is how 95% of apps are coded now..at least from what i am currentlt seeing

Please read the link that I sent you on Unicode to gain an understanding on how Unicode is implemented on Postgres Server and Client

Martin Gainty
______________________________________________
Jogi és Bizalmassági kinyilatkoztatás/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité

Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni.

> Date: Mon, 18 May 2009 09:14:41 +0800
> From: craig@postnewspapers.com.au
> To: mail@webthatworks.it
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] 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
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Insert movie times and more without leaving Hotmail®. See how.

Martin Gainty wrote:

> There are about 12 different ways of accomplishing this featureset in J2EE
> TC would be the lightest implementation

For the unitiated: Tomcat (from the Apache Software Foundation)

> GF or WL would be the more heavyweight J2EE AppServer offerings

For the uninitiated: GlassFish (from Sun) and WebLogic (from BEA/Oracle)


J2EE development isn't the shrieking nightmare it used to be with
Enterprise Java Beans, since JPA 1.0 and implementations of it like
Hibernate Annotations make things a _great_ deal less painful.

You still need to "get" Java in a pretty solid way, and be willing to
rethink the way you handle database access a bit. You're doing your
database access via an object-model translation layer in an application
server, accessing and manipulating persistent objects that back onto the
real database. You have to define the persistence scheme by which these
objects are stored and retrieved using tools like Hibernate or JPA 1.0
(Hibernate Annotations, Toplink, etc). You have to understand Java
object persistence and migration, vaguely how the app servers work, how
to work with JSP, etc.

Many of the same tools are useful in a J2SE environment for rich client
development using Swing, but that's a *lot* of work for a volunteer project.

--
Craig Ringer

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

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