Concatenate Field Values into a String over a CSV

Question

I have a CSV file that is ‘|’ delimited and has multiple lines in it. Some entries in the file have duplicates and I need to merge these entries into a single line. These entries will then be used to create a map object. I am new to Java and need help on how to do this. I created a map object but don’t know how to handle the duplicates. Can anyone suggest how to do this in Java?

My list:

HR |325|50051710|CN=ADGroup1

 

HR |325|50051710|CN=ADGroup2

 

BA |375|50110084|CN=ADGroup1

 

SYS ADMIN |877|50145471|CN=ADGroup2

 

Output has to be like this to be read as map object:

HR |325|50051710|CN=ADGroup1,CN=ADGroup2

 

BA |375|50110084|CN=ADGroup1

 

...

 

My code:

 

Map attrList = new HashMap();

 

 String[] record = line.split("\\|");

 

 try{

 

 br1 = new BufferedReader(new FileReader(inputFile));

 

 String line = "";

 

 while ((line = br1.readLine()) != null) {

 

 if (record[1] != null ) {

 

 Map map = new HashMap();

 

 String costcentre = record[1].trim();

 

 map.put("Costcentre", costcentre);

 

 String jobcode = record[2].trim();

 

 map.put("Jobcode", jobcode);

 

 String adgroup = record[3].trim();

 

 map.put("ADGroup", adgroup);

 

 attrList.put(costcentre + jobcode, map);

 

Answer

Some simple structured computations are needed to solve your problem: first group data by the first 3 columns and then join values column 4 up by the comma. You have to express the algorithm in a very inconvenient way because Java lacks a related class library to handle this. In this case you can get it done with esProc SPL. The Structured Process Language generates intuitive and easy to understand code:

 

A

1

=file("test.csv").import(;,"|")

2

=A1.group(_1,_2,_3;~.(_4).concat@c())

A1: Import the file by delimiter "|".

undefined

A2: Group records by the first fields and then join values of the 4th fields in each group (represented by ~) with the comma.

undefined

esProc provides JDBC interface to let a third-party program to call an SPL script in the way they call a database result set. To know more about the invocation, see How to Call an SPL Script in Java.