Thread

1. excell to postgres

   Hal Snyder <hal@vailsys.com> — 2000-04-17T19:10:05Z

   PostgreSQL team - please comment if you see a better way to do this or
   if I'm getting something wrong here!
   
   "Martin A. Marques" <martin@math.unl.edu.ar> writes:
   
   > Is there any utilitie (for Linux if it can be) to pass from excell
   > tables to postgres database tables?
   
   We are just beginning to solve a similar problem, although the
   PostgreSQL server is running on FreeBSD rather than Linux.
   
   Goal is to migrate some technical info stores from .XLS files to
   tables on PostgreSQL where everyone who needs them can get at them.
   
   Our plan is:
   
   1. Mirror layout of the current .XLS tables as a pgsql schema. We are
   keeping it simple - all columns are varchar() and no indexing is used.
   This schema is a throw-away, designed to make it as idiot proof and
   effortless as possible for the Excel user to drop his tables into the
   database. 
   
   2. Export .XLS files as comma-delimited text into Access.
   
   3. Install postodbc on the MS box and link an Access database to the
   provisional schema in #1. The postodbc bits seem a bit crufty (pgsql
   6.5.3) and are alleged not to work if you index on data types other
   than int, so we again plan to stay away from indexing at this point.
   
   4. Drop the Access local tables into PostgreSQL. At this point, our MS
   user can use the tables almost as easily as if they were on Excel.
   That is one major goal and why we didn't just export .XLS comma
   delimited into pgsql - minimal squawking from the MS user.
   
   5. Have a production schema which is better normalized, etc than the
   raw input one derived from the ad hoc spreadsheets.
   
   6. Use SQL scripts (pg-perl or such) to migrate the raw tables
   imported in #4 into the production tables. Link the production tables
   to Access for our MS guy.
   
   Status: #1 is done. #3 is done in that we can read/write pgsql tables
   from MS Access. We expect #2 and #4 to happen today or tomorrow.
   
   When it's all done, we hope to have: a) our MS user happy using
   Access; b) engineers able to use intranet interface to the database
   for common queries and updates; c) net admin guys happy because they
   can do real SQL and tie the data into the rest of the support
   database. PostgreSQL is a Good Thing!
   
   
   

2. Re: excell to postgres

   Jeffrey <seesej@uswest.net> — 2000-04-17T19:48:12Z

   I don't understand the need for such complicated solutions. Why not just
   create a table with your shcema, pg_dump the table and insert your delimited
   .xls file in the pg_dump file. Then re-install the pg_dump-ed table.
   
   > From: "Hal Snyder" <hal@vailsys.com>
   > Date: 17 Apr 2000 14:10:05 -0500
   > To: pgsql-general@hub.org
   > Cc: "Martin A. Marques" <martin@math.unl.edu.ar>
   > Subject: [GENERAL] excell to postgres
   > 
   > PostgreSQL team - please comment if you see a better way to do this or
   > if I'm getting something wrong here!
   > 
   > "Martin A. Marques" <martin@math.unl.edu.ar> writes:
   > 
   >> Is there any utilitie (for Linux if it can be) to pass from excell
   >> tables to postgres database tables?
   > 
   > We are just beginning to solve a similar problem, although the
   > PostgreSQL server is running on FreeBSD rather than Linux.
   > 
   > Goal is to migrate some technical info stores from .XLS files to
   > tables on PostgreSQL where everyone who needs them can get at them.
   > 
   > Our plan is:
   > 
   > 1. Mirror layout of the current .XLS tables as a pgsql schema. We are
   > keeping it simple - all columns are varchar() and no indexing is used.
   > This schema is a throw-away, designed to make it as idiot proof and
   > effortless as possible for the Excel user to drop his tables into the
   > database. 
   > 
   > 2. Export .XLS files as comma-delimited text into Access.
   > 
   > 3. Install postodbc on the MS box and link an Access database to the
   > provisional schema in #1. The postodbc bits seem a bit crufty (pgsql
   > 6.5.3) and are alleged not to work if you index on data types other
   > than int, so we again plan to stay away from indexing at this point.
   > 
   > 4. Drop the Access local tables into PostgreSQL. At this point, our MS
   > user can use the tables almost as easily as if they were on Excel.
   > That is one major goal and why we didn't just export .XLS comma
   > delimited into pgsql - minimal squawking from the MS user.
   > 
   > 5. Have a production schema which is better normalized, etc than the
   > raw input one derived from the ad hoc spreadsheets.
   > 
   > 6. Use SQL scripts (pg-perl or such) to migrate the raw tables
   > imported in #4 into the production tables. Link the production tables
   > to Access for our MS guy.
   > 
   > Status: #1 is done. #3 is done in that we can read/write pgsql tables
   > from MS Access. We expect #2 and #4 to happen today or tomorrow.
   > 
   > When it's all done, we hope to have: a) our MS user happy using
   > Access; b) engineers able to use intranet interface to the database
   > for common queries and updates; c) net admin guys happy because they
   > can do real SQL and tie the data into the rest of the support
   > database. PostgreSQL is a Good Thing!
   > 
   > 
   
   
   

