Обсуждение: Table to Excel

Hi , <br /><br /> Does any one how to convert table to excel.<br /> <br /> I just tried the COPY table_name to
'/home/user/output.xls'using delimiters ',';<br /><br />but the out put of the table that is the column in a table are
tabseparted, all were merged in a single cell.<br /><br />-Nicholas I<br /> 

hello

use csv format - excel have to read this format without any format

COPY tablename TO '...' CSV;

regards
Pavel Stehule

2009/3/26 Nicholas I <nicholas.domnic.i@gmail.com>:
> Hi ,
>
>  Does any one how to convert table to excel.
>
>  I just tried the COPY table_name to '/home/user/output.xls' using
> delimiters ',';
>
> but the out put of the table that is the column in a table are tab separted,
> all were merged in a single cell.
>
> -Nicholas I
>

On Thu, 26 Mar 2009 08:04:25 +0100
Pavel Stehule <pavel.stehule@gmail.com> wrote:

> hello
> 
> use csv format - excel have to read this format without any format
> 
> COPY tablename TO '...' CSV;

Another possibility that I use often is to set the output to HTML
mode using:

\H

Then set to send query results to a file:

\o myresults.html

Then run a single query:

SELECT col1, col2 FROM foo WHERE bar = '1' ORDER BY baz;

\q

Now you can open your .html file directly into Excel since it's just
a big html table.

Josh

2009/3/26 Josh Trutwin <josh@trutwins.homeip.net>:
> On Thu, 26 Mar 2009 08:04:25 +0100
> Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>> hello
>>
>> use csv format - excel have to read this format without any format
>>
>> COPY tablename TO '...' CSV;
>
> Another possibility that I use often is to set the output to HTML
> mode using:
>
> \H
>
> Then set to send query results to a file:
>
> \o myresults.html
>
> Then run a single query:
>
> SELECT col1, col2 FROM foo WHERE bar = '1' ORDER BY baz;
>
> \q
>
> Now you can open your .html file directly into Excel since it's just
> a big html table.
>

It is true, so you can use html, but html has significant overhead -
CVS is really simple format - designed for this case.

Pavel

> Josh
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

2009/3/26 Nicholas I <nicholas.domnic.i@gmail.com>:
> Hi ,
>
>  Does any one how to convert table to excel.
>
>  I just tried the COPY table_name to '/home/user/output.xls' using
> delimiters ',';
>
> but the out put of the table that is the column in a table are tab separted,
> all were merged in a single cell.
>
> -Nicholas I
>

I find it easier to use the ODBC Driver (available at
http://pgfoundry.org/projects/psqlodbc/) and query the database from
Excel.  This way you get updateable results (right click refresh data)
with the option of modifying your query from within Excel.

--
THINK BEFORE YOU PRINT - Save paper if you don't really need to print this.

On 2009-03-26, Nicholas I <nicholas.domnic.i@gmail.com> wrote:
> --002215046d3b27679c0465ffd2cf
> Content-Type: text/plain; charset=ISO-8859-1
> Content-Transfer-Encoding: 7bit
>
> Hi ,
>
>  Does any one how to convert table to excel.
>
>  I just tried the COPY table_name to '/home/user/output.xls' using
> delimiters ',';

use CSV, be sure to select the correct types for each column when loading
the CSV