excell to postgres

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

You could use Sun's Staroffice to take the Excel files, save them as a
text file, and then use PostgreSQL COPY features to upload them into a
database.

Saludos... ;-)

/* Saludos, ch�. Sos argentino o Uruguayo, porque conozco un tocayo tuyo
por ac� ...*/

On Mon, 17 Apr 2000, Martin A. Marques wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

ODBC driver for M$-Win?

Karel

Hi!

You can use Excel own export function to save the file - for example in
CSV (comma delimited).
Then, as Harold says, use COPY inside pgsql to read.
I don't know how will be character escaping in excel/pgsql.

Better yet, would be to install ODBC driver for postgres on your windows
system, and then open the ODBC datasource in excel.
You will then be able to simply drag and drop data from the Excel
worksheet to the pg database that you will be connecting via odbc.

M$ Access is another solution. In that case, you must set up a data source
in Control Panel, ODBC, using the Excel file database driver - thus making
an odbc database directly from the excel file. You will then be able to
use M$ Access functionalities (or PgAdmin if you prefer) to do the export.

Regards, Silvio

On Mon, 17 Apr 2000, Haroldo Stenger wrote:

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

You could use Sun's Staroffice to take the Excel files, save them as a
text file, and then use PostgreSQL COPY features to upload them into a
database.

Saludos... ;-)

/* Saludos, ch���. Sos argentino o Uruguayo, porque conozco un tocayo tuyo
por ac��� ...*/

--
``````````` Silvio Emanuel Nunes Barbosa de Macedo (PhD Std) '''''''''''''
smacedo@ic.ac.uk smacedo@inescn.pt
Intelligent and Interactive Systems Telecom. and Multimedia
Imperial College, University of London INESC Porto
Exhibition Road, Pc da Republica, 93
London SW7 2AZ, England 4050-497 Porto Portugal
Tel:+44 171 5946323 Tel:+351 22 2094220

----- Original Message -----
From: "Martin A. Marques" <martin@math.unl.edu.ar>
To: <pgsql-general@hub.org>
Sent: Monday, April 17, 2000 3:16 PM
Subject: [GENERAL] excell to postgres

Is there any utilitie (for Linux if it can be) to pass from excell tables

to

postgres database tables?

The fastest way to do it is choose Save As from the file menu, choose tab
delimited file as the file type. Then import into the postgresql database
using psql's copy command. Of course this does require you to set up the
table first in postgres!

Alternatively, you ought to be able to do it through pgAdmin's import
database tool? available through http://www.pgadmin.freeserve.co.uk which
allows you to administer stuff, and I *think* allows you to import an ODBC
database into . The Excel ODBC driver allows you to think of Excel as just
any other ODBC database, so I think this might work.

You may have problems with itsy things like date formats, though.
Yours,
Moray McConnachie
----------------------------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk

[Charset iso-8859-1 unsupported, filtering to ASCII...]

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

You could use Sun's Staroffice to take the Excel files, save them as a
text file, and then use PostgreSQL COPY features to upload them into a
database.

Try xlHtml at http://www.gate.net/~ddata/xlHtml/index.htm. This
converts Excel to HTML. Very nice. I then convert it to text using
other tools.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I dont know of anything like that but you should be able to write
one in PHP. What I normally do is export from the original
database format to a tab delimited text file and then I write a PHP
(or whatever) utility that converts a tab delimited file to the PSQL
format.

On 17 Apr 00, at 17:21, Fabrice Scemama wrote:

Can't you just export the excel sheets to a character delimited file and
then import that into pgsql?

With a little VB script in excel, you should be able to make pretty
automated. Just create you own "save" method that would save the sheet as
the delimited file is a certain location. Then have a cron job parse the
location for new files.

Just my 2 cents (and man, the bank is hurtin' :)

Chris

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

Saludos... ;-)

--
"And I'm happy, because you make me feel good, about me." - Melvin Udall
-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

You might use C OLE -- a C lib which was designed so as to
import OLE files to other formats. An application is xlHTML
(excel to HTML). I use xlHTML to import excel tables, then
convert the HTML to DBF using a perl script. For some reason,
xlHTML's author did not code a xlDBF file; and I'm not familiar
with C. I'd be interested in something better than my
solution (which I can send to you if you wish so).

xlHTML:
http://www.gate.net/~ddata/xlHtml/index.htm

Regards
Fabrice Scemama

On Mon, Apr 17, 2000 at 11:48:52AM -0400, Bruce Momjian wrote:

[Charset iso-8859-1 unsupported, filtering to ASCII...]

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from excell tables to
postgres database tables?

You could use Sun's Staroffice to take the Excel files, save them as a
text file, and then use PostgreSQL COPY features to upload them into a
database.

Try xlHtml at http://www.gate.net/~ddata/xlHtml/index.htm. This
converts Excel to HTML. Very nice. I then convert it to text using
other tools.

Hmm, this thread is the first I've seen this code: looks very interesting.
Both Bruce and the earlier poster mentioned getting html out, and then
post processing. Looks like the latest version has an 'ascii' switch, added
for plotting with gnuplot. Might be just the ticket. Adding code to do
postgresql quoting of strings should be trivial.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

Try xlHtml at http://www.gate.net/~ddata/xlHtml/index.htm. This
converts Excel to HTML. Very nice. I then convert it to text using
other tools.

Hmm, this thread is the first I've seen this code: looks very interesting.
Both Bruce and the earlier poster mentioned getting html out, and then
post processing. Looks like the latest version has an 'ascii' switch, added
for plotting with gnuplot. Might be just the ticket. Adding code to do
postgresql quoting of strings should be trivial.

Yes, that and mswordview allow me to work in an MS-dominated world.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

I'm a Linux user.
I've been using xlHTML and a home-made HTML2DBF rather than
ODBC with Perl's DBD::Proxy and DBD::ODBC because, to be
able to use ODBC, you need people to *name* the table they
create when using Excel. Well, they never do so.
Sometimes, they will name a table with 256 columns and
thousands of rows! and this, will *not* be handled by
stupid ODBC. No name being given to define the table will
make ODBC fail, as well.

You can open the file, then save it as CSV, etc.
But don't we want to make these kinds of things automatic?

That's why I use xlHTML. And yes, even with it, I come
across bad surprises, very often. So often that I've been
in the obligation to refuse that people send me Excel tables.
Access is the maximum I can tolerate (ODBC is ok at least).

Fabrice Scemama

Chris Carbaugh wrote:

Can't you just export the excel sheets to a character delimited file and
then import that into pgsql?

With a little VB script in excel, you should be able to make pretty
automated. Just create you own "save" method that would save the sheet as
the delimited file is a certain location. Then have a cron job parse the
location for new files.

Just my 2 cents (and man, the bank is hurtin' :)

Chris

-----Original Message-----
From: pgsql-general-owner@hub.org
[mailto:pgsql-general-owner@hub.org]On
Behalf Of Fabrice Scemama
Sent: Monday, April 17, 2000 12:22 PM
To: Martin A. Marques
Cc: pgsql-general@hub.org
Subject: Re: [GENERAL] excell to postgres

"Martin A. Marques" wrote:

Is there any utilitie (for Linux if it can be) to pass from

excell tables to

postgres database tables?

Saludos... ;-)

--
"And I'm happy, because you make me feel good, about me." -

Melvin Udall

-----------------------------------------------------------------
Mart�n Marqu�s email: martin@math.unl.edu.ar
Santa Fe - Argentina http://math.unl.edu.ar/~martin/
Administrador de sistemas en math.unl.edu.ar
-----------------------------------------------------------------

You might use C OLE -- a C lib which was designed so as to
import OLE files to other formats. An application is xlHTML
(excel to HTML). I use xlHTML to import excel tables, then
convert the HTML to DBF using a perl script. For some reason,
xlHTML's author did not code a xlDBF file; and I'm not familiar
with C. I'd be interested in something better than my
solution (which I can send to you if you wish so).

xlHTML:
http://www.gate.net/~ddata/xlHtml/index.htm

Regards
Fabrice Scemama

--
"How could this be a problem in a country where we have Intel and
Microsoft?" (Al Gore, Vanity Fair, January 1999 issue, talking about
Y2K)