not able to copy data from file

[bhupal]

Hi Guys,

i am using .csv file to load data. it has data like

2013Q2,201306,Asia Pacific,Asia Pacific Countri,Japan,Japan,Japan,Japan Local Sales,R700,PSG Support,Ongoing Renewals,Ongoing Renewals,O,Indirect,HPS SAP PJ1 APJ,,,,N/V,N/V,N/V,5X,5X,A8685A,"Adaptor, PCI-4x, LAN, 1000Base-TX",?,N/V,N/V,JP690723697,0,5

see the column number 7 from last has " and ,

i am using the below copy command to load data. but not able to do it. please suggest me

<<See attachment for COPY command>>

i have prepared this command by seeing this post. viewtopic.php?t=284#p932

[NorbertKrupa]

What isn't working? Are you receiving an error?

[bhupal]

see tha column 7 from last has " and ,

becuase of this i am getting exception To Many Columns Found and the record has been rejected. what i want is, i want to load the data by removing this " and , from that columns if it is not possible to load the original data.

[JimKnicely]

bhupal,

NOTE: I had to edit your original post and move your COPY command text to an attachment as it was causing a weird formatting issue...

Anyway, try using the the ENCLOSED BY '"' option.

Example:

Code: Select all

dbadmin=> create table public.test (col1 int, col2 varchar(100), col3 varchar(100));
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/test.txt
1,"Adaptor, PCI-4x, LAN, 1000Base-TX",My Test
dbadmin=> copy public.test from '/home/dbadmin/test.txt' enclosed by '"' delimiter ',';
 Rows Loaded
-------------
           1
(1 row)

dbadmin=> select * from public.test;
 col1 |               col2                |  col3
------+-----------------------------------+---------
    1 | Adaptor, PCI-4x, LAN, 1000Base-TX | My Test
(1 row)

[bhupal]

Thanks Knicely.

If I try by enclosed by '"' some records are inserting into table but again I got some rejected records. Saying two many column found.

Data is like
1,"Adaptor, PCI-4x, LAN, 17"" 1000Base-TX",My Test

So what should I do for loading this data. If u have any solution with replacing the " and , also no problem. But it is great if I load data as it is.

Thanks
Ram

[JimKnicely]

Hi,

Are you okay with removing all "" (2 double quotes next to each other) from your file? If so, you can use the sed command to replace them with a null value (or some other value).

Example:

Code: Select all

dbadmin=> \! cat /home/dbadmin/test.txt
1,"Adaptor, PCI-4x, LAN, 1000Base-TX",My Test
1,"Adaptor, PCI-4x, LAN, 17"" 1000Base-TX",My Test

dbadmin=> \! cp /home/dbadmin/test.txt /home/dbadmin/test.txt.backup

dbadmin=> \! sed -i 's/""//g' /home/dbadmin/test.txt

dbadmin=> \! cat /home/dbadmin/test.txt
1,"Adaptor, PCI-4x, LAN, 1000Base-TX",My Test
1,"Adaptor, PCI-4x, LAN, 17 1000Base-TX",My Test

dbadmin=> create table public.test (col1 int, col2 varchar(100), col3 varchar(100));
CREATE TABLE

dbadmin=> copy public.test from '/home/dbadmin/test.txt' enclosed by '"' delimiter ',';
 Rows Loaded
-------------
           2
(1 row)

dbadmin=> select * from public.test;
 col1 |                 col2                 |  col3
------+--------------------------------------+---------
    1 | Adaptor, PCI-4x, LAN, 17 1000Base-TX | My Test
    1 | Adaptor, PCI-4x, LAN, 1000Base-TX    | My Test
(2 rows)

Are you using Windows? If so, you can download the sed utility for Windows here:

http://gnuwin32.sourceforge.net/packages/sed.htm