Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

 ...(Fields 2 to 25)

 ...(Fields 2 to 25) 

 ...(Fields 2 to 25) 

 ...goes up to 15000

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

 ...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.

I've hit a wall on this one. Please help. Thank you.

share|improve this question
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
up vote 1 down vote accepted

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

if [ ! -f "$1" ] ; then
    echo "First argument must be active users list file"
    exit 1
while read line
    if [ "$( grep -E "^$( echo $line | cut -d' ' -f2 )$" "$1" )" ]  ; then
        echo $line

Store your original active users list as active.txt and the modified stats file stats.txt Make executable using chmod +x and run it:

$ < stats.txt ./ 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.

share|improve this answer
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 ./ active.txt | tee result.txt ./ line 11: syntax error near unexpected token done' ./ 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: ./ 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 ( ). 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:

"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.

share|improve this answer
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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .