[GENERAL] Excell
Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? Bob Pawley
Re: [GENERAL] Excell
Bob Pawley wrote: Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? odbc? Joshua D. Drake Bob Pawley -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Excell
Agreed ODBC is the way to go, depending on what you are doing, Access may be helpfull as an intermediate step. Joshua D. Drake wrote: Bob Pawley wrote: Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? odbc? Joshua D. Drake Bob Pawley ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Excell
Thanks Does one version of ODBC work for all versions of Excel and Postgresql. I am wanting to transfer one or two tables from Excel and manipulate the information in Postgresql then transfer the results back to Excel as a single table. I am using Excel 2000 and PostgreSql 8.1. Bob - Original Message - From: "David Gardner" <[EMAIL PROTECTED]> To: "Postgresql" Sent: Tuesday, June 19, 2007 4:56 PM Subject: Re: [GENERAL] Excell Agreed ODBC is the way to go, depending on what you are doing, Access may be helpfull as an intermediate step. Joshua D. Drake wrote: Bob Pawley wrote: Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? odbc? Joshua D. Drake Bob Pawley ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Excell
It should work, but you could post your question on pgsql-odbc and get an answer from someone who has more experience with Excel and PostgreSQL via the ODBC driver. Bob Pawley wrote: Thanks Does one version of ODBC work for all versions of Excel and Postgresql. I am wanting to transfer one or two tables from Excel and manipulate the information in Postgresql then transfer the results back to Excel as a single table. I am using Excel 2000 and PostgreSql 8.1. Bob - Original Message - From: "David Gardner" <[EMAIL PROTECTED]> To: "Postgresql" Sent: Tuesday, June 19, 2007 4:56 PM Subject: Re: [GENERAL] Excell Agreed ODBC is the way to go, depending on what you are doing, Access may be helpfull as an intermediate step. Joshua D. Drake wrote: Bob Pawley wrote: Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? odbc? Joshua D. Drake Bob Pawley ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- David Gardner ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Excell
On Wednesday 20 June 2007 03:09, Bob Pawley wrote: > Thanks > > Does one version of ODBC work for all versions of Excel and Postgresql. > > I am wanting to transfer one or two tables from Excel and manipulate the > information in Postgresql then transfer the results back to Excel as a > single table. > > I am using Excel 2000 and PostgreSql 8.1. > Save as CSV from Excel and use Posrgres 'copy' command to import the data, would be a straightforward solution, works both ways -copy to, copy from. http://www.postgresql.org/docs/8.2/static/sql-copy.html Regards Garry ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Excell
Bob Pawley napisał(a): Thanks Does one version of ODBC work for all versions of Excel and Postgresql. I am wanting to transfer one or two tables from Excel and manipulate the information in Postgresql then transfer the results back to Excel as a single table. I am using Excel 2000 and PostgreSql 8.1. If this is a one-time or infrequent opreration then the round-trip of Excel->Access->ODBC->PostgreSQL is the way to go. I do this way usually. But when you need to process your data in this way more frequently then you'll probably start looking for something more sophisticated, involving much less clicking or drag'n dropping. If you are familiar with Perl then using: http://search.cpan.org/~timb/DBI-1.57/DBI.pm http://search.cpan.org/~hmbrand/Spreadsheet-Read-0.22/Read.pm http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.18/lib/Spreadsheet/WriteExcel.pm is the way to go and I do this way when I need to update and process sets of data provided to me as Excel spreadsheets. However, this usually involves tailoring a separate solution for each individual data-processing case. Irek. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Excell
David Gardner wrote: Agreed ODBC is the way to go, depending on what you are doing, Access may be helpfull as an intermediate step. Joshua D. Drake wrote: Bob Pawley wrote: Hi All Is there a fast and easy method of transferring information between MS Excel and PostgreSQL?? odbc? Another option is to use your favorite scripting language and throw an excel header then the data in tab delimited format. Or even in excel xml format. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Excell
On Thu, 2007-06-21 at 16:45, Scott Marlowe wrote: > Another option is to use your favorite scripting language and throw an > excel header then the data in tab delimited format. Or even in excel > xml format. Why would you need any scripting language ? COPY supports CSV output pretty well, it can even put you a header on the top. And 8.2 can COPY a SELECT too, so you can generate the CSV dump directly from joins too... on older systems I usually create a temporary table as the result of the join and then COPY that - plain SQL is all you need... I always did it this way when it comes to occasional data from/to excel. Now if it is about regularly exchanging data with excel, possibly using excel as a DB interface, probably ODBC is the only viable choice, but if the OP really needs a DB for the data, I would say using excel as the interface to it is likely a bad decision... Cheers, Csaba. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Excell
Csaba Nagy wrote: On Thu, 2007-06-21 at 16:45, Scott Marlowe wrote: Another option is to use your favorite scripting language and throw an excel header then the data in tab delimited format. Or even in excel xml format. Why would you need any scripting language ? COPY supports CSV output pretty well, it can even put you a header on the top. Because I'm delivering reports to dozens of people who have windows, no psql client, and just want to go to a web page, click a button, and get their report (or was that a banana?) I guess I could give them an account on the reporting server and a copy of pgadmin or something, but most of them are happier with a web page and a set of buttons. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Excell
Scott Marlowe wrote: Csaba Nagy wrote: On Thu, 2007-06-21 at 16:45, Scott Marlowe wrote: Another option is to use your favorite scripting language and throw an excel header then the data in tab delimited format. Or even in excel xml format. Why would you need any scripting language ? COPY supports CSV output pretty well, it can even put you a header on the top. Because I'm delivering reports to dozens of people who have windows, no psql client, and just want to go to a web page, click a button, and get their report (or was that a banana?) If you make psql with the \H option output html, excel will open it directly. I guess I could give them an account on the reporting server and a copy of pgadmin or something, but most of them are happier with a web page and a set of buttons. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Excell
> > Because I'm delivering reports to dozens of people who have windows, no > > psql client, and just want to go to a web page, click a button, and get > > their report (or was that a banana?) I do exactly this with bog basic HTML and bash scripts. Can send you a copy if you want examples. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Excell
Yes please send me a copy. Bob - Original Message - From: "Harvey, Allan AC" <[EMAIL PROTECTED]> To: "Joshua D. Drake" <[EMAIL PROTECTED]>; "Scott Marlowe" <[EMAIL PROTECTED]> Cc: "Csaba Nagy" <[EMAIL PROTECTED]>; "David Gardner" <[EMAIL PROTECTED]>; "Postgres general mailing list" Sent: Thursday, June 21, 2007 9:01 PM Subject: Re: [GENERAL] Excell > Because I'm delivering reports to dozens of people who have windows, no > psql client, and just want to go to a web page, click a button, and get > their report (or was that a banana?) I do exactly this with bog basic HTML and bash scripts. Can send you a copy if you want examples. Allan The material contained in this email may be confidential, privileged or copyrighted. If you are not the intended recipient, use, disclosure or copying of this information is prohibited. If you have received this document in error, please advise the sender and delete the document. Neither OneSteel nor the sender accept responsibility for any viruses contained in this email or any attachments. ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
RE: [GENERAL] excell to postgres
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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED]]On > Behalf Of Hal Snyder > Sent: Monday, April 17, 2000 4:41 PM > To: [EMAIL PROTECTED] > Subject: Re: [GENERAL] excell to postgres > > > Jeffrey <[EMAIL PROTECTED]> 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. >
Re: [GENERAL] excell to postgres
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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED]]On > > Behalf Of Fabrice Scemama > > Sent: Monday, April 17, 2000 12:22 PM > > To: Martin A. Marques > > Cc: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] > > > Santa Fe - Argentinahttp://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)
