Обсуждение: Extracting data into .csv format

Поиск
Список
Период
Сортировка

Extracting data into .csv format

От
"Ferrell, Denise D CTR NSWCDD, H11"
Дата:
Using PostgreSQL 9.3 on Linux

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different
fromOracle.  Does anyone have any suggestions? 

Thank you in advance.
Denise

Вложения

Re: Extracting data into .csv format

От
Vivekanand Joshi
Дата:
Please read this: 

http://www.postgresql.org/docs/9.4/static/sql-copy.html



-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferrell, Denise D CTR
NSWCDD,H11
 
Sent: Wednesday, February 17, 2016 10:19 PM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Extracting data into .csv format

Using PostgreSQL 9.3 on Linux 

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different
fromOracle.  Does anyone have any suggestions?
 

Thank you in advance.
Denise

Re: Extracting data into .csv format

От
Stephen Tahmosh
Дата:
The "Copy" statement (and \copy for local access

COPY (
select * from (.........
) TO ' || '''' || filname || '''' || ' with csv header'


STeve

-----Original Message-----
From: pgsql-admin-owner@postgresql.org [mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Ferrell, Denise D CTR
NSWCDD,H11
 
Sent: Wednesday, February 17, 2016 11:49 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Extracting data into .csv format

Using PostgreSQL 9.3 on Linux

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different
fromOracle.  Does anyone have any suggestions?
 

Thank you in advance.
Denise
THIS MESSAGE (AND ALL ATTACHMENTS) IS INTENDED FOR THE USE OF THE PERSON OR ENTITY TO WHOM IT IS ADDRESSED AND MAY
CONTAININFORMATION THAT IS PRIVILEGED, CONFIDENTIAL AND EXEMPT FROM DISCLOSURE UNDER APPLICABLE LAW. If you are not the
intendedrecipient, your use of this message for any purpose is strictly prohibited. If you have received this
communicationin error, please delete the message without making any copies and notify the sender so that we may correct
ourrecords. Thank you.
 

Re: Extracting data into .csv format

От
"David G. Johnston"
Дата:
On Wed, Feb 17, 2016 at 9:49 AM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL 9.3 on Linux

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle.  Does anyone have any suggestions?

In the documentation lookup the psql "\copy" meta-command along with the SQL COPY command.​

David J.

Re: Extracting data into .csv format

От
Ziggy Skalski
Дата:
On 2016-02-17 11:49 AM, Ferrell, Denise D CTR NSWCDD, H11 wrote:
Using PostgreSQL 9.3 on Linux 

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle.  Does anyone have any suggestions?

Thank you in advance.
Denise
Have you tried something like i.e.:

psql -c "COPY ( QUERY ) TO STDOUT WITH CSV HEADER " > CSV_FILE.csv

Should do the trick (assuming it's basic output you're after).

Regards,

Ziggy

Re: Extracting data into .csv format

От
Shreeyansh Dba
Дата:
On Wed, Feb 17, 2016 at 10:19 PM, Ferrell, Denise D CTR NSWCDD, H11 <denise.ferrell.ctr@navy.mil> wrote:
Using PostgreSQL 9.3 on Linux

I'm trying to extract data into a comma delimited format and not having much success...seems to act totally different from Oracle.  Does anyone have any suggestions?

Thank you in advance.
Denise

Hi Denise, 

Please read below official documentation of copy command:


 Or check below tutorial


Hope this will solve your problem.
--
Thanks & Regards,
Harshad Adalkonda
Database Administrator
www.shreeyansh.com

Re: Extracting data into .csv format

От
Pierre Chevalier Géologue
Дата:
Hi,

Le 17/02/2016 17:49, Ferrell, Denise D CTR NSWCDD, H11 a écrit :
> Using PostgreSQL 9.3 on Linux
>
> I'm trying to extract data into a comma delimited format and not having
>much success...seems to act totally different from Oracle.  Does anyone
>have any suggestions?

Just in case it helps you, I wrote a pair of very simple scripts, but
they have eased my life a lot:

* sql2csv:
https://github.com/pierrechtux/geolllibre/blob/master/sql2csv

sql2csv takes an SQL query as an argument, and spits out the result in
CSV. Just redirecting its output (>) makes a .csv file.
It is extremely trivial: just this is relevant:
echo "COPY ($1) TO stdout WITH CSV HEADER FORCE QUOTE *" | psql -X -h
$GLL_BD_HOST $GLL_BD_NAME



* csv2sql:
https://github.com/pierrechtux/geolllibre/blob/master/csv2sql

csv2sql works the other way: you give him a .csv file, and it dumps it
into your database, in a 'tmp_imports' schema (makes it easy for
cleaning...).



These utility scripts are very rough: all fields are considered as
strings (varchar), to be as generic as possible.

I use these 2 utilities in production daily, for a number of purposes,
including heavy diff'ing. So far, no worries, they proved to be very
reliable,

There are a couple of environment variables that should be just adapted
for a generic use: $GLL_BD_HOST and $GLL_BD_NAME.

My little 0.02€...

À+
Pierre
--
____________________________________________________________________________
Pierre Chevalier
PChGEI: Pierre Chevalier Géologue Et Informaticien
Partenaire DALIBO
     Mesté Duran
     32100 Condom
   Tél+fax  :    09 75 27 45 62
                 06 37 80 33 64
   Émail  :   pierrechevaliergeolCHEZfree.fr
   icq#   :   10432285
   jabber: pierre.chevalier1967@jabber.fr
   http://pierremariechevalier.free.fr/pierre_chevalier_geologue
____________________________________________________________________________