pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedImport from Ms Excel
8+ messages / 6 participants
[nested] [flat]
* Import from Ms Excel
@ 2004-03-16 09:43 Kumar <sgnerd@yahoo.com.sg>
0 siblings, 3 replies; 8+ messages in thread
From: Kumar @ 2004-03-16 09:43 UTC (permalink / raw)
To: pgsql-sql
Dear Friends,
Is possible to import data from MS Excel sheet into postgres database 7.3.4 running on Linux 7.2
Thanks
Kumar
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 11:29 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 2 replies; 8+ messages in thread
From: Andrew Sullivan @ 2004-03-16 11:29 UTC (permalink / raw)
To: pgsql-sql
On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote:
> Dear Friends,
>
> Is possible to import data from MS Excel sheet into postgres
> database 7.3.4 running on Linux 7.2
Yes. I find the easiest way is to export a delimited file from Excel
and use the \copy command in psql.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
I remember when computers were frustrating because they *did* exactly what
you told them to. That actually seems sort of quaint now.
--J.D. Baldwin
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 11:42 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 1 reply; 8+ messages in thread
From: Achilleus Mantzios @ 2004-03-16 11:42 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
O kyrios Andrew Sullivan egrapse stis Mar 16, 2004 :
> On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar wrote:
> > Dear Friends,
> >
> > Is possible to import data from MS Excel sheet into postgres
> > database 7.3.4 running on Linux 7.2
>
> Yes. I find the easiest way is to export a delimited file from Excel
> and use the \copy command in psql.
Another fancy lib (although not necessarilly pgsql specific),
is the POI project from jakarta.
You can read/write M$ XLS documents from java, and
subsequently (via jdbc) manipulate pgsql tables.
The good part is that you can dynamically generate an excel file
with arbitary content.
>
> A
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 11:47 Andrew Sullivan <ajs@crankycanuck.ca>
parent: Achilleus Mantzios <achill@matrix.gatewaynet.com>
0 siblings, 1 reply; 8+ messages in thread
From: Andrew Sullivan @ 2004-03-16 11:47 UTC (permalink / raw)
To: pgsql-sql
On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote:
> Another fancy lib (although not necessarilly pgsql specific),
> is the POI project from jakarta.
> You can read/write M$ XLS documents from java, and
> subsequently (via jdbc) manipulate pgsql tables.
>
> The good part is that you can dynamically generate an excel file
> with arbitary content.
You can do the same thing (I have done it) with Perl using the Pg and
WriteExcel modules. Excel has a lot of pretty annoying limitations,
though, and it's fairly dangerous to get too dependent on it for this
sort of thing, as you can easily run into its limitations. I suppose
it depends on how big your resulting spreadsheets are going to be.
In my experience, though, the first thing that happens when you
deliver someone a summary spreadsheet is, they ask you for the raw
data so they can double-check it.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
This work was visionary and imaginative, and goes to show that visionary
and imaginative work need not end up well.
--Dennis Ritchie
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 12:08 Achilleus Mantzios <achill@matrix.gatewaynet.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Achilleus Mantzios @ 2004-03-16 12:08 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; +Cc: pgsql-sql
O kyrios Andrew Sullivan egrapse stis Mar 16, 2004 :
> On Tue, Mar 16, 2004 at 01:42:45PM +0200, Achilleus Mantzios wrote:
> > Another fancy lib (although not necessarilly pgsql specific),
> > is the POI project from jakarta.
> > You can read/write M$ XLS documents from java, and
> > subsequently (via jdbc) manipulate pgsql tables.
> >
> > The good part is that you can dynamically generate an excel file
> > with arbitary content.
>
> You can do the same thing (I have done it) with Perl using the Pg and
> WriteExcel modules. Excel has a lot of pretty annoying limitations,
> though, and it's fairly dangerous to get too dependent on it for this
> sort of thing, as you can easily run into its limitations.
Especially the way "excel" tries to store date/time "cells" (in a way
much like cobol in MVS mainframes),
or text not being able to contain only numeric chars, etc...
> I suppose
> it depends on how big your resulting spreadsheets are going to be.
> In my experience, though, the first thing that happens when you
> deliver someone a summary spreadsheet is, they ask you for the raw
> data so they can double-check it.
At least they dont have to worry about their latest antivirus update!
>
> A
>
>
--
-Achilleus
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 12:13 Dana Hudes <dhudes@tcp-ip.info>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 8+ messages in thread
From: Dana Hudes @ 2004-03-16 12:13 UTC (permalink / raw)
To: Kumar <sgnerd@yahoo.com.sg>; +Cc: pgsql-sql
Certainly you can export your spreadsheet in CSV and
it will easily go into Postgresql database (after you
create the database and its tables).
If you want something more sophisticated, a program
in e.g. Perl could use one of the various library programs
to extract data in some other-than-straightforward
manner (perhaps only certain columns or a certain set of regions?)
and then use one of the database libraries (e.g. DBI) to insert
into database. The CSV export is the simplest approach.
On Tue, 16 Mar 2004, Kumar wrote:
> Dear Friends,
>
> Is possible to import data from MS Excel sheet into postgres database 7.3.4 running on Linux 7.2
>
> Thanks
> Kumar
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-16 16:13 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Andrew Sullivan <ajs@crankycanuck.ca>
1 sibling, 0 replies; 8+ messages in thread
From: Jeff Eckermann @ 2004-03-16 16:13 UTC (permalink / raw)
To: Andrew Sullivan <ajs@crankycanuck.ca>; pgsql-sql
--- Andrew Sullivan <ajs@crankycanuck.ca> wrote:
> On Tue, Mar 16, 2004 at 03:13:38PM +0530, Kumar
> wrote:
> > Dear Friends,
> >
> > Is possible to import data from MS Excel sheet
> into postgres
> > database 7.3.4 running on Linux 7.2
>
> Yes. I find the easiest way is to export a
> delimited file from Excel
> and use the \copy command in psql.
A couple of quick points:
* Don't use .csv, unless you are quite sure that your
data does not include any commas. "Copy" isn't smart
enough to know that the quotes mean "ignore the
delimiters inside here".
* If your data does include commas, even using
tab-delimited will cause Excel to pointlessly quote
those columns, and the quotes will be imported as data
unless you remove them.
* Make sure to remove any carriage returns that may
have been included with your line endings. I think
newer versions of "copy" are better about handling
that, but best to be sure...
>
> A
>
> --
> Andrew Sullivan | ajs@crankycanuck.ca
> I remember when computers were frustrating because
> they *did* exactly what
> you told them to. That actually seems sort of
> quaint now.
> --J.D. Baldwin
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
__________________________________
Do you Yahoo!?
Yahoo! Mail - More reliable, more storage, less spam
http://mail.yahoo.com
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Import from Ms Excel
@ 2004-03-21 16:25 Hans de Bruin <hans@nl.invalid>
parent: Kumar <sgnerd@yahoo.com.sg>
2 siblings, 0 replies; 8+ messages in thread
From: Hans de Bruin @ 2004-03-21 16:25 UTC (permalink / raw)
To: pgsql-sql
Kumar wrote:
> Dear Friends,
>
> Is possible to import data from MS Excel sheet into postgres database
> 7.3.4 running on Linux 7.2
>
Install the postgress ODBC drivers. Create a new access database. Create two
linked tabels, one to a table in de database and one to the excel sheet. Use
a insert into query to transfer the data.
--
Hans de Bruin
http://eratosthenes.xs4all.nl
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2004-03-21 16:25 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-03-16 09:43 Import from Ms Excel Kumar <sgnerd@yahoo.com.sg>
2004-03-16 11:29 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-03-16 11:42 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-03-16 11:47 ` Andrew Sullivan <ajs@crankycanuck.ca>
2004-03-16 12:08 ` Achilleus Mantzios <achill@matrix.gatewaynet.com>
2004-03-16 16:13 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2004-03-16 12:13 ` Dana Hudes <dhudes@tcp-ip.info>
2004-03-21 16:25 ` Hans de Bruin <hans@nl.invalid>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox