Обсуждение: excell to postgres
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 -----------------------------------------------------------------
"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
"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
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
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: > "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 >
[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
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 >
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)