Обсуждение: 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!
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! > >
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.
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. >
"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.
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
--------------------------------------------------
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 > -------------------------------------------------- >
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
--------------------------------------------------
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=-
"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
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 ...
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.