Handling Newline and Comma in data field of a CSV file – using an UDF

Usually Newline will be endSeparator for records in CSV. But sometimes CSV reports sent from cloud systems (Ariba, SuccessFactors) can contain Newline and Comma in data fields. This happens, when end user enters some description with ‘Enter’ or ‘,’ in it. Comma in data can be handle in FCC using enclosureSign parameter. But Newline in data cannot be handled using FCC or Standard modules.

Here an example CSV file.

Solution:-

Configure FCC or Standard module.

Map using an UDF, to split each line to corresponding field.

Testing

UDF code: – ‘Execution Type’ : ‘All Values of a Context’.

public void udf_FlatToXML(String[] eachLines, ResultList Records, ResultList Name, ResultList ID, ResultList Description, ResultList Location, Container container) throws StreamTransformationException {
    //Read input content into String.
    String input = "";
    for (String eachLine : eachLines) {
        input = input + eachLine + "\n";
    }
    input = input.replaceFirst(".*?\n", ""); //Remove header line. FYI, You can comment this line, if there is no header in input.
    //Remove \n and , when they are present in data.
    int len = input.length();        StringBuilder out = new StringBuilder();
    boolean withInQuotes = false;    char c;
    for (int i = 0; i < len; i++) {
        c = input.charAt(i);
        if (c == '"') { withInQuotes = !withInQuotes; }
        if (withInQuotes && (c == '\n' || c == ',')) { c = ' '; } //When \n or , are present with in quotes, replace them with space.
        out.append(c);
    }
    //Poupulate target elements.
    eachLines = out.toString().split("\n"); //Get each line.
    for (String eachLine : eachLines) {
        eachLine = eachLine.replace("\"", ""); //Remove all ".
        eachLine = eachLine + ",,,,--"; //Add ,,,,-- (4 target fields). This is to avoid ArrayIndexOutOfBoundsException.
        String[] value = eachLine.split(","); //Get each value.
        Records.addValue("");
        Name.addValue(value[0]);            Name.addContextChange();
        ID.addValue(value[1]);              ID.addContextChange();
        Description.addValue(value[2]);     Description.addContextChange();
        Location.addValue(value[3]);        Location.addContextChange();
    }
}

UDF