Re: Excel and pg - Mailing list pgsql-general
From | Martin Gainty |
---|---|
Subject | Re: Excel and pg |
Date | |
Msg-id | BLU142-W3243E278978EBC1D6D579AE5A0@phx.gbl Whole thread Raw |
In response to | Re: Excel and pg (Craig Ringer <craig@postnewspapers.com.au>) |
Responses |
Re: Excel and pg
|
List | pgsql-general |
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é
> 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.
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é
Ez az üzenet bizalmas. Ha nem ön az akinek szánva volt, akkor kérjük, hogy jelentse azt nekünk vissza. Semmiféle továbbítása vagy másolatának készítése nem megengedett. Ez az üzenet csak ismeret cserét szolgál és semmiféle jogi alkalmazhatósága sincs. Mivel az electronikus üzenetek könnyen megváltoztathatóak, ezért minket semmi felelöség nem terhelhet ezen üzenet tartalma miatt.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
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.
pgsql-general by date: