Two files, remove lines from second based on lines in first


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Two files, remove lines from second based on lines in first
# 1  
Old 01-31-2014
Two files, remove lines from second based on lines in first

I have two files, a keepout.txt and a database.csv. They're unsorted, but could be sorted.

keepout:
Code:
user1
buser3
anuser19
notheruser27

database:
Code:
user1,2343,"information about",field,blah,34
user2,4231,"mo info",etc,stuff,43
notheruser27,4344,"hiya",thing,more thing,423
user5,6666,"test text",info,stuff,833

Output would be
Code:
user2,4231,"mo info",etc,stuff,43
user5,6666,"test text",info,stuff,833

Make sense? If a line in keepout matches a field (would be nice to just be first field) in database then I want to dump the line in the output.

I've tried awky ways and greppy ways but can't get it just right. It's not entire lines that are matching, just the lines. Kind of like a loopy grep -v.

Thanks for your help!

Last edited by Franklin52; 01-31-2014 at 02:41 PM.. Reason: Please use code tags
# 2  
Old 01-31-2014
Code:
awk -F'[, ]' 'NR==FNR{A[$1];next}!($1 in A)' keepout database

This User Gave Thanks to Yoda For This Post:
# 3  
Old 01-31-2014
Awesome, except it isn't working on my "real" files. It works entirely correctly on the sample data I gave -- your answer is correct.

I wonder if one of the hacks I had tried before might have worked...

In the real world, with messy data, what might be keeping this from working? Some unprintable nonsense? Line ending CR/LF vs CR vs LF crud? Do you have tips for me where this might be brittle?

Thanks!
# 4  
Old 01-31-2014
Use hexdump or od to check if you have any such characters in your file.
This User Gave Thanks to Yoda For This Post:
# 5  
Old 01-31-2014
Craaaaaap. I think I had this on my own but was bitten by a trip through an editor that changed the line endings. So, takeaway for my internet self when I search for this again.

1) Yoda was right! Thanks for the help!
2) Self, it might not be your code but the input. Maybe create your test files before making them up to ask for help...
3) LF line endings vs CR line endings matter, and seem to be what broke this for you. (LF is what you needed and what seems to fix it.)
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Remove duplicate lines from file based on fields

Dear community, I have to remove duplicate lines from a file contains a very big ammount of rows (milions?) based on 1st and 3rd columns The data are like this: Region 23/11/2014 09:11:36 41752 Medio 23/11/2014 03:11:38 4132 Info 23/11/2014 05:11:09 4323... (2 Replies)
Discussion started by: Lord Spectre
2 Replies

2. UNIX for Dummies Questions & Answers

Remove lines in a positional file based on string value

Gurus, I am relatively new to Unix scripting and am struck with a problem in my script. I have positional input file which has a FLAG indicator in at position 11 in every record of the file. If the Flag has value =Y, then the record from the input needs to be written to a new file.However if... (3 Replies)
Discussion started by: gsam
3 Replies

3. Shell Programming and Scripting

Remove certain lines from file based on start of line except beginning and ending

Hi, I have multiple large files which consist of the below format: I am trying to write an awk or sed script to remove all occurrences of the 00 record except the first and remove all of the 80 records except the last one. Any help would be greatly appreciated. (10 Replies)
Discussion started by: nwalsh88
10 Replies

4. Shell Programming and Scripting

Remove duplicate lines based on field and sort

I have a csv file that I would like to remove duplicate lines based on field 1 and sort. I don't care about any of the other fields but I still wanna keep there data intact. I was thinking I could do something like this but I have no idea how to print the full line with this. Please show any method... (8 Replies)
Discussion started by: cokedude
8 Replies

5. Shell Programming and Scripting

Remove lines based on column value

Hi All, I just need a quick fix here. I need to delete all lines containing "." in the 6th column. Input: 1 1055498 . G T 5.46 . 1 1902377 . C T 7.80 . 1 1031540 . A G 34.01 PASS 1 ... (2 Replies)
Discussion started by: Hkins552
2 Replies

6. UNIX for Dummies Questions & Answers

remove duplicate lines based on two columns and judging from a third one

hello all, I have an input file with four columns like this with a lot of lines and for example, line 1 and line 5 match because the first 4 characters match and the fourth column matches too. I want to keep the line that has the lowest number in the third column. So I discard line 5.... (5 Replies)
Discussion started by: TheTransporter
5 Replies

7. Shell Programming and Scripting

Remove lines from XML based on condition

Hi, I need to remove some lines from an XML file is the value within a tag is empty. Imagine this scenario, <acd><acdID>2</acdID><logon></logon></acd> <acd><acdID></acdID><logon></logon></acd> <acd><acdID></acdID><logon></logon></acd> <acd><acdID></acdID><logon></logon></acd> I... (3 Replies)
Discussion started by: giles.cardew
3 Replies

8. Shell Programming and Scripting

Remove lines based on contents of another file

So, this issue is driving me nuts! I was hoping to get a lending hand here... I have 2 files: file1.txt contains: this is example1 this is example2 this is example3 this is example4 this is example5 file2.txt contains: example3 example5 Basically, I need a script or command to... (4 Replies)
Discussion started by: bashshadow1979
4 Replies

9. Shell Programming and Scripting

remove lines based on score criteria

Hi guys, Please guide for Solution. PART-I INPUT FILE (has 2 columns ID and score) TC5584_1 93.9 DV161411_2 79.5 BP132435_5 46.8 EB682112_1 34.7 BP132435_4 29.5 TC13860_2 10.1 OUTPUT FILE (It shudn't contain the line ' BP132435_4 29.5 ' as BP132435 is repeated... (2 Replies)
Discussion started by: smriti_shridhar
2 Replies

10. Shell Programming and Scripting

Remove lines, Sorted with Time based columns using AWK & SORT

Hi having a file as follows MediaErr.log 84 Server1 Policy1 Schedule1 master1 05/08/2008 02:12:16 84 Server1 Policy1 Schedule1 master1 05/08/2008 02:22:47 84 Server1 Policy1 Schedule1 master1 05/08/2008 03:41:26 84 Server1 Policy1 ... (1 Reply)
Discussion started by: karthikn7974
1 Replies
Login or Register to Ask a Question