Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

awk to delete fields from flat file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting awk to delete fields from flat file
# 1  
Old 11-04-2013
awk to delete fields from flat file
I would like to know awk command to delete the specific feild from a flat file delimited by '|' if the first feild starts with 354

Code:
 
I tried 
 
awk 'BEGIN {FS=OFS="|"} {$16=""; sub(/\|/, "")}'1
 
Input file:
 
354|||||GROUND||97||JUDD STREET|||LONDON||WC1H 9JG|ADI001|
354|||||FLAT 1 ||213||STRAND|||LONDON||WC2R 1AP|ADI0001|
324|||||||491A||FULHAM ROAD|||LONDON||SW6 1HJ|ADI0001|
 
Expected Output:
 
354|||||GROUND||97||JUDD STREET|||LONDON||WC1H9JG|
354|||||FLAT 1 ||213||STRAND|||LONDON||WC2R 1AP|
324|||||||491A||FULHAM ROAD|||LONDON||SW6 1HJ|ADI0001|

# 2  
Old 11-04-2013
You can do something like:
Code:
awk -F"|" '/^354/{NF=NF-2;NF++}1' OFS="|" file

# 3  
Old 11-04-2013
Hi,
this one is tested and works
Code:
cat filename|awk -F\| '
{for (i=1; i<16; i++)
        {printf "%s|",$i}
 if ($i!="354") {printf "%s|",$16}
 for (i=17; i<NF; i++)
        {printf "%s|",$i}
 printf "\n"
}

If you want to include lines that the 1st field begins with 354 but there are other chars after it (eg. 3540) then you can change the if statement to
if (substr($1,1,3)=="354") ...

Hope to help,
Nikos
Last edited by Franklin52; 11-04-2013 at 09:06 AM.. Reason: Please use code tags for data and code samples , thanks
# 4  
Old 11-05-2013
I tried both the commands provided above on a sample file as shown below,

Code:
 
Input file:
 
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER|
748|49790|
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12||
352|5759991|SP29|1200031516735|20131101|
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|ADI0001|
748|49792|
352|5760019|SP27|1200021998833|20131101|
 
 
Expected output:
 
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER|
748|49790|
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12|
352|5759991|SP29|1200031516735|20131101|
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|
748|49792|
352|5760019|SP27|1200021998833|20131101|
 
 
It should remove 16th feild on a line which starts only with 354
 
From 
 
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12||

 
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|ADI0001|

To 
 
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12|

354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|

Thanks very much and awaiting for your suggestions
# 5  
Old 11-05-2013
What's wrong with the provided code:
Code:
awk -F"|" '/^354/{NF=NF-2;NF++}1' OFS="|" file

# 6  
Old 11-05-2013
Hi Franklin, the above command is not removing the 16th feild which starts with 354 line in a file.

I have executed the above command on my unix box and this is how it shows,

Code:
 
XXXXX:XXXXXXXX [XXXXXXXXXXXX] $ awk -F"|" '/^354/{NF=NF-2;NF++}1' OFS="|" D171_test_file
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER|
748|49790|
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12||
352|5759991|SP29|1200031516735|20131101|
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|ADI0001|
748|49792|
352|5760019|SP27|1200021998833|20131101|
XXXXX:XXXXXXXX [XXXXXXXXXXXX] $
 
 
It should have removed the 16th feild from the file where line starts with 354
 
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12|

354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|

Thanks very much,
# 7  
Old 11-05-2013
first you try this NF prints number of fields in file

Code:
$ awk -F"|" '{print $0,NF}' OFS="|" file
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER||13
748|49790||3
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12|||17
352|5759991|SP29|1200031516735|20131101||6
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|ADI0001||17
748|49792||3
352|5760019|SP27|1200021998833|20131101||6

from post #1

Code:
$ cat file
354|||||GROUND||97||JUDD STREET|||LONDON||WC1H 9JG|ADI001|
354|||||FLAT 1 ||213||STRAND|||LONDON||WC2R 1AP|ADI0001|
324|||||||491A||FULHAM ROAD|||LONDON||SW6 1HJ|ADI0001|

Code:
$ awk -F"|" '/354/{$16="\b"}1' OFS="|" file

Resulting
Code:
354|||||GROUND||97||JUDD STREET|||LONDON||WC1H 9JG|
354|||||FLAT 1 ||213||STRAND|||LONDON||WC2R 1AP|
324|||||||491A||FULHAM ROAD|||LONDON||SW6 1HJ|ADI0001|

from post #6

Code:
$ cat file
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER|
748|49790|
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12||
352|5759991|SP29|1200031516735|20131101|
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|ADI0001|
748|49792|
352|5760019|SP27|1200021998833|20131101|

