Replacing characters inside the quoted string

Question

As part of my project, I have a CSV file which has comma separated values. But there are few fields which are in quotes. As a result the data is not loaded correctly. For example: if the data is ==>> car, deer, "bear, cat"

In the above example, ideally there should be 3 columns. But its being treated as 4 columns due to the presence of a comma between rat and mat. The field "rat,mat" is not clubbed and considered as a single field.

Please suggest if there is something in PIG to handle such scenarios

you want the final output something like this col1=car, col2=deer and col3="bear,cat" ? — Sivasakthi Jayaraman, Dec 05 '14 at 05:33

score 1 · Answer 1 · answered Dec 05 '14 at 05:31

1

you can use apache CSV library to handle this

answered Dec 05 '14 at 05:31

Patrick Chan

1,019
10
14

score 0 · Accepted Answer · edited May 23 '17 at 10:33

0

You can try out this regex:

have a look here, already ask on SO, very well explained by the user.

click here

edited May 23 '17 at 10:33

Community

1
1

answered Dec 05 '14 at 05:26

Ankur Singhal

26,012
16
82
116

score 0 · Answer 3 · answered Dec 05 '14 at 05:54

Can you try this?
input.csv

car,deer,"bear,cat"
car,deer,"bear,cat"

PigScript: output format1:

A = LOAD 'input.csv' AS line;
B = FOREACH A GENERATE FLATTEN(REGEX_EXTRACT_ALL(line,'(\\w+),(\\w+),(.*)$')) AS (col1:chararray,col2:chararray,col3:chararray);
DUMP B;

Output:

(car,deer,"bear,cat")
(car,deer,"bear,cat")

PigScript Output format2:

A = LOAD 'input.csv' AS line;
B = FOREACH A GENERATE FLATTEN(REGEX_EXTRACT_ALL(line,'(\\w+),(\\w+),"(\\w+),(.*)"$')) AS (col1:chararray,col2:chararray,col3:chararray,col4:chararray);
DUMP B;

Output:

(car,deer,bear,cat)
(car,deer,bear,cat)

score -1 · Answer 4 · answered Dec 05 '14 at 05:39

-1

     public static void main(String[] args) {
        // TODO code application logic here
        String str = "c,b,\"c,d\"";
        System.out.println(str);
           if(str.contains("\"")){
              str= str.replaceAll("\"", " ");
              //System.out.println(str);
              str = str.replaceAll(" ", "");
              System.out.println(str);

           }
    }

enter image description here

answered Dec 05 '14 at 05:39

Az.MaYo

1,044
10
23

this looks like it will not work for any content that contains quotes AND spaces – Matt Coubrough Dec 05 '14 at 05:59
Thanks for your comment but I have tested above code :) – Az.MaYo Dec 05 '14 at 06:06
Looks like your test coverage is wrong then :). A CSV file with this line `c,a,"c a,b"` should yield 3 distinct elements: `"c", "a", "c a,b". http://en.wikipedia.org/wiki/Comma-separated_values – Matt Coubrough Dec 05 '14 at 06:21

Replacing characters inside the quoted string

4 Answers4