Rename Duplicate Fields with a Separator

This example shows how to deal with records that contain two fields with the same name, which happens when a CSV header repeats a column or a join brings in a second copy of a field. RemoveDuplicateFields resolves the duplicates with a policy. The built-in DuplicateFieldsPolicy values keep all copies, keep only the first or the last, merge the values into an array, or rename the later copies with a numeric suffix; RENAME_UNDERSCORE puts an underscore before the number so Name becomes Name_1. A RenamePolicy lets you pick any separator. The separator matters when a field name itself ends in digits: with plain RENAME a duplicate of 2010 would become the ambiguous 20101, with a separator it becomes 2010_1.

Input CSV file

The header names the Name column twice.

Account,Name,LastName,Name
101,Keanu,Reeves,Keanu Reeves
102,John,Doe,John Doe
103,Jane,Doe,Jane Doe

Java Code Listing

package com.northconcepts.datapipeline.examples.cookbook;

import java.io.File;

import com.northconcepts.datapipeline.core.DataReader;
import com.northconcepts.datapipeline.core.StreamWriter;
import com.northconcepts.datapipeline.csv.CSVReader;
import com.northconcepts.datapipeline.job.Job;
import com.northconcepts.datapipeline.transform.RemoveDuplicateFields;
import com.northconcepts.datapipeline.transform.RemoveDuplicateFields.DuplicateFieldsPolicy;
import com.northconcepts.datapipeline.transform.RemoveDuplicateFields.RenamePolicy;
import com.northconcepts.datapipeline.transform.TransformingReader;

public class RenameDuplicateFieldsWithASeparator {

    public static void main(String[] args) {
        DataReader reader = new CSVReader(new File("example/data/input/duplicate_fields.csv"))
                .setFieldNamesInFirstRow(true);

        reader = new TransformingReader(reader)
                .add(new RemoveDuplicateFields(DuplicateFieldsPolicy.RENAME_UNDERSCORE));

        Job.run(reader, StreamWriter.newSystemOutWriter());

        reader = new CSVReader(new File("example/data/input/duplicate_fields.csv"))
                .setFieldNamesInFirstRow(true);

        reader = new TransformingReader(reader)
                .add(new RemoveDuplicateFields(new RenamePolicy("-")));

        Job.run(reader, StreamWriter.newSystemOutWriter());
    }

}

Code Walkthrough

1. A CSVReader reads duplicate_fields.csv with field names in the first row, so every record has two Name fields.
2. A TransformingReader applies RemoveDuplicateFields with DuplicateFieldsPolicy.RENAME_UNDERSCORE; the second Name field is renamed Name_1. Job.run() prints the records to a StreamWriter.
3. The file is read again, this time with new RenamePolicy("-"), which renames each later duplicate to its name, the separator and the first unused number starting at 1: Name-1.

Console Output

-----------------------------------------------
0 - Record {
    0:[Account]:STRING=[101]:String
    1:[Name]:STRING=[Keanu]:String
    2:[LastName]:STRING=[Reeves]:String
    3:[Name_1]:STRING=[Keanu Reeves]:String
}

-----------------------------------------------
1 - Record {
    0:[Account]:STRING=[102]:String
    1:[Name]:STRING=[John]:String
    2:[LastName]:STRING=[Doe]:String
    3:[Name_1]:STRING=[John Doe]:String
}

-----------------------------------------------
2 - Record {
    0:[Account]:STRING=[103]:String
    1:[Name]:STRING=[Jane]:String
    2:[LastName]:STRING=[Doe]:String
    3:[Name_1]:STRING=[Jane Doe]:String
}

-----------------------------------------------
3 records
-----------------------------------------------
0 - Record {
    0:[Account]:STRING=[101]:String
    1:[Name]:STRING=[Keanu]:String
    2:[LastName]:STRING=[Reeves]:String
    3:[Name-1]:STRING=[Keanu Reeves]:String
}

-----------------------------------------------
1 - Record {
    0:[Account]:STRING=[102]:String
    1:[Name]:STRING=[John]:String
    2:[LastName]:STRING=[Doe]:String
    3:[Name-1]:STRING=[John Doe]:String
}

-----------------------------------------------
2 - Record {
    0:[Account]:STRING=[103]:String
    1:[Name]:STRING=[Jane]:String
    2:[LastName]:STRING=[Doe]:String
    3:[Name-1]:STRING=[Jane Doe]:String
}

-----------------------------------------------
3 records