Mapping a data in a file and delete line in source file if data does not exist.


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Mapping a data in a file and delete line in source file if data does not exist.
# 1  
Old 08-20-2012
Mapping a data in a file and delete line in source file if data does not exist.

Hi Guys,

Please help me with my problem here:
I have a source file:
Code:
1212  23232   343434  ASAS1  4
3212  23232   343434  ASAS2  4
3234  23232   343434  QWQW1  4
1134  23232   343434  QWQW2  4
3212  23232   343434  QWQW3  4

and a mapping file:
Code:
AAA    1    1   ASAS1    AA123
AAA   10   10   ASAS2    AA234
BBB    1    1   QWQW1    QQ123
BBB   24   24   QWQW3    QWSA1

Lets assume that these are all fixed length files.
What I want to happen is if the data in the 4th column of the source file (which is at the 50th-69th positions) does not exist in the 4th column of the mapping file (which is at the 40th-59th position); delete that line in the source file.

This should result like this:
Code:
1212  23232   343434  ASAS1  4
3212  23232   343434  ASAS2  4
3234  23232   343434  QWQW1  4
3212  23232   343434  QWQW3  4

The line:
Code:
1134  23232   343434  QWQW2  4

is deleted since the QWQW2 data is not in the mapping file.

Please help.
Thanks in advance!
# 2  
Old 08-20-2012
You can use awk to read the mapping file, storing each unique key (4th column) in an array. Then read the source file and print the line only if the 4th column is a key in the array.

Regards,
Alister
# 3  
Old 08-20-2012
Yes exactly my friend, i hope someone can help me in composing a one or two liner awk command since I am a bit worried with the time it would take to process the source file if it has hundred thousands of records in it.
# 4  
Old 08-20-2012
the usual paradigm...
Code:
awk 'FNR==NR{m[$4];next} $4 in m' mappingFile sourceFile

This User Gave Thanks to vgersh99 For This Post:
# 5  
Old 08-20-2012
Quote:
Originally Posted by vgersh99
the usual paradigm...
Code:
awk 'FNR==NR{m[$4];next} $4 in m' mappingFile sourceFile

Thanks!
I will try this one Smilie
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Compare the source definition file with data file

Hi How to compare the source definition file in unix with the data file . Please can you share me example if some one has done it before (3 Replies)
Discussion started by: Raj4fusion
3 Replies

2. UNIX for Dummies Questions & Answers

Formatting data in a raw file by using another mapping file

Hi All, i have a requirement where i need to format the input RAW file ( which is CSV) by using another mapping file(also CSV file). basically i am getting feed file with dynamic headers by using mapping file (in that target field is mapped with source filed) i have to convert the raw file into... (6 Replies)
Discussion started by: ravi4informatic
6 Replies

3. UNIX for Dummies Questions & Answers

using sed delete a line from csv file based on specific data in two separate fields

Hello, :wall: I have a 12 column csv file. I wish to delete the entire line if column 7 = hello and column 12 = goodbye. I have tried everything that I can find in all of my ref books. I know this does not work /^*,*,*,*,*,*,"hello",*,*,*,*,"goodbye"/d Any ideas? Thanks Please... (2 Replies)
Discussion started by: Chris Eagleson
2 Replies

4. Shell Programming and Scripting

Redirect output to a different text file depending source of data

I have a list of DNS servers I need to look up information on. Each of these servers has a master and a slave database. Essentially what I need to do is create two text files for each server. One with the Master view and one with the Slave view. There's 20 servers, in the end I should have 40 text... (4 Replies)
Discussion started by: spartan22
4 Replies

5. Shell Programming and Scripting

Help in adding a data after a particular line of data in a file.

Hi.. I'm into a bump after trying to solve this prob.. i've a file with contents like below. <blankline> 'pgmId' : 'UNIX', 'pgmData' : 'textfile', 'author' : 'admin', ....... Now i'm trying to insert a new data after pgmId. so the final output will be... (7 Replies)
Discussion started by: arjun_arippa
7 Replies

6. Shell Programming and Scripting

Delete lines line by match data 2 file.

i need to delete the lines is match from file data 1 & data 2 please help? data 1 4825307 4825308 4825248 4825309 4825310 4825311 4825336 data 2 4825248 0100362210 Time4Meal 39.00 41.73 MO & MT MT SMS 4825305 0100367565... (2 Replies)
Discussion started by: ooilinlove
2 Replies

7. Shell Programming and Scripting

Delete line in file based on data in another file

Hi there I would like to create a shell script to do the following: - delete a line in file1 if it contains the data string in file2 eg: file1 1 100109942004051510601703694 0.00 0.00 2 100109942004051510601702326 0.00 0.00 3 ... (1 Reply)
Discussion started by: earth_goddess
1 Replies

8. Shell Programming and Scripting

Post Shell programming: Question about source a file and read data from the file

This is shell programming assignment. It needs to create a file called .std_dbrc contains STD_DBROOT=${HOME}/class/2031/Assgn3/STD_DB (which includes all my simple database files) and I am gonna use this .std_dbrc in my script file (read the data from the database files) like this: .... (3 Replies)
Discussion started by: ccwq
3 Replies

9. Shell Programming and Scripting

how to delete record in file data with index in another file?

I want to deal with several data, i.e., data.*.txt with following structure MSG|20010102|123 125 4562 409|SEND MSG|20010102|120 230|SEND MSG|20010102|120 204 5071|SEND MSG|20010103|2 11 1098 9810|SEND ...... index file index.txt is 11 201 298 100 ...... What I want to do is: 1)... (0 Replies)
Discussion started by: zhynxn
0 Replies

10. Shell Programming and Scripting

Compare data in 2 files and delete if file exist

Hi there, I have written a script called "compare" (see below) to make comparison between 2 files namely test_put.log and Output_A0.log #!/bin/ksh while read file do found="no" while read line do echo $line | grep $file > /dev/null if then echo $file found found="yes" break fi... (3 Replies)
Discussion started by: lweegp
3 Replies
Login or Register to Ask a Question