Compare two csv files in groovy
<p>I am trying to create a comparison script to compare 2 SQL tables.
Each of these two tables has around 4.000 rows.</p>

<p>I used Groovy to import the tables and store them as CSV files.
For both CSVs the first column is the ID which I need to use for the comparison. </p>

<p>For every id number there might be many rows.
The number of rows for an ID in one CSV can be different for the same ID in the other CSV.</p>

<p>Eg:</p>

<pre><code>   CSV1    -     CSV2
---------  -  ----------
ID, Value  -  ID , value
1 , A      -  1  , A
1 , B      -  2  , A
2 , A      -  2  , B
3 , B      -  3  , C
</code></pre>

<p>So in the above case, I would expect an output like</p>

<pre><code>ID,Value, FoundIn
 1, A   , both
 1, B   , 1
 2, A   , both
 2, B   , 2
 3, B   , 1
 3, B   , 2
</code></pre>

<p>So far I haven't managed to come across a suitable way of doing that comparison in Groovy. </p>

<p>I have tried by nesting bash inside groovy and use diff command, as:</p>

<pre><code>"println "diff -sw /home/testFolder/file1.txt /home/testFolder/file2.txt".execute().text 
</code></pre>

<p>but diff does line-by-line comparison which in this case doesn't work.</p>

<p>Is there a better way to do this?</p>

<p>My knowledge of programming is very narrow and I would gladly use some help.
I hope I have given you enough info to understand the problem, but if not, I would be happy to provide more.</p>

<p>Thanks in advance</p>

<p>EDIT:</p>

<p>Just to make it a bit clearer</p>

<p>Eg:</p>

<pre><code>   CSV1    -     CSV2
---------  -  ----------
ID, value  -  ID , value
1 , A      -  1  , A
1 , B      -  2  , A
2 , A      -  2  , B
3 , B      -  3  , C
</code></pre>

<p>The above example only shows the supposed content of the two files. It is not the actual structure. So, I am having two different files. I only used hyphen (-) to show that they are different files. No hyphen is present in the files.
I am already having the two csv files and their content is imported from the sql tables.
The content of each csv file looks like the following:</p>

<pre><code>[id:1, value1:A, value2:B, value3:C]
[id:1, value1:A, value2:G, value3:K]
[id:2, value1:A, value2:G, value3:K]
</code></pre>

<p>Where id, value1,value2,value3<br>
it used to be the column names of the sql tables.</p>

<p>So I am having those csv files and what I need is to compare their content.</p>