# Filter Entries in a Large Text File Based on Whitelist

Good day. I could really use your help on this one. I have a stats text file in the following format.

 ID=1000000
Name=Name1
Field1=Value1
...(Fields 2 to 25)
Field26=Value26

ID=1000001
Name=Name2
Field1=Value1
...(Fields 2 to 25)
Field26=Value26

ID=1000002
Name=Name2
Field1=Value1
...(Fields 2 to 25)
Field26=Value26

...goes up to 15000


I have an active people text file separated by line breaks.

 Name2
Name5
Name11
Name12
...goes up to 1400 Random Names


I need to be able to delete records from the stats text file (ID, Name, Fields1 to 26) if the name is not found in the active people text file. In the example above, the associated record for Name1(ID, Name, Fields1 to 26) should be deleted since it's not in the active people text file.

I've tried reformatting the stats file through notepad++ using TextFX->Quick->Find/Replace to convert it to a comma separated file with each record separated by a line break. I had it rearranged to

 ID       Name    Field1  ...Fields2 to Fields 25... Field26
1000000  Name1   Value1  ...Value2 to Value 25...   Value26
1000001  Name2   Value1  ...Value2 to Value 25...   Value26
1000002  Name3   Value1  ...Value2 to Value 25...   Value26


I've opened it with excel and I've created two tables (stats table and a active names table) in mysql using the csv file file. I'm not sure how to process this in an automatic function. Besides removing inactive records, the other problem I have is rewriting it back to its old format.

I've been trying my best to figure this out for a hours on end. Is there a solution that won't require me to use find, copy, paste and switch between the two files 1400 times? I'm open to using different methods/programs to simplify the process. Unfortunately, I have to keep the stats file in this format.

-
Can you put the files into 2 DB-tables, preferrably a DB that can do SELECT with EXCEPT? Maybe you can keep the stats file in DB permanently if you need to do this task more often. –  ott-- Oct 18 '11 at 17:51

On pretty much any BSD or Linux system (I wrote this on OS X) you can use the following bash script filter.sh:

#!/bin/bash
if [ ! -f "$1" ] ; then echo "First argument must be active users list file" exit 1 fi while read line do if [ "$( grep -E "^$( echo$line | cut -d' ' -f2 )$" "$1" )" ]  ; then
echo $line fi done  Store your original active users list as active.txt and the modified stats file stats.txt Make filter.sh executable using chmod +x filter.sh and run it: $ < stats.txt ./filter.sh active.txt | tee result.txt
1000001 Name2 Value1 ...Value2 to Value 25... Value26


It will both print the active stats entries to standard output and write them to result.txt.

-
Hoping the user has a Linux system around somewhere. He didn't specify OS for desired solution. –  Daniel Beck Oct 18 '11 at 18:05
Thanks for replying. I'm have windows 7 ultimate x64. I used cygwin to run the script. \$ < stats.txt ./filter.sh active.txt | tee result.txt ./filter.sh: line 11: syntax error near unexpected token done' ./filter.sh: line 11: done' I don't know if running this in cygwin is the way to go. Also would it keep the original formatting? ID=idhere Name=namehere Field1=valuehere .... Field26=valuehere Unfortunately, I have to keep the field labels and equal signs. I look forward to your reply. –  Krispy K Oct 18 '11 at 18:57
@KrispyK No idea how to do it there, so I'm also looking forward to a solution on that system. Good luck! –  Daniel Beck Oct 18 '11 at 18:59
I accidentally pressed enter while editing my comment. Could you comment on the first reply I made? Thanks. –  Krispy K Oct 18 '11 at 19:01
@KrispyK Try the following: ./filter.sh active.txt < stats.txt | tee result.txt. Alternatively, click the link next to edited below my post and look at the first version I suggested. The file names were hardcoded in the script itself. –  Daniel Beck Oct 18 '11 at 19:09

Edit: I misconstrued the part about csv in the question. I was only trying to discuss the output portion.

Excel is not a database/recordset paradigm. Instead of excel, you can use Access, or barring that perhaps something like Base from OpenOffice ( http://www.openoffice.org/product/base.html ). Import the files as tables, link the tables, do [magic], export new files.

In Access, one crazy way to output the file is to create a query which builds the string you want:

Select
"ID=" & [id] & chr(13) & chr(10) &
"Name=" & [Name1] & chr(13) & chr(10) &
( ... )
"Field26=" & [Value26] & chr(13) & chr(10) &
FROM fooTable


And then save it, then export it to a text file. This will create a query with one field per record. The 13+10 is windows carriage return+line feed combo.

Not elegant.

-
Where do you filter based on the active users file? It doesn't look like you answered the question. –  Daniel Beck Oct 18 '11 at 19:30
I was answering the 2nd part about output. Once it is a database format, one could do a JOIN query; create a flag field which is set to 1 if their name matches the active names list (update table1 set flagField = 1 where fooName in (select name from table2)); etc. Note that storing the name in that active names list instead of their (unique) ID is an uh oh: which one of the John Smiths is the right one? –  horatio Oct 18 '11 at 19:43