Question

Source: https://stackoverflow.com/questions/67559645/custom-sort-2-csv-files-with-2-columns-and-print-differences-in-java

I have two CSV files as follows:

csv1:

101,101-1,400.01,500.01

101,101-2,400.02,500.01

102,102-1,600.01,700.01

102,102-2,600.02,700.02

csv2:

101,101-1,400.02,500.01

101,101-2,400.02,500.01

102,102-1,600.01,700.02

102,102-2,600.02,700.07

I want to store the data in Java collection in such a way I can compare column C and D of both CSVs and print the differences for each id.

Desired output:

difference of column C of id 101 and sub id 101-1 is : 0.01

difference of column D of id 101 and sub id 101-1 is : 0.00

difference of column C of id 102 and sub id 102-1 is : 0.00

difference of column D of id 102 and sub id 102-1 is : 0.01

and so on.

I have tried using Map<Integer,List<Map<String,List>>> but it is getting too complex and time-consuming. Please suggest a better way to get the result using Java.

Answer

To finish your task, we need to sort each CSV file by two specific columns in same order, calculate the difference of corresponding values in another column, and output the absolute value. It is rather complicated to get this done in Java.

I suggest you using SPL to do this. It is an open-source Java package, and you just need several lines of code, as shown below:

SPL offers JDBC driver to be invoked by Java. Just store the above SPL script as diff.splx and invoke it in Java as you call a stored procedure:

…

Class.forName("com.esproc.jdbc.InternalDriver");

con= DriverManager.getConnection("jdbc:esproc:local://");

st=con.prepareCall("call diff()");

st.execute();

…

View SPL source code.