DEV Community

Judy
Judy

Posted on

Compare large csv files #eg38

Here are two csv files (A and B) of same structure. Both use KEY_A, KEY_B and KEY_C as the primary key, and the two have different records.

Image description
Use Java to compare the two files in three ways and write the results respectively to new csv files. 1. Find records where primary keys are equal and values of the other fields are not equal, output their primary keys and then the other fields of A and the other fields in B. Below is the expected result:

Image description

  1. Find difference of A and B, that is, records existing in A but not existing in B, according to the primary key. Below is the expected result:

Image description

  1. Find difference of B and A according to the primary key. Below is the expected result:

Image description
Write SPL code to do the three comparisons. Below is for the 1st comparison:

Image description

Image description

Image description
T()function parses a csv file or write data to a csv file; @c option enables using the cursor to retrieve data from a file that cannot fit into the memory. sortx() function sorts data in a cursor. joinx()function performs a merge-join. merge() function merges records; @d option enables finding the diffefrence.

Read How to Call a SPL Script in Java to find how to integrate SPL into a Java application.

This is one of the problems on StackOverflow. You can click on it to see that the conventional solution is quite complicated, but the SPL approach is really simple and efficient.

SPL open source address

Top comments (2)

Collapse
 
stephgrino profile image
Stephane Pellegrino

Really interesting, thanks...
And your opnion about SPL vs Sqlite for instance ?

Collapse
 
esproc_spl profile image
Judy

Thank you for your attention.
You can check out this article:esProc SPL, the challenger of SQLite
Feel free to talk to me if you have any questions later.