Code:
$ awk -F"|" '/354/{$16="\b"}1' OFS="|" file

Resulting
Code:
ZHV|3011|D0171001|L|LPN|Z|PAN|20131104000148||||OPER|
748|49790|
354|902|20131029|1|2|3|4|5|6|7|8|9|10|11|12|
352|5759991|SP29|1200031516735|20131101|
354|||||FLAT 2 AT||2||GARENDON ROAD|||MORDEN|SURREY|SM4 6LN|
748|49792|
352|5760019|SP27|1200021998833|20131101|

Last edited by Akshay Hegde; 11-05-2013 at 09:04 AM..
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk Flat File Conversion Script

In awk how would I flatten input.txt to output.txt? Input: givenname: Darth sn: Vadar mail: d.vadar@deathstar.com uid: dv12345 orclguid: 1234567890 givenname: Carlito sn: Brigante mail: c.brigante@paradise.com uid: cb12345 orclguid: 2134567890 Output: ... (3 Replies)
Discussion started by: u20sr
3 Replies

2. Shell Programming and Scripting

awk to place value at 24 field in a flat file issue

I am trying to add 0393 value at 24th feild using the below command, but its adding at all the lines including header and trailer Input file: ZHV|2657|D0217001|T|TXU|Z|PAN|20131112000552||||OPER| 754|52479| 492|489|SP40|1014570286334|20131111|20131201|14355334|CHAMELON... (1 Reply)
Discussion started by: Aditya_001
1 Replies

3. Shell Programming and Scripting

Delete last 2 fields from every record in a file

Sample file record : "20130617003","2013-06-18T07:00:03","OUTWARD","01001011","TEST PLC","","HFX834346364364","20130617","10","DUM87534758","","1.28","826","020201","65879278","","","","","","010101","56789","DUMMY... (3 Replies)
Discussion started by: bigbuk
3 Replies

4. UNIX for Dummies Questions & Answers

using sed delete a line from csv file based on specific data in two separate fields

Hello, :wall: I have a 12 column csv file. I wish to delete the entire line if column 7 = hello and column 12 = goodbye. I have tried everything that I can find in all of my ref books. I know this does not work /^*,*,*,*,*,*,"hello",*,*,*,*,"goodbye"/d Any ideas? Thanks Please... (2 Replies)
Discussion started by: Chris Eagleson
2 Replies

5. Shell Programming and Scripting

SED | Awk flat file one liner

sed awk one liner (2 Replies)
Discussion started by: jap2614
2 Replies

6. Shell Programming and Scripting

use awk to ssh from variable in flat file

flat file looks like ooss-pfgg-1234,vol_name_1, mail-list decoded = hostname,volum_name,mail_list each line has diff info am trying to ssh into each fist field, check vol usage for second field, and if greater than 90% send mail to mail-list got the second and third part working, ... (1 Reply)
Discussion started by: riegersteve
1 Replies

7. Shell Programming and Scripting

awk to compare flat files and print output to another file

Hello, I am strugling from quite a some time to compare flat files with over 1 million records could anyone please help me. I want to compare two pipe delimited flat files, file1 with file2 and output the unmatched rows from file2 in file3 Sample File1: ... (9 Replies)
Discussion started by: suhaeb
9 Replies

8. Shell Programming and Scripting

awk sed cut? to rearrange random number of fields into 3 fields

I'm working on formatting some attendance data to meet a vendors requirements to upload to their system. With some help on the forums here, I have the data close. But they've since changed what they want. The vendor wants me to submit three fields to them. Field 1 is the studentid field,... (4 Replies)
Discussion started by: axo959
4 Replies

9. Shell Programming and Scripting

Search flat file and return 3 fields

I need to be able to search a flat file (comma-separated values) for a specific value and then return the following 2 fields into variables. Here's a sample flat file: SN,Account,IPaddress W120394YF,adam,10.0.20.2 W394830PR,betty,10.0.20.3 W847582TD,charlie,10.0.20.4... (7 Replies)
Discussion started by: da2357
7 Replies

10. Shell Programming and Scripting

Awk to convert a flat file to CSV file

Hi , I have a file with contents as below: Contract Cancellation Report UARCNCL LOS CODE DATE REAS TYPE AMOUNT AMOUNT LETTER BY ========= ======= ==== ==== ==== ========= ==== ==== 8174739 7641509 1S NONE CRCD 30-JUN-2008 NPAR N .00 .00 CCAN 8678696 8091709 1S NONE DDEB 30-JUN-2008... (14 Replies)
Discussion started by: rkumudha
14 Replies
Login or Register to Ask a Question