Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Deleting record in a file by looking up another file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Deleting record in a file by looking up another file
# 1  
Old 12-29-2011
MySQL Deleting record in a file by looking up another file
I have two files which are pipe delimited "|". Also, each record in these files will end with a "|"

File 1
====
Code:
EMPID|NAME|DEPT
1|AAA|PPP
2|BBB|QQQ
3|CCC|RRR
4|DDD|SSS
5|EEE|TTT

File 2 (EMPID of those employees who have left the organisation)
====
Code:
EMPID|
2|
5|

I have to delete the records of emplyees in file 1 if their EMPID is found in file 2

Expected output
============

File 1
====
Code:
EMPID|NAME|DEPT
1|AAA|PPP
3|CCC|RRR
4|DDD|SSS

It would be greatful if someone helps me with this
Last edited by Franklin52; 12-29-2011 at 10:43 AM.. Reason: Please use code tags for data and code samples, thank you
# 2  
Old 12-29-2011
Code:
$fgrep -v -f file2 file1
1|AAA|PPP
3|CCC|RRR
4|DDD|SSS

# 3  
Old 12-29-2011
Thanks a lot Smilie it worked...can you explain me how this works Smilie
# 4  
Old 12-29-2011
we are using fgrep here

Code:
 
-f file1 file2 ---> search file1 pattern in the file2
-v    Prints all lines except those that  contain  the  pat-
           tern.

# 5  
Old 12-29-2011
Ok..But if the File 2 contains more than one field, then this will not work correct? Can you please tell me how to handle this.

Let Say,

File 2
====
Code:
EMPID|NAME|DEPT|RESIGNREQNO
2|BBB|QQQ|5
5|EEE|TTT|2

In this case, I just want to check the EMPID column alone of each record in file 2 and then delete the corresponding record in file1

Smilie

---------- Post updated at 03:18 PM ---------- Previous update was at 02:59 PM ----------

Some one please help on this. Smilie

Moderator's Comments:
Mod Comment How to use code tags when posting data and code samples.
Last edited by Franklin52; 12-29-2011 at 10:44 AM.. Reason: Please use code tags for data and code samples, thank you
# 6  
Old 12-29-2011
if file2 contains more than one field also, it will work ( as long as the file2 line is present in the file1)

Code:
$ cat file1
EMPID|NAME|DEPT
1|AAA|PPP
2|BBB|QQQ
3|CCC|RRR
4|DDD|SSS
5|EEE|TTT

$ cat file2
EMPID|NAME|DEPT|RESIGNREQNO
2|BBB|QQQ|5
5|EEE|TTT|2

$ awk -F\| '$1~/^[0-9]/{a=$1"|"a} END{printf("egrep -v \"%s\" file1",substr(a,1,length(a)-1))}' file2 | sh
EMPID|NAME|DEPT
1|AAA|PPP
3|CCC|RRR
4|DDD|SSS

This User Gave Thanks to itkamaraj For This Post:
machomaddy
# 7  
Old 12-29-2011
Thanks... it works!! you are awesome Smilie
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Keeping record of file 2 based on a reference file 1 in awk

I have 2 input files (tab separated): file1: make_A 1990 foo bar make_B 2010 this that make_C 2004 these those file2: make_X 1970 1995 ref_1:43 ref_2:65 make_A 1970 1995 ref_1:4 ref_2:21 ref_3:18 make_A 1980 2002 ref_1:7 ref_2:7 ref_3:0 ... (2 Replies)
Discussion started by: beca123456
2 Replies

2. Shell Programming and Scripting

Extract timestamp from first record in xml file and it checks if not it will replace first record

I have test.xml <emp><id>101</id><name>AAA</name><date>06/06/14 1811</date></emp> <Join><id>101</id><city>london</city><date>06/06/14 2011</date></join> <Join><id>101</id><city>new york</city><date>06/06/14 1811</date></join> <Join><id>101</id><city>sydney</city><date>06/06/14... (2 Replies)
Discussion started by: vsraju
2 Replies

3. Shell Programming and Scripting

Need unix commands to delete records from one file if the same record present in another file...

Need unix commands to delete records from one file if the same record present in another file... just like join ... if the record present in both files.. delete from first file or delete the particular record and write the unmatched records to new file.. tried with grep and while... (6 Replies)
Discussion started by: msathees
6 Replies

4. UNIX for Dummies Questions & Answers

12. If an ‘88’ Record with BAI Code ‘902’ was found on input file and not written to Output file, re

This is my input file like this 03,105581,,015,+00000416418,,,901,+00000000148,,,922,+00000000354,,/ 49,+00000000000416920,00002/ 03,5313236,,015,+00231036992,,,045,+00231036992,,,901,+00000048428,,/ 88,100,+0000000000000,0000000,,400,+0000000000000,0000000,/ 88,902,+0000000079077,,/... (0 Replies)
Discussion started by: sgoud
0 Replies

5. Shell Programming and Scripting

Get the file name file size record count a file

Hi I want to find file size and record count for a file in same line. Thanks (2 Replies)
Discussion started by: dgmm
2 Replies

6. UNIX for Dummies Questions & Answers

how to read record by record from a file in unix

Hi guys, i have a big file with the following format.This includes header(H),detail(D) and trailer(T) information in the file.My problem is i have to search for the character "6h" at 14 th and 15 th position in all the records .if it is there i have to write all those records into a... (1 Reply)
Discussion started by: raoscb
1 Replies

7. Shell Programming and Scripting

Script to search a bad record in a file then put the record in the bad file

I need to write a script that can find a bad record (for example: there is date field colom but value provided in the file for this field is N/A) then script shoud searches this pattern and then insert the whole record into the bad file. Example: File1 Name designation dateOfJoining... (2 Replies)
Discussion started by: shilendrajadon
2 Replies

8. UNIX for Advanced & Expert Users

Script to search a bad record in a file then put the record in the bad file

I need to write a script that can find a bad record (for example: there is date field colom but value provided in the file for this field is N/A) then script shoud searches this pattern and then insert the whole record into the bad file. Example: File1 Name designation dateOfJoining... (1 Reply)
Discussion started by: shilendrajadon
1 Replies

9. Shell Programming and Scripting

splitting a record and adding a record to a file

Hi, I am new to UNIX scripting and woiuld appreicate your help... Input file contains only one (but long) record: aaaaabbbbbcccccddddd..... Desired file: NEW RECORD #new record (hardcoded) added as first record - its length is irrelevant# aaaaa bbbbb ccccc ddddd ... ... ... (1 Reply)
Discussion started by: rsolap
1 Replies

10. UNIX for Dummies Questions & Answers

deleting record file

if in my file consist with record like this : AAA1234567 BBB3314134 AAA1232134 CCC1231232 DDD0980980 AAA8098090 And I want to delete record which substring(record,1,3)="AAA". Is it possible to do that (deleting specific record) by unix shell script or perl without coding any... (1 Reply)
Discussion started by: yatno
1 Replies
Login or Register to Ask a Question