Обсуждение: 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? -- ---------------- eugene
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
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?