3. Re: excell to postgres

   Hal Snyder <hal@vailsys.com> — 2000-04-17T20:41:24Z

   Jeffrey <seesej@uswest.net> writes:
   
   > I don't understand the need for such complicated solutions. Why not
   > just create a table with your shcema, pg_dump the table and insert
   > your delimited .xls file in the pg_dump file. Then re-install the
   > pg_dump-ed table.
   
   The above step would save the MS user from converting his .XLS files
   to Access - all he does is Save As .CSV. Good idea.
   
   

4. RE: excell to postgres

   Chris Carbaugh <chris@leerelectric.com> — 2000-04-17T21:20:26Z

   If you really want to save the user some work/confusion, investigate what
   you can do with VB under XL.
   
   I would think XL allows you to set some options when export as CSV, with VB,
   you could write your own Save function.
   
   Chris
   
   > -----Original Message-----
   > From: pgsql-general-owner@hub.org
   > [mailto:pgsql-general-owner@hub.org]On
   > Behalf Of Hal Snyder
   > Sent: Monday, April 17, 2000 4:41 PM
   > To: pgsql-general@hub.org
   > Subject: Re: [GENERAL] excell to postgres
   >
   >
   > Jeffrey <seesej@uswest.net> writes:
   >
   > > I don't understand the need for such complicated solutions. Why not
   > > just create a table with your shcema, pg_dump the table and insert
   > > your delimited .xls file in the pg_dump file. Then re-install the
   > > pg_dump-ed table.
   >
   > The above step would save the MS user from converting his .XLS files
   > to Access - all he does is Save As .CSV. Good idea.
   >
   
   
   

5. Re: excel to postgres

   Hal Snyder <hal@vailsys.com> — 2000-04-17T21:48:08Z

   "Chris Carbaugh" <chris@leerelectric.com> writes:
   
   > If you really want to save the user some work/confusion, investigate
   > what you can do with VB under XL.
   > 
   > I would think XL allows you to set some options when export as CSV,
   > with VB, you could write your own Save function.
   
   How messy to Save to a table via ODBC? Excel talking straight to
   PostgreSQL - nice idea.
   
   

6. Re: excel to postgres

   Tom Cook <tcook@lisa.com.au> — 2000-04-17T23:37:49Z

   On 17 Apr 2000, Hal Snyder wrote:
   
   > "Chris Carbaugh" <chris@leerelectric.com> writes:
   > 
   > > If you really want to save the user some work/confusion, investigate
   > > what you can do with VB under XL.
   > > 
   > > I would think XL allows you to set some options when export as CSV,
   > > with VB, you could write your own Save function.
   > 
   > How messy to Save to a table via ODBC? Excel talking straight to
   > PostgreSQL - nice idea.
   > 
   
   This would have to be doable in VB, wouldn't it? Doesn't it have some sort
   of ODBC functionality? I'm no expert here (never used the language) but
   I'd have thought it'd have to be in there...
   
   Cheers
   --
   Tom Cook - Software Engineer
   
   "Christ died for sin, the righteous for the unrighteous, to bring you to God."
   	- 1 Peter 3:18
   
   LISAcorp - www.lisa.com.au
   
   --------------------------------------------------
   38 Greenhill Rd.          Level 3, 228 Pitt Street
   Wayville, SA, 5034        Sydney, NSW, 2000
   
   Phone:   +61 8 8272 1555  Phone:   +61 2 9283 0877
   Fax:     +61 8 8271 1199  Fax:     +61 2 9283 0866
   --------------------------------------------------
   
   
   

