Обсуждение: Import from Ms Excel

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

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

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

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

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
> 

--- 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

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