Shell Programming and Scripting

It is hard to get your requirements when you change them every time you add a new post to this thread. And, the requirements still are not clear.

Until post #12 in this thread we didn't know the type of the key field in your input files (and, in post #12, it still isn't stated; we just have to assume that that field is a numeric rather than a string value because the sample data shown is out of order for a string value). Until post #12, we also did not know that the key field in your input files could not only be missing from either file, but can also be duplicated in file 1.

Can key values also be duplicated in file 2?

If there can be duplicates in both files, what output is supposed to be produced? For example, a key exists two times in file 1 and three times in file 2, are there supposed to be six output records (a cross product of the input records) or are the first records with a key from each file matched, the 2nd records with that key matched, and the unmatched record with that key from file 2 written with empty values for the missing file 1 record data?

I think we are all assuming that both input files have been sorted on your key field (and the examples shown support this assumption), but your stated requirements leave this as an assumption; not something that anyone trying to meet your requirements can depend upon.

@Don Cragun:

Sorry for changing the requirement from the initial one. Actually the key values can be duplicate only in file 1 but not file 2. Also the key value in file 1 and file 2 would be sorted numerically. If you are familiar with Database joins, I'm looking for left outer join between File 1 and File 2 but not cross product. Let me give some simple examples that would give you the better understanding of my requirement:

File 1

Code:

EmpID,EmpName,DeptID,EmpSal
1,AAA,10,100
2,BBB,20,200
3,CCC,30,300
3,DDD,40,400
5,EEE,50,500
6,FFF,60,600
8,GGG,70,700

File 2:

Code:

EmpID,EmpDOB,EmpHireDate,Active
1,Jun-04-1986,2012-03-23 12:40:00 PM,Y
2,Apr-12-1991,2010-05-12 08:50:00 PM,N
3,Dec-31-1978,2010-01-08 12:00:00 AM,Y
6,Mar-09-1989,2010-05-08 06:45:00 PM,N

Output:

Code:

EmpID,EmpName,DeptID,EmpSal,EmpID,EmpDOB,EmpHireDate,Active
1,AAA,10,100,Jun-04-1986,2012-03-23 12:40:00 PM,Y
2,BBB,20,200,Apr-12-1991,2010-05-12 08:50:00 PM,N
3,CCC,30,300,Dec-31-1978,2010-01-08 12:00:00 AM,Y
3,DDD,40,400,Dec-31-1978,2010-01-08 12:00:00 AM,Y
5,EEE,50,500,,
6,FFF,60,600,Mar-09-1989,2010-05-08 06:45:00 PM,N
8,GGG,70,700,,

Thanks in advance

OK. So just to be sure I understand:

Noting that the requested output in post #16 in this thread:
has eight fields in the header line (including two occurrences of EmpID, seven fields in the lines where the EmpID appears in both input files, and six fields in the lines where the given EmpID does not appear in File 2 and making the not too wild assumptions that EmpID should only appear in the output header line once and that all output lines should contain seven fields; you might want to try something like:

Code:

awk '
BEGIN {	FS = OFS = ","
}
NR == 1 {
	for(i = 2; i <= NF; i++)
		nm = nm OFS
}
FNR == NR {
	id = $1
	$1 = ""
	d[id] = $0
	next
}
{	print $0 (($1 in d) ? d[$1] : nm)
}' "File 2" "File 1"

which produces the output:

Code:

EmpID,EmpName,DeptID,EmpSal,EmpDOB,EmpHireDate,Active
1,AAA,10,100,Jun-04-1986,2012-03-23 12:40:00 PM,Y
2,BBB,20,200,Apr-12-1991,2010-05-12 08:50:00 PM,N
3,CCC,30,300,Dec-31-1978,2010-01-08 12:00:00 AM,Y
3,DDD,40,400,Dec-31-1978,2010-01-08 12:00:00 AM,Y
5,EEE,50,500,,,
6,FFF,60,600,Mar-09-1989,2010-05-08 06:45:00 PM,N
8,GGG,70,700,,,

from your two sample input files. Is this reasonably close to the output you want.

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

@Don Cragun:

The header should have 7 Columns. Forgot to remove EMPID in the Column 5. Header should be as below:

Code:

EmpID,EmpName,DeptID,EmpSal,EmpDOB,EmpHireDate,Active

Sincere apologies for causing some confusion around this.

Anyway, I have executed the code given by you and the Output is in line with what I'm expecting.

Thanks much for the help even though my requirements were not communicated clearly