Copying column which has both , and "

Moderator: NorbertKrupa

Post Reply
Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Copying column which has both , and "

Post by Beg1nner » Thu Aug 13, 2015 9:38 pm

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?

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Copying column which has both , and "

Post by NorbertKrupa » Fri Aug 14, 2015 3:57 am

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.

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Re: Copying column which has both , and "

Post by Beg1nner » Fri Aug 14, 2015 2:07 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Copying column which has both , and "

Post by JimKnicely » Tue Aug 18, 2015 6:36 pm

What's the issue? The data loads fine.

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)
Here is another example:

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Beg1nner
Newbie
Newbie
Posts: 20
Joined: Fri Jul 24, 2015 8:46 pm

Re: Copying column which has both , and "

Post by Beg1nner » Tue May 17, 2016 5:41 pm

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.

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Copying column which has both , and "

Post by JimKnicely » Wed May 18, 2016 12:37 pm

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:

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

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: Copying column which has both , and "

Post by NorbertKrupa » Mon May 23, 2016 3:05 am

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.
I would honestly recommend cleaning the data in a third party tool such as Talend before trying to have Vertica parse it.
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “Vertica Data Load”