To get the exact mismatches from two csv files

Shell Programming and Scripting
1

Hello Guys,
I am pretty new to unix shell scripting where in i need to compare two files which are comma separated files.

So here i go with the file contents

cty_id,grade_val,g_val_2,g_val_3
001,10,20,30
002,,,40
003,100,,10


grade_val,g_val_2,cty_id
10,20,001
41,,002
100,1,003

In both of my files cty_id is the key column where i would be searching by putting the cty_id number. For eg if i search for cty_id 001 then i should get below results

city_id 001 doest have any mismatch

if 002
Mismatch at col grade_val : Null - 41
if 003
Mismatch at col g_val : Null - 1

Like wise i need to compare based on the columns from two files and there can be mismatches in other columns for the same city_id's but need to compare only the columns which are present in both files.

I tried with below code but not able to get through

echo Please enter your cty_id:
read id
while read id; do grep "$id" file.csv; done < file_1.csv
2

Not sure I understand your intentions nor requirements.

As a starting point. this post might help to compare fields that have a common header but different position in a line.

3

Thanks RudiC for your suggestion,

What i would require is to compare the files based on the columns and extract the mismatches in below format if i input city_id.

For eg if i give 001 as input it will search in both files because the files will have only one record with 001 and compare column by column the values if they have any mismatch in column

column	         1st file	  2nd file
city_id	    1	            1
grade_val	   10	           10
g_val_2	   20	           20

There is no mismatch

city_id 001 doesn't have any mismatch
Mismatch at col grade_val : Null - 41
Mismatch at col g_val : Null - 1
4

Still some questions and guesses; but, based on that link given, try

awk '
NR == FNR       {if (NR == 1)   for (MX=n=NF; n>0; n--) REF[$n] = n
                 else           TMP[NR] = $0
                 next
                }

FNR == 1        {for (n=NF; n>0; n--)   {if ($n in REF) CMP[n]=REF[$n]
                                         if ($n == SRCH) NSR = n
                                         HD[n]  = $n
                                         NL     = "Null"
                                        }
                 next
                }

                {n = split (TMP[FNR], IT)
                 EQU = 1
                 for (i=1; i<=MX; i++)  {T = IT[CMP]
                                         if ($i != T)   {print SRCH, $NSR ": mismatch at", HD ":", $i?$i:NL, "-", T?T:NL
                                                         EQU = 0
                                                        }
                                        }
                 if (EQU) print SRCH, $NSR, "doesn�t have any mismatch."
                }


' FS="," SRCH="cty_id" file2 file1
cty_id 001 doesn�t have any mismatch.
cty_id 002: mismatch at grade_val: 40 - 41
cty_id 003: mismatch at g_val_2: Null - 1
1 Like
5

Thanks for your help RudiC, How i can filter with city_id i mean i can pass city id as input parameter to perform the mismatch

I got it i can use grep at the end

Also RudiC, can you let me know what questions you have so that i can help to sort out for you