Filter duplicate records from csv file with condition on one column

Shell Programming and Scripting
1

I have csv file with 30, 40 columns
Pasting just three column for problem description
I want to filter record if column 1 matches CN or DN then,
check for values in column 2 if column contain 1235, 1235 then in column 3 values must be sequence of 2345, 2345
and if column 2 contains 6789, 6789 in row, then in column 3 values must be in sequence 7890, 7890
or if column 2 contains duplicate value(1234,1234) in row(1-4) in bundle, then column 3 must also contains duplicate value(4567,4567) in row(1-4)
or if column 2 contains duplicate value(5678,5678) in row(5-8) in bundle, then column 3 must also contains duplicate value(4321,4321) in row(5-8)
if combination as explained above is not present, then logs must be printed in another file with error code and line number

Sample file.

CN	1234	4567
CN	1234	4567
CN	1234	4567
CN	1234	4567
CN	5678	4321
CN	5678	4321
CN	5678	4321
CN	5678	4321
2

This is the kind of question that needs to have:
Sample good input that will not be "filtered"
Sample bad input -> expected output

Without this start we cannot help.

What code have you tried? Please show us where you are in your attempt.

3

Hi Jim,

In this problem i want to look into csn file and need to print error if combination above does not exist in any row.(No changes to be done in csv file)
i tired the below code, but not sure what to do next,

awk '{if (x[$2$3]) { x_count[$2$3]++; print $0; if (x_count[$2$3] == 1) { print x[$2$3] } } x[$2$3] = $0}'

As asked by you :
Good input will be like below :

DT	DN	ON
CN	1234	4567
CN	1234	4567
CN	1234	4567
CN	1234	4567
CN	5678	4321
CN	5678	4321
CN	5678	4321
CN	5678	4321

Bad input will be like below :(marked in red)

DT	DN    ON
CN	1234	4567
CN	1234	4567
CN	1234	4567
CN	5678	4564
CN	5678	4321
CN	5678	4564
CN	7890	7654
CN	7890	7654
CN	7890	3243
4

And what should the output look like?
Do you always need 4 rows of identical values?
I can't recognize a pattern. How do we tell correct from wrong numbers?
Will it always be those exact numbers given in post#1?

5

Taking a bit of a guess at the error message format but hopefully this is close enough for the OP to modify to their liking:

awk '
$2 in V && V[$2] != $3 {
    print "Line " NR " " $3 " <> " V[$2]
    next }
{ V[$2] = $3 }' inputfile

Output for testing data:

Line 5 4321 <> 4564
Line 9 3243 <> 7654

f you want to try this on a Solaris/SunOS system, change awk to /usr/xpg4/bin/awk or nawk .

Edit:

or another similar solution:

awk '
{ 
  if (V[$2] && V[$2] != $3 )
    print "Line " NR " " $3 " <> " V[$2]
  else V[$2] = $3
}' inputfile
6

Hi Rudic,

I don't want to modify input data in csv file and don't want output in diff file
i just want to throw/print error for the rows where condition is not met in csv file

File should contain data in two columns in below given format.
and numbers in row and column may vary.
In short if column 2 contain row(1-2) with duplicate values(1234,1234) and column 3 should also contain duplicate values(4567,4567) in row(1-2)
and false condition will be when column 2 contain duplicate value(0808,0808,0808) where in row(1-3) but column 3 does not contain duplicate value(4567,4567,1234) in rows(1-3) , where column 3 contain 1234 in row 3 which causes this condition to be false

hope im clear now
Good condition

DT	DN	ON
CN	1234	4567
CN	1234	4567
CN	9876	6543
CN	9876	6543
CN	5678	4321
CN	5678	4321
CN	0909	3089
CN	0909	3089

False condition in "red"

DT   DN     ON
CN   0808  4567
CN   0808  4567
CN   0808  1234

---------- Post updated at 03:31 AM ---------- Previous update was at 02:24 AM ----------

Hi chubler,

Could you please help me , how to execute these script.
As when i tried putting these code in .sh file then no output is coming
and when tried from command line getting syntax error at "next" command.

---------- Post updated 12-30-17 at 12:41 AM ---------- Previous update was 12-29-17 at 03:31 AM ----------

Hi chubler,

Thank you for the code, will run and test the same,
and will let you know for issue if any.

thanks :slight_smile: