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
not able to copy data from file
Moderator: NorbertKrupa
not able to copy data from file
- Attachments
-
- copy.txt
- (1.35 KiB) Downloaded 603 times
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: not able to copy data from file
What isn't working? Are you receiving an error?
Checkout vertica.tips for more Vertica resources.
Re: not able to copy data from file
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.
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: not able to copy data from file
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:
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)
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Re: not able to copy data from file
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
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
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: not able to copy data from file
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:
Are you using Windows? If so, you can download the sed utility for Windows here:
http://gnuwin32.sourceforge.net/packages/sed.htm
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)
http://gnuwin32.sourceforge.net/packages/sed.htm
Jim Knicely
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.
Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.