Re: Excell

Hi All

Is there a fast and easy method of transferring information between MS Excel and PostgreSQL??

Bob Pawley

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/

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

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" <david(at)gardnerit(dot)net>
To: "Postgresql" <pgsql-general(at)postgresql(dot)org>
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
>

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" <david(at)gardnerit(dot)net>
> To: "Postgresql" <pgsql-general(at)postgresql(dot)org>
> 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

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

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

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.

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.

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.

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/

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

Yes please send me a copy.

Bob

----- Original Message -----
From: "Harvey, Allan AC" <HarveyA(at)OneSteel(dot)com>
To: "Joshua D. Drake" <jd(at)commandprompt(dot)com>; "Scott Marlowe"
<smarlowe(at)g2switchworks(dot)com>
Cc: "Csaba Nagy" <nagy(at)ecircle-ag(dot)com>; "David Gardner"
<david(at)gardnerit(dot)net>; "Postgres general mailing list"
<pgsql-general(at)postgresql(dot)org>
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
>