Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Using awk to remove duplicate line if field is empty

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Using awk to remove duplicate line if field is empty
# 1  
Old 01-15-2015
Using awk to remove duplicate line if field is empty
Hi all,

I've got a file that has 12 fields. I've merged 2 files and there will be some duplicates in the following:

FILE:

Code:
1. ABC, 12345, TEST1, BILLING, GV, 20/10/2012, C, 8, 100, AA, TT, 100
2. ABC, 12345, TEST1, BILLING, GV, 20/10/2012, C, 8, 100, AA, TT, (EMPTY)
3. CDC, 54321, TEST3, BILLING, VG, 10/11/2012, D, 9, 99, BB, AA, 99
4. CDC, 54321, TEST3, BILLING, VG, 10/11/2012, D, 9, 99, BB, AA, (EMPTY)
5. AAA, 34535, TEST1, BILLING, CC, 12/09/2011, C, 9, 99, BB, CC, (EMPTY)

OUTPUT:

Code:
1. ABC, 12345, TEST1, BILLING, GV, 20/10/2012, C, 8, 100, AA, TT, 100
3. CDC, 54321, TEST3, BILLING, VG, 10/11/2012, D, 9, 99, BB, AA, 99
5. AAA, 34535, TEST1, BILLING, CC, 12/09/2011, C, 9, 99, BB, CC, (EMPTY)

Need an awk script to remove lines 2 and 4 because there is no data in the 12th field. Line 5 would still remain as there is no other duplicate line.

I have no problem removing these in excel but i need the script to remove them so that the user doesn't need to remove them in excel.

Thanks!
Last edited by Scrutinizer; 01-17-2015 at 04:00 AM..
# 2  
Old 01-16-2015
Hello tugar,

Could you please try following and let me know if this helps.
Code:
awk -F, '{A[$1 FS $2 FS $3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10 FS $11]+=$12} END{for(i in A){if(!A[i]){print i} else {print i OFS A[i]}}}' OFS=", "  Input_file

Output will be as follows.
Code:
ABC, 12345, TEST1, BILLING, GV, 20/10/2012, C, 8, 100, AA, TT, 100
CDC, 54321, TEST3, BILLING, VG, 10/11/2012, D, 9, 99, BB, AA, 99
AAA, 34535, TEST1, BILLING, CC, 12/09/2011, C, 9, 99, BB, CC

EDIT: Adding a non oneliner form for same.
Code:
awk -F, '       {
                        A[$1 FS $2 FS $3 FS $4 FS $5 FS $6 FS $7 FS $8 FS $9 FS $10 FS $11]+=$12
                }
         END    {       for(i in A)      {
                                if(!A[i]){
                                                print i
                                         }
                                else     {
                                                print i OFS A[i]
                                         }
                                         }
                }
        ' OFS=", "  Input_file


Thanks,
R. Singh
Last edited by RavinderSingh13; 01-16-2015 at 12:58 AM.. Reason: Added a non one liner form of solution
This User Gave Thanks to RavinderSingh13 For This Post:
tugar
# 3  
Old 01-16-2015
Worked flawlessly!

Thank you Ravinder.
# 4  
Old 01-16-2015
Linux
If the data follows these rules (reverse lexicographical order), you can write like this.

Code:
$12 && (p = $0) && NF-- && (l = $0) && $0 = p;!$12 && l != $0;

rules
  • empty duplicate line appears only as the next line of its not empty duplicate line.
# 5  
Old 01-17-2015
If the file is ordered, try:
Code:
awk -F", *" '{p=$0; $NF=x} !A[$0]++{print p}' file

 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to remove line if field has symbols in it

Trying to use awk to remove a line only if $1 contains either ; or :. Thje awk below runs but no lines are removed. Thank you :). awk awk '$1 !~ /;/ || $1 !~ /:/ { print }' file file AARS2;TMEM151B 1 AASS 2 ABAT 3 ABCA1 3 ABCA10 1 ABCA12 2 ABCA13 1 ABCA13:AX746840 2 ABCA2 5 (5 Replies)
Discussion started by: cmccabe
5 Replies

