Postgres Pro
Downloads
Home   >   mailing lists

Thread: copy csv eclosed by analog quotes problem superuser ?

copy csv eclosed by analog quotes problem superuser ?

From
Eugene
Date:
hello!
I have IP CSV, it is huge CSV with quotes
"69110784","69111807","US","UNITED
STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701","LEVEL 3
COMMUNICATIONS INC","DSL-VERIZON.NET"
"69111808","69112831","US","UNITED
STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3
COMMUNICATIONS INC","DSL-VERIZON.NET"

In my sql there is enclosed by command, i can do this way
mysql> LOAD DATA INFILE
"<path>/IP-COUNTRY-REGION-CITY-LAT-LONG-ZIPCODE-ISP-DOMAIN.CSV" INTO TABLE
IPCITYLATLONGISPDOMAIN FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
TERMINATED BY '\r\n';

ENCLOSED BY '"' helps in mysql


1.in postgresql I can't do COPY WITH CSV, it says superuser needed
2. i can use is [\copy] but it wont help me, I get syntax error
3. i can't convert current csv into csv without quotes

it is like a death circle ...

what should I do?






--

----------------
eugene

Re: copy csv eclosed by analog quotes problem superuser ?

From
Bruce Momjian
Date:
Eugene wrote:
> hello!
> I have IP CSV, it is huge CSV with quotes
> "69110784","69111807","US","UNITED
> STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> "69111808","69112831","US","UNITED
> STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
>
> In my sql there is enclosed by command, i can do this way
> mysql> LOAD DATA INFILE
> "<path>/IP-COUNTRY-REGION-CITY-LAT-LONG-ZIPCODE-ISP-DOMAIN.CSV" INTO TABLE
> IPCITYLATLONGISPDOMAIN FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES
> TERMINATED BY '\r\n';
>
> ENCLOSED BY '"' helps in mysql
>
>
> 1.in postgresql I can't do COPY WITH CSV, it says superuser needed

Right.

> 2. i can use is [\copy] but it wont help me, I get syntax error
> 3. i can't convert current csv into csv without quotes
>
> it is like a death circle ...

Please post the line that is generating the error, and the table schema,
the COPY syntax used, and the actual error message.  Have you read the
COPY manual page?

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: copy csv eclosed by analog quotes problem superuser ?

From
"George Pavlov"
Date:
use this:

$ psql -Uyer_user -dyer_database -fyer_copy_script.sql <
yer_data_file.csv

where yer-copy-script.sql is:

-- -------------------------------------
drop table foo;

create table foo (
c01 varchar,
c02 varchar,
c03 varchar,
c04 varchar,
c05 varchar,
c06 varchar,
c07 varchar,
c08 varchar,
c09 varchar,
c10 varchar,
c11 varchar
);

\COPY foo FROM pstdin WITH csv

select * from foo;
-- -------------------------------------

if you want more control over \COPY you can issue something like this:

\COPY foo FROM pstdin WITH delimiter as ',' quote as '"' csv

although in your case that is not needed since your data file falls
under the default settings.

if your data file is truly "huge" i do recommend you use COPY instead of
\COPY because performance is better. and yes, you do have to be the
postgres user to run COPY

all of the COPY and \COPY options, by the way, are nicely explained in
the documentation.

hope this helps.

george



> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Eugene
> Sent: Saturday, December 24, 2005 4:46 AM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] copy csv eclosed by analog quotes problem
> superuser ?
>
> hello!
> I have IP CSV, it is huge CSV with quotes
> "69110784","69111807","US","UNITED
> STATES","ILLINOIS","BLOOMINGTON","40.4758","-88.9894","61701",
> "LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
> "69111808","69112831","US","UNITED
> STATES","TEXAS","GRAPEVINE","32.9309","-97.0755","76051","LEVEL 3
> COMMUNICATIONS INC","DSL-VERIZON.NET"
>
> In my sql there is enclosed by command, i can do this way
> mysql> LOAD DATA INFILE
> "<path>/IP-COUNTRY-REGION-CITY-LAT-LONG-ZIPCODE-ISP-DOMAIN.CSV
> " INTO TABLE
> IPCITYLATLONGISPDOMAIN FIELDS TERMINATED BY ',' ENCLOSED BY
> '"' LINES
> TERMINATED BY '\r\n';
>
> ENCLOSED BY '"' helps in mysql
>
>
> 1.in postgresql I can't do COPY WITH CSV, it says superuser needed
> 2. i can use is [\copy] but it wont help me, I get syntax error
> 3. i can't convert current csv into csv without quotes
>
> it is like a death circle ...
>
> what should I do?