Removing Lines if value exist in first file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Removing Lines if value exist in first file
# 1  
Old 08-28-2009
Removing Lines if value exist in first file

I tried a few ways to resolve this using a bash script w/ a loop, no luck.

File1: roughly 6,000 account numbers such as:
1111
1512
1113
123

I also have a dozen or so csv files, w/ the account number in the 4th field. What I would like to do is remove all lines if the account number is contained in File1.

So file looking like this:
1,55,John,2000,abc
1,55,Jane,1113,abc
1,55,Smit,9565,def
1,55,Phil,123,adf
1,55,John,205480,sdf

Would end up looking like this:
1,55,John,2000,abc
1,55,Smit,9565,def
1,55,John,205480,sdf

Any help would be appreciated!
# 2  
Old 08-28-2009
cat filter.sh

Code:
#/usr/bin/bash

INFILE=${1}

IFS=","

cat ${INFILE} | \
while read A B C ID D
do
  grep "^${ID}" excludes > /dev/null
  if [ ${?} -ne 0 ]
  then
    echo "${A},${B},${C},${ID},${D}"
  fi
done

cat filter2.sh

Code:
#/usr/bin/bash

INFILE=${1}

cat ${INFILE} | \
while read LINE
do
  ID=`echo "${LINE}" | cut -f4 -d","`

  grep "^${ID}" excludes > /dev/null
  if [ ${?} -ne 0 ]
  then
    echo "${LINE}"
  fi
done

cat excludes
Code:
1111
1512
1113
123

cat infile
Code:
1,55,John,2000,abc
1,55,Jane,1113,abc
1,55,Smit,9565,def
1,55,Phil,123,adf
1,55,John,205480,sdf

Execute:

filter1.sh infile
or
filter2.sh infile

or

filter1.sh infile > outfile
or
filter2.sh infile > outfile
# 3  
Old 08-28-2009
Works great - I used your 2nd suggestion. Processed huge files a lot faster that I would have thought.

You really helped me out of a jam! Thanks again.
# 4  
Old 08-28-2009
Wow...

No way would I use a shell for that job! The following Perl script is probably a hundred times faster and more efficient!

Put the following into a file and add execute permission. Run the script and give it two filenames. The first should be the list of account numbers to exclude and the second should be the list that has the account number in field 4.

Code:
#!/usr/bin/perl

my @a, %exclude;
my $file = shift;
open(EXCLUDE_LIST, "< $file") or die;
chomp( @a=<EXCLUDE_LIST> );
close(EXCLUDE_LIST);
@exclude{@a}=@a;

while (<>) {
    print unless exists $exclude{ (split(/,/))[3] };
}

# 5  
Old 08-29-2009
Tried the Perl version - yeah, does work a LOT faster. Not familiar w/ Perl - had one question - if the account number changes position, i know i can edit the [3] to let's say [0] if it is located in the first column.

My question is how can i make that account number position a variable so that i can pass it at the same time I'm specifying the file names? Unfortunately, some of the files I am scrubbing against, the variable changes position.

Thanks again!
# 6  
Old 08-29-2009
awk alternative:

Code:
awk -F',' 'NR==FNR{_[$0]=1}NR>FNR&&!_[$4]{print}' exclude infile

# 7  
Old 08-29-2009
For the awk option - how do i put it into a bash script? When running manually, works great. As soon as I put that same line into a script, the $0 and $1 doesn't utilize the arguments specified in that line of code.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Removing lines from a file

I have a file `/tmp/wrk` containing filenames with paths. I want to remove filenames from this file, for example remove all filenames containing alja cagr cavt clta cmdo or corl remove all filenames containing data for days in region `d.2016.001` to `d.2016.207` remove all filenames... (10 Replies)
Discussion started by: kristinu
10 Replies

2. Shell Programming and Scripting

Removing lines from a file

Hi, I have a linux server that was hacked and I have a bunch of files that sporadically contain the following lines through out the file: <?php eval(base64_decode("Xxxxxxxxxxxxxx/xxxxxxxx")); I did't put the exact lines of the file in this post. The "Xxxx" are random letters/numbers.... (8 Replies)
Discussion started by: nck
8 Replies

3. Shell Programming and Scripting

Remove lines from one file that exist in another file

Hello Everyone, I'm currently have a requirement where I've generated a list of files with specific attributes and I need to know what lines are similar between the two files. For example: -File 1- line1 line2 line3 -File 2- line1 line2 line4 line5 -Desires Output- line1 line2... (5 Replies)
Discussion started by: omnivir
5 Replies

4. Shell Programming and Scripting

Deleting lines of a file if they exist in another file

I have a reference file that needs to remain static and another file that may or may not have duplicate rows that match the reference file. I need help with a command that will delete any duplicate rows from the second file while leaving reference file intact For example reference file would... (4 Replies)
Discussion started by: bjdamon
4 Replies

5. UNIX for Dummies Questions & Answers

Removing a user that doesnt exist from a group

Hi there, normally if I want to remove a user tht I have added to a specific group, i would do the following this is what my group2 looks like # grep group2 /etc/group group2:x:7777:user2,user1,user4 user1 has been defined in a few groups # id -nG user1 group1 group2 group3 So... (3 Replies)
Discussion started by: rethink
3 Replies

6. UNIX for Dummies Questions & Answers

removing several lines from a file

Hi folks, I have a long string of DNA sequences, and I need to remove several lines, as well as the line directly following them. For example, here is a sample of my starting material: >548::GY31UMJ02DLYEH rank=0007170 x=1363.5 y=471.0 length=478... (1 Reply)
Discussion started by: kkohl78
1 Replies

7. Shell Programming and Scripting

Removing the first and last lines in a file

Hi Gurus, I'm a little new to UNIX. How can I do remove the first and last line in a file? Say, supppose I have a file as below: Code: 1DMA 400002BARRIE 401002CALGARY/LETHBRI 402002CARLETON 500001PORTLAND-AUBRN 501001NEW YORK, NY 502001BINGHAMTON, NY ... (2 Replies)
Discussion started by: naveendronavall
2 Replies

8. UNIX for Dummies Questions & Answers

Removing lines from a file

I'm trying to find a command which will allow me to remove a range of lines (2-4) from a .dat file from the command line without opening the file. Someone mentioned using the ex command? Does anyone have any ideas? thanks (6 Replies)
Discussion started by: computersaysno
6 Replies

9. Shell Programming and Scripting

Removing lines within a file

Hi There, I've written a script that processes a data file on our system. Basically the script reads a post code from a list file, looks in the data file for the first occurrence (using grep) and reads the line number. It then tails the data file, with the line number just read, and outputs to a... (3 Replies)
Discussion started by: tookers
3 Replies

10. Shell Programming and Scripting

Removing lines from a file

Hello i have 2 files file1 and file2 as shown below file1 110010000000206|567810008161509 110010000000207|567810072227627 110010000000208|567811368851555 110010000000209|567811422513652 110010000000210|567812130217683 110010000000211|567813220211182 110010000000212|567813449322589... (4 Replies)
Discussion started by: PradeepRed
4 Replies
Login or Register to Ask a Question