7. Re: excel to postgres

   Andrew Perrin - Demography <aperrin@demog.berkeley.edu> — 2000-04-18T03:25:51Z

   Sure, VB works, as would perl with DBD::ODBC module.  I've been using that
   technique for porting a database from access to postgres, and it should
   work equally well with excel-based ODBC connections.  Disadvantage to the
   original poster is that it does require a Windows machine to read the
   Excel file.
   
   ---------------------------------------------------------------------
   Andrew J. Perrin - aperrin@demog.berkeley.edu - NT/Unix Admin/Support
   Department of Demography    -    University of California at Berkeley
   2232 Piedmont Avenue #2120  -    Berkeley, California, 94720-2120 USA
   http://demog.berkeley.edu/~aperrin --------------------------SEIU1199
   
   On Tue, 18 Apr 2000, Tom Cook wrote:
   
   > On 17 Apr 2000, Hal Snyder wrote:
   > 
   > > "Chris Carbaugh" <chris@leerelectric.com> writes:
   > > 
   > > > If you really want to save the user some work/confusion, investigate
   > > > what you can do with VB under XL.
   > > > 
   > > > I would think XL allows you to set some options when export as CSV,
   > > > with VB, you could write your own Save function.
   > > 
   > > How messy to Save to a table via ODBC? Excel talking straight to
   > > PostgreSQL - nice idea.
   > > 
   > 
   > This would have to be doable in VB, wouldn't it? Doesn't it have some sort
   > of ODBC functionality? I'm no expert here (never used the language) but
   > I'd have thought it'd have to be in there...
   > 
   > Cheers
   > --
   > Tom Cook - Software Engineer
   > 
   > "Christ died for sin, the righteous for the unrighteous, to bring you to God."
   > 	- 1 Peter 3:18
   > 
   > LISAcorp - www.lisa.com.au
   > 
   > --------------------------------------------------
   > 38 Greenhill Rd.          Level 3, 228 Pitt Street
   > Wayville, SA, 5034        Sydney, NSW, 2000
   > 
   > Phone:   +61 8 8272 1555  Phone:   +61 2 9283 0877
   > Fax:     +61 8 8271 1199  Fax:     +61 2 9283 0866
   > --------------------------------------------------
   > 
   
   
   

8. Re: excel to postgres

   Tom Cook <tcook@lisa.com.au> — 2000-04-18T03:49:16Z

   On Mon, 17 Apr 2000, Andrew Perrin - Demography wrote:
   
   > Sure, VB works, as would perl with DBD::ODBC module.  I've been using that
   > technique for porting a database from access to postgres, and it should
   > work equally well with excel-based ODBC connections.  Disadvantage to the
   > original poster is that it does require a Windows machine to read the
   > Excel file.
   
   Probably if users are saving to an excel file to be put into access and
   then shipped of to a postgres instance then there is a windows machine
   around. So write a function to save to the database instance, and then
   alias it to a menu item 'Save to Database' or something like that.
   
   [snip]
   > > This would have to be doable in VB, wouldn't it? Doesn't it have some sort
   > > of ODBC functionality? I'm no expert here (never used the language) but
   > > I'd have thought it'd have to be in there...
   
   Cheers
   --
   Tom Cook - Software Engineer
   
   "Christ died for sin, the righteous for the unrighteous, to bring you to God."
   	- 1 Peter 3:18
   
   LISAcorp - www.lisa.com.au
   
   --------------------------------------------------
   38 Greenhill Rd.          Level 3, 228 Pitt Street
   Wayville, SA, 5034        Sydney, NSW, 2000
   
   Phone:   +61 8 8272 1555  Phone:   +61 2 9283 0877
   Fax:     +61 8 8271 1199  Fax:     +61 2 9283 0866
   --------------------------------------------------
   
   
   

