0

List1:

1,name1                   
2,name2                    
3,name3                  
4,name4                   
5,name5                  
6,name6                  
7,name7                  
8,name8                  
9,name9                  

List2:

3
13
14
6
1
7
15
26

I need output like:

2,name
4,name
5,name
8,name
9,name

The output should contain items from column #1 of List1 which are not in List2.

List1 and List2 are not sorted but I will sort both lists if required.
List1 has around 60,000 records and List2 contains 20,0000. Need solution by using Linux shell or mysql.

4

Assuming bash is used (otherwise, pre-sort the input files with sort -o List1 List1 and sort -o List2 List2 and use List1 List2 instead of the process substitutions):

$ join -t, -v1 <( sort List1 ) <( sort List2 )
2,name2
4,name4
5,name5
8,name8
9,name9

This does a relational JOIN operation between your two files, sorting their contents first. The -t, tells join to treat commas as the field delimiter, and with -v1 we ask for the entries in the first file that does not join with any entry in the second. The join happens by default on the first field of each file.

To strip off the last integer from this:

join -t, -v1 <( sort List1 ) <( sort List2 ) | sed 's/[0-9]*$//'

For a MySQL solution, you would have had to provide the SQL schema for the involved tables, but the resulting query would have looked something like

SELECT * FROM table1 WHERE joinfield1 NOT IN (SELECT joinfield2 FROM table2);
  • I tried sorting both files but it is giving result as list 1 without excluding list2 records – kcmakwana Jul 21 '18 at 8:08
  • Mysql solution tried,it still running for last 7 hours. May be because list 2 contains 20 lakhs records – kcmakwana Jul 21 '18 at 8:09
  • @kcmakwana If the data looks exactly as in you question, and if you ran the join command exactly as I wrote it, it will work. Let me know if the data has some other format. As for the MySQL query, it will depend on your schema what the actual query should be and you would need the appropriate indexes for making it run faster. – Kusalananda Jul 21 '18 at 8:22
0

Using grep:

grep -wvf List2 List1

Output:

2,name2
4,name4
5,name5
8,name8
9,name9
0

Try this,

awk -F ',' 'NR==FNR{a[$1]++;next};a[$1] == 0'  List2  List1

2,name2                    
4,name4                   
5,name5                  
8,name8                  
9,name9
  • a[$1]++; next : it saves the 1st field in an array. Then,
  • a[$1] == 0: the else block will only be executed if this is the second file so we check whether field 1 of this file has already been seen (a[$1]==0).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.