Page 1 of 1

Copying column which has both , and "

Posted: Thu Aug 13, 2015 9:38 pm
by Beg1nner
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?

Re: Copying column which has both , and "

Posted: Fri Aug 14, 2015 3:57 am
by NorbertKrupa
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.

Re: Copying column which has both , and "

Posted: Fri Aug 14, 2015 2:07 pm
by Beg1nner
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.

Re: Copying column which has both , and "

Posted: Tue Aug 18, 2015 6:36 pm
by JimKnicely
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)

Re: Copying column which has both , and "

Posted: Tue May 17, 2016 5:41 pm
by Beg1nner
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.

Re: Copying column which has both , and "

Posted: Wed May 18, 2016 12:37 pm
by JimKnicely
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)

Re: Copying column which has both , and "

Posted: Mon May 23, 2016 3:05 am
by NorbertKrupa
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.