Append a field to the end of each line of a file based on searching another file.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Append a field to the end of each line of a file based on searching another file.
# 1  
Old 03-29-2005
Append a field to the end of each line of a file based on searching another file.

Hi All,

I have two comma separated value(CSV) files, say FileA and FileB.
The contents looks like that shown below.

FileA
EmpNo,Name,Age,Sex,
1000,ABC,23,M,
1001,DES,24,F,
1002,JHS,26,F,
1003,JOS,42,M,
...................


FileB
EmpNo,Spouse,
1000,DEB,
1002,FAR,
................


FileA has say 20000 lines and FileB has 1000. What I am trying to do is to append the spouse's name in the FileA if the employee is married. Else I need to append a N.A., or something. So for each EmpNo in FileA, I need to check FileB for a matching first column, and if a match is found append FileA with second column of FileB. Else Append a N.A. to the end of that line.

So my output file should be something like the one shown below.

FileC
EmpNo,Name,Age,Sex,Spouse,
1000,ABC,23,M,DEB,
1001,DES,24,F,N.A.,
1002,JHS,26,F,FAR,
1003,JOS,42,M,N.A.,

I know this can be done using awk or sed. But I am not much familier with them either. Please help me with some pointers to tackle this.. Some sample codes are most welcome..

Thanks in Advance.
Ultimate.

Last edited by ultimate; 03-29-2005 at 06:58 AM.. Reason: Subject modification
# 2  
Old 03-29-2005
nawk -f ult.awk FileB FileA

here's ult.awk:
Code:
BEGIN { FS=OFS="," }
FNR == NR { arr[$1]=$2; next }
{ $NF=($1 in arr) ? arr[$1] : "N.A."; print }

# 3  
Old 03-29-2005
Thanks a lot vgersh99!!
This exactly satisfies the need.. Now i realize that i know nothing about awk.. Smilie

I have been trying for half a day to accomplish this.. Great help.. Smilie

Btw, I did not have nawk, and tried with awk and it works fine.. Is there any big difference b/w both?

Last edited by ultimate; 03-29-2005 at 01:49 PM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To append new data at the end of each line based on substring of last column

Hi guys, I need to append new data at the end of each line of the files. This new data is based on substring (3rd fields) of last column. Input file xxx.csv: U1234|1-5X|orange|1-5X|Act|1-5X|0.1 /sac/orange 12345 0 U5678|1-7X|grape|1-7X|Act|1-7X|0.1 /sac/grape 5678 0... (5 Replies)
Discussion started by: null7
5 Replies

2. Shell Programming and Scripting

SED and Solaris Append line to the end of File does not work

Hello, I have to add a new line at the end of a File on Solaris-System: I think my script should be right, because I evaluated it to other threads. However the script does not what I am expected it should do. My file might look like this: Line1 Line2 Line3 And my script could... (7 Replies)
Discussion started by: Timo_HR
7 Replies

3. Shell Programming and Scripting

Append text from one file to another based on a search from the end of a document

Hi all, I have output files that are all text files with various different extensions. So, if I submit the input file "job_name.inp", when it finishes I get an output file "job_name.dat". A typical input file looks something like this: $CONTRL SCFTYP=RHF RUNTYP=ENERGY MAXIT=199 MULT=1... (4 Replies)
Discussion started by: marcozd
4 Replies

4. Shell Programming and Scripting

Append the end of each line in a file with a given string

Hi friends, I have a file containing many lines as follows. M:\mmarimut_v6.4.0_pit_01\java\build.xml@@\main\v6.4.0_pit_a M:\mmarimut_v6.4.0_pit_01\ADBasicView.java@@\main\v6.4.0_pit_a I would like to append the string "\0" at the end of each line in the file. The output should look... (10 Replies)
Discussion started by: nmattam
10 Replies

5. Shell Programming and Scripting

Append a string at the end of every line in a file

Hi Friends, I have a file with many lines as shown below. /START SAMPLE LINE/ M:\mmarimut_v6.4.0_pit_01\java\build.xml@@\main\v6.4.0_pit_a M:\mmarimut_v6.4.0_pit_01\port\Post.java@@\main\v6.4.0_pit_a M:\mmarimut_v6.4.0_pit_01\switchview\View.java@@\main\v6.4.0_pit_a /END SAMPLE LINE/ I... (1 Reply)
Discussion started by: nmattam
1 Replies

6. Shell Programming and Scripting

How can I append a string at the end of a line in a file

Hi, guys. I have one question: I have a file called "group", the contents of it is below: ******************************** ... test:x:203: sales:x:204: repair:x:205: research:x:206:brownj ... *********** Now I want to add string ",sherrys" at the end of "research:x:206:brownj", so... (5 Replies)
Discussion started by: daikeyang
5 Replies

7. Shell Programming and Scripting

Line Count and Append it to the end of the file.

Hi, I want to get a Line count of a file and append that at the end of the file. The Line count should not include the Headers : ------------------ COL1,COL2,COL3 123,abc,011 111,abd,0212 Record Count: 2 ------------------- Thanks. (7 Replies)
Discussion started by: smc3
7 Replies

8. Shell Programming and Scripting

append a character at end of each line of a file

Hi, i want to append a character '|' at end of each line of a file abc.txt. for example if the file abc.txt conatins: a|b|c 1|2|33 w|2|11 i want result file xyz.txt a|b|c| 1|2|33| w|2|11| I know this is simple but sumhow i am not able to reach end of line. its urgent, thanks for... (4 Replies)
Discussion started by: muaz
4 Replies

9. Shell Programming and Scripting

Append text at end of the first line in a file

Hi I need to append some text @ end of the first line in a file. like myfile.txt list = a,b,c list.a=some.. I give the arg "d" . now it append at end of first line list=a,b,c,d list.a=some... Please help me out this (7 Replies)
Discussion started by: catgovind
7 Replies

10. Shell Programming and Scripting

Append to end of each line of file without a temp file.

Hello I am trying to append an incrimenting number to the end of each line I have it working with a temp file. But I want to do this without a temp file. a=1 cat "file" | while read LINE do echo "$LINE, $a" >> filewithnumbers a=`expr $a + 1` ... (4 Replies)
Discussion started by: rorey_breaker
4 Replies
Login or Register to Ask a Question