I have a column in my data which has both , as well as ".
E.g. Abc,"xyz"
so I'm not able to load.
Enclosed by '"' isn't helping. How can I overcome this?
Copying column which has both , and "
Moderator: NorbertKrupa
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Copying column which has both , and "
If it's just going to be a static column with double quotes, you can add column options with an ENCLOSED BY for that column.
Checkout vertica.tips for more Vertica resources.
Re: Copying column which has both , and "
Name
ABC,12
Present"X"
Lets say I have these 2 data in the column. If I Enclosed it by ' " ' then my second record is rejected. If I don't do anything (file is csv) then my first record gets rejected.
ABC,12
Present"X"
Lets say I have these 2 data in the column. If I Enclosed it by ' " ' then my second record is rejected. If I don't do anything (file is csv) then my first record gets rejected.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Copying column which has both , and "
What's the issue? The data loads fine.
Here is another example:
Code: Select all
dbadmin=> \! cat /home/dbadmin/test.txt
ABC,12
Present"X"
dbadmin=> \d test
List of Fields by Tables
Schema | Table | Column | Type | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+-------------+------+---------+----------+-------------+-------------
public | test | name | varchar(10) | 10 | | f | f |
(1 row)
dbadmin=> copy test from '/home/dbadmin/test.txt';
Rows Loaded
-------------
2
(1 row)
dbadmin=> select * from test;
name
------------
ABC,12
Present"X"
(2 rows)
Code: Select all
dbadmin=> create table test (col1 varchar(100), col2 varchar(100));
CREATE TABLE
dbadmin=> \! cat /home/dbadmin/test.txt
Abc,"xyz"
Abc,"x,yz"
ABC,12
Present"X"
dbadmin=> copy test from '/home/dbadmin/test.txt' delimiter ',' enclosed by '"' trailing nullcols;
Rows Loaded
-------------
4
(1 row)
dbadmin=> select * from test;
col1 | col2
------------+------
ABC | 12
Abc | x,yz
Abc | xyz
Present"X" |
(4 rows)
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: Copying column which has both , and "
How About this?
csv file.
55682,"Bolts, Studs Click",2,Timer
55682,"Bolts”” Studs Click",2,Timer
Enclosed by ‘”’ in copy command, rejects the second row. Removing the Enclosed by ‘”’ in the copy command, rejects the first.
csv file.
55682,"Bolts, Studs Click",2,Timer
55682,"Bolts”” Studs Click",2,Timer
Enclosed by ‘”’ in copy command, rejects the second row. Removing the Enclosed by ‘”’ in the copy command, rejects the first.
- JimKnicely
- Site Admin
- Posts: 1825
- Joined: Sat Jan 21, 2012 4:58 am
- Contact:
Re: Copying column which has both , and "
Hi,
What version of Vertica are you using?
Version 7.2.x includes a csv parser and takes all the guess work out of loading csv files!
Read about it here:
https://my.vertica.com/docs/7.2.x/HTML/ ... erence.htm
Example:
What version of Vertica are you using?
Version 7.2.x includes a csv parser and takes all the guess work out of loading csv files!
Read about it here:
https://my.vertica.com/docs/7.2.x/HTML/ ... erence.htm
Example:
Code: Select all
dbadmin=> COPY test FROM stdin PARSER fcsvparser(header='false');
Enter data to be copied followed by a newline.
End with a backslash and a period on a line by itself.
>> 55682,"Bolts, Studs Click",2,Timer
>> 55682,"Bolts”” Studs Click",2,Timer
>> \.
dbadmin=> select * from test;
c1 | c2 | c3 | c4
-------+---------------------+----+-------
55682 | Bolts, Studs Click | 2 | Timer
55682 | Bolts”” Studs Click | 2 | Timer
(2 rows)
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.
-
- GURU
- Posts: 527
- Joined: Tue Oct 22, 2013 9:36 pm
- Location: Chicago, IL
- Contact:
Re: Copying column which has both , and "
I would honestly recommend cleaning the data in a third party tool such as Talend before trying to have Vertica parse it.Beg1nner wrote:How About this?
csv file.
55682,"Bolts, Studs Click",2,Timer
55682,"Bolts”” Studs Click",2,Timer
Enclosed by ‘”’ in copy command, rejects the second row. Removing the Enclosed by ‘”’ in the copy command, rejects the first.
Checkout vertica.tips for more Vertica resources.