Regex to remove line break within double quote in CSV

Question

Hi I have a csv file with an error in it.so i want it to correct with regular expression, some of the fields contain line break, Example as below

"AHLR150","CDS","-1","MDCPBusinessRelationshipID",,,"Investigating","1600 Amphitheatre Pkwy

California",,"Mountain View",,"United States",,"California",,,"94043-1351","9958"

the above two lines should be in one line

"AHLR150","CDS","-1","MDCPBusinessRelationshipID",,,"Investigating","1600 Amphitheatre PkwyCalifornia",,"Mountain View",,"United States",,"California",,,"94043-1351","9958"

I tried to use the below regex but it didnt help me

%s/\\([^\"]\\)\\n/\\1/

Line breaks inside double quotes are legal in CSV (at least, in the most common dialects, as there is no single standard). This is the most common way if line breaks need to be included in a field. You are mutilating Google's address by just pasting those lines together. — Thomas, Jul 13 '20 at 09:04

DigitShifter · Answer 1 · 2020-08-14T17:58:58.307

Try this:

public static void main(String[] args) {
    String input = "\"AHLR150\",\"CDS\",\"-1\",\"MDCPBusinessRelationshipID\","
            + ",,\"Investigating\",\"1600 Amphitheatre Pkwy\n"
            + "California\",,\"Mountain View\",,\"United\n"
            + "States\",,\"California\",,,\"94043-1351\",\"9958\"\n";

    Matcher matcher = Pattern.compile("\"([^\"]*[\n\r].*?)\"").matcher(input);
    Pattern patternRemoveLineBreak = Pattern.compile("[\n\r]");

    String result = input;
    while(matcher.find()) {
        String quoteWithLineBreak = matcher.group(1);
        String quoteNoLineBreaks = patternRemoveLineBreak.matcher(quoteWithLineBreak).replaceAll(" ");
        result = result.replaceFirst(quoteWithLineBreak, quoteNoLineBreaks);
    }

    //Output
    System.out.println(result);
}

Output:

"AHLR150","CDS","-1","MDCPBusinessRelationshipID",,,"Investigating","1600 Amphitheatre Pkwy California",,"Mountain View",,"United States",,"California",,,"94043-1351","9958"

Milton Castro · Answer 2 · 2022-10-13T19:13:35.637

Create a RegEx surrounding the text you want to keep by parentheses and that will create a group of matched characters. Then replace the string using the group index to compose as you wish.

String test = "\"AHLR150\",\"CDS\",\"-1\",\"MDCPBusinessRelationshipID\","
        + ",,\"Investigating\",\"1600 Amphitheatre Pkwy\n"
        + "California\",,\"Mountain View\",,\"United\n"
        + "States\",,\"California\",,,\"94043-1351\",\"9958\"\n";
    
System.out.println(test.replaceAll("(\"[^\"]*)\n([^\"]*\")", "$1$2"));

So when we replace the matching string ("United\nStates") by $1$2 we are removing the line break because it not belongs to any group:

$1 => the first group (\"[^\"]*) that will match "United
$2 => the second group ([^\"]*\")" that will match States"

score -1 · Answer 3 · answered Jul 13 '20 at 09:00

-1

Based on this you can try with:

/\r?\n|\r/

I checked it here and seems to be fine

answered Jul 13 '20 at 09:00

user12176589

107
1

They only want to remove line breaks within quoted strings, not _all_ line breaks. – Thomas Jul 13 '20 at 09:05
@user12176589 it is not working as i chcked in the link u provided and i only want to remove \r\n inside double quotes – Rohit Kumar Jul 13 '20 at 11:21

Regex to remove line break within double quote in CSV

3 Answers3