2. Shell Programming and Scripting

How to remove empty field in a text file?

Hi all, I want to remove empty field in a text file. I tried to used sed. But it failed. Input: LG10_PM_map_19_LEnd 1000560 G AG AG LG10_PM_map_19_LEnd 1005621 G AG LG10_PM_map_19_LEnd 1011214 A AG AG LG10_PM_map_19_LEnd 1011673 T CT CT ... (3 Replies)
Discussion started by: huiyee1
3 Replies

3. Shell Programming and Scripting

Remove duplicate value based on two field $4 and $5

Hi All, i have input file like below... CA009156;20091003;M;AWBKCA72;123;;CANADIAN WESTERN BANK;EDMONTON;;2300, 10303, JASPER AVENUE;;T5J 3X6;; CA009156;20091003;M;AWBKCA72;321;;CANADIAN WESTERN BANK;EDMONTON;;2300, 10303, JASPER AVENUE;;T5J 3X6;; CA009156;20091003;M;AWBKCA72;231;;CANADIAN... (2 Replies)
Discussion started by: mohan sharma
2 Replies

4. UNIX for Dummies Questions & Answers

remove empty field

Hi all ! I'm sure it is a basic question but I didn't find any threads that fit my need. How to remove empty fields with awk? Or in other words, how to shift all the fields after an empty field on the left? input: 1|2||3|4|5||6 wanted: 1|2|3|4|5|6 I tried: awk '{for(i=1; i<=NF;... (7 Replies)
Discussion started by: lucasvs
7 Replies

5. Shell Programming and Scripting

awk - remove row if specific field is empty/blank

I have this text.filecharles darwin sam delight george washington johnson culper darwin sam delight micheal jackson penny lite and would like to remove the row, if the first field is blank. so the result would be: result.filecharles darwin sam ... (4 Replies)
Discussion started by: charles33
4 Replies

6. Shell Programming and Scripting

Remove duplicate lines (the first matching line by field criteria)

Hello to all, I have this file 2002 1 23 0 0 2435.60 131.70 5.60 20.99 0.89 0.00 285.80 2303.90 2002 1 23 15 0 2436.60 132.90 6.45 21.19 1.03 0.00 285.80 2303.70 2002 1 23 ... (6 Replies)
Discussion started by: joggdial3000
6 Replies

7. Shell Programming and Scripting

awk - if field is empty, move line to new file

I have a script with this statement: /usr/xpg4/bin/awk -F"" 'NR==FNR{s=$2;next}{printf "%s\"%s\"\n", $0, s}' LOOKUP.TXT finallistnew.txt >test.txt I want to include logic or an additional step that says if there is no data in field 3, move the whole line out of test.txt into an additional... (9 Replies)
Discussion started by: scriptr2be
9 Replies

8. Shell Programming and Scripting

Using sed to remove lines where field is empty

I was just looking at this post: https://www.unix.com/shell-programming-scripting/22893-delete-multiple-empty-lines.html. and I am looking to achieve the same with sed. So the idea is to delete lines from a file where a certain field has no value. Inputfile: EMID MMDDYY HOURS JOB EMNAME 0241... (4 Replies)
Discussion started by: figaro
4 Replies

9. Shell Programming and Scripting

awk script to remove duplicate rows in line

i have the long file more than one ns and www and mx in the line like . i need the first ns record and first www and first mx from line . the records are seperated with tthe ; i am try ing in awk scripting not getiing the solution. ... (4 Replies)
Discussion started by: kiranmosarla
4 Replies

10. Shell Programming and Scripting

Awk scrip to remove empty field

Hi I have a file which looks like this name: Sally group: Group4 name: Tim group: Group1 name: Dan group: Group2 name: Chris group: Group3 name: Peter group: name: Fred group: name: Mary group: Group2 Well I want to get rid of the... (4 Replies)
Discussion started by: bombcan
4 Replies
Login or Register to Ask a Question