9. Starting a new project, any opinions about using 7.0?

   Michael S. Kelly <michaelk@axian.com> — 2000-04-18T17:11:09Z

   I'm starting a new project.  We've selected PostgreSQL and I'm wondering if
   we shouldn't just jump in with 7.0 and avoid conversion hassles later on.  I
   guess the issues for me are:
   
   1.  Stability.  How good is Beta 5 (it is 5 now isn't it?)
   2.  Documentation.  Are the docs ready?
   3.  Support.  Who out there can help when things go wrong?
   
   I'd also be interested in what you feel are the most compelling features of
   7.0.  Is it the foreign keys, the overhauled optimizer, the updated psql, or
   other features?
   
   Thanks,
   
   -=michael=-
   
   
   
   

10. Re: Starting a new project, any opinions about using 7.0?

    Ron Peterson <rpeterson@yellowbank.com> — 2000-04-18T18:34:03Z

    "Michael S. Kelly" wrote:
    > 
    > I'm starting a new project.  We've selected PostgreSQL and I'm wondering if
    > we shouldn't just jump in with 7.0 and avoid conversion hassles later on.
    
    Yes.
    
    I haven't seen any discussions on this list since 7.0beta1 was released
    that would indicate you should be concerned about it's stability.  Quite
    the contrary - there have been a number of discussions which concluded
    by saying something like "...why don't you just try upgrading to
    7.0betaX and maybe your problems will go away."  At which point the
    discussion usually ceased.
    
    Also, by the time your project, whatever it is, is really underway, I
    think you can expect to see the final release of 7.0.
    
    Don't forget that one of the contributions you can make to the
    PostgreSQL effort is to provide feedback.  Of course feedback on the
    current product is more useful than feedback on past versions.
    
    The features you mention are significant additions to PostgreSQL's
    capabilities.  I'm particularly grateful for the addition of referential
    integrity constraints.
    
    Ron Peterson
    rpeterson@yellowbank.com
    
    

11. Re: Starting a new project, any opinions about using 7.0?

    Marc Fournier <scrappy@hub.org> — 2000-04-18T18:52:40Z

    On Tue, 18 Apr 2000, Michael S. Kelly wrote:
    
    > I'm starting a new project.  We've selected PostgreSQL and I'm wondering if
    > we shouldn't just jump in with 7.0 and avoid conversion hassles later on.  I
    > guess the issues for me are:
    > 
    > 1.  Stability.  How good is Beta 5 (it is 5 now isn't it?)
    
    RC1 now ... I'm running a UDMSearch database, amongst other things, off of
    that server right now ... 75k+ pages, a dictionary of >9million tuples
    ... and find it both stable and faster then v6.5.3 ... only reason all my
    databases aren't running off of it right now is haven't had time to move
    them ...
    
    > 3.  Support.  Who out there can help when things go wrong?
    
    The mailing lists, if you are into that, are quite fast and effective
    ... if you want to reach someone by phone,
    
    	PostgreSQL, Inc (http://www.pgsql.com) provides support ...
    
    
    
    

12. Re: Starting a new project, any opinions about using 7.0?

    Lincoln Yeoh <lylyeoh@mecomb.com> — 2000-04-20T01:00:50Z

    At 10:11 AM 18-04-2000 -0700, Michael S. Kelly wrote:
    >I'm starting a new project.  We've selected PostgreSQL and I'm wondering if
    >we shouldn't just jump in with 7.0 and avoid conversion hassles later on.  I
    >guess the issues for me are:
    >
    >1.  Stability.  How good is Beta 5 (it is 5 now isn't it?)
    
    Haven't personally shifted over yet. But will probably do so soon - looks
    like quite a number have shifted over, and the noticeable lack of screaming
    and grumbling is good.
    
    >2.  Documentation.  Are the docs ready?
    
    Docs here seem a bit lacking on the practical stuff. But I don't dare make
    too much noise in case a whole bunch of people suggest I write the docs ;). 
    
    >3.  Support.  Who out there can help when things go wrong?
    
    It's everyone here I guess :). What I like about most open source stuff, is
    that you can get real info on what works, partially works, flaky, slightly
    broken and completely broken. No sales, marketing etc to get in the way
    between you and the truth.
    
    I've had way too many problems with closed source stuff, trying to get
    things which officially work, but in practice don't or require undocumented
    kludges.
    
    >I'd also be interested in what you feel are the most compelling features of
    >7.0.  Is it the foreign keys, the overhauled optimizer, the updated psql, or
    >other features?
    
    Compelling reasons to shift? It appears to be a significant improvement
    overall and the other reason is to give the developers less of a headache
    dealing with multiple versions. Can focus on fixing 7.0 to make it really
    good.
    
    The foreign keys support should help make one of my colleagues more
    comfortable.
    
    Cheerio,
    
    Link.