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 "|".
A2: Group records by the first fields and then join values of the 4th fields in each group (represented by ~) with the comma.
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.
SPL Official Website 👉 https://www.scudata.com
SPL Feedback and Help 👉 https://www.reddit.com/r/esProc_SPL
SPL Learning Material 👉 https://c.scudata.com
SPL Source Code and Package 👉 https://github.com/SPLWare/esProc
Discord 👉 https://discord.gg/cFTcUNs7
Youtube 👉 https://www.youtube.com/@esProc_SPL