Remove column from file using awk

UNIX for Beginners Q & A
1

I am trying to delete the 5th coloumn from tst_file, tried with the below command it doesnt give the desired o/p.
Need to acheive this using awk.
Please help

tst_file

Fname|Id No.|Id Name|A/c No.|Addr|A/c Type|
Asset Mgt. Co., Ltd.|00100|Inc.|01700|1-12-1, xxx yyy, zzz-20|C|
Fit Mgt. Co., Ltd.|00900|Inc.|01800|1-50-1, xxx yyy, zzz-70|U|

o/p expected

Fname|Id No.|Id Name|A/c No.|A/c Type|
Asset Mgt. Co., Ltd.|00100|Inc.|01700|C|
Fit Mgt. Co., Ltd.|00900|Inc.|01800|U|

I tried with the below command but it doesnt give the desired o/p.

awk '{FS=OFS="|"; $5="";print $0}' tst_file
2

Hi, try something like:

awk 'BEGIN {FS=OFS="|"} {$5=""; sub(/\|\|/,FS); print $0}' tst_file

This sets the FS and OFS in the BEGIN section, so they are only set once and work on the first line. It then replaces the first occurrence of two field separators by one, which could be imprecise if any of the first fields can be empty as well.

This might be a more exact approach:

sed 's/[^|]*|//5' tst_file

or

cut -d"|" -f1-4,6 tst_file
2 Likes
3

If i need to remove the 4th column as well along with 5th can we do it in single command using sed or awk

4

You can try:

awk 'BEGIN {FS=OFS="|"} {$4=$5=""; sub(/\|\|\|/,FS); print $0}' tst_file

or more exactly:

cut -d"|" -f1-3,6- tst_file
2 Likes
5 
cut -d'|' -f-4,6- tst_file
1 Like
6

If there are more than 6 columns, then the - must be added at the end indeed. In fact it is a better general solution, since it also works with exactly 6 columns. I adjusted the second solution. The 1 can be removed or left as is, that is a matter of preference.

1 Like
7

Thanks for the response.
Is there a way that i can quote each of the field value in quotes and ignore the column header.

say if am extracting the 4th field with the below cmd -
cut -d"|" -f4 tst_file

O/p should look like
A/c No.
'01700',
'01800',

If am selecting addl. columns(like 3rd and 4th) this has to generalise.

8

Like so?

cut -d"|" -f1-4,6 tst_file | sed "1n;s/[^|]*/'&'/g"

Fname|Id No.|Id Name|A/c No.|A/c Type
'Asset Mgt. Co., Ltd.'|'00100'|'Inc.'|'01700'|'C'
'Fit Mgt. Co., Ltd.'|'00900'|'Inc.'|'01800'|'U'

% cut -d"|" -f4 tst_file | sed "1n;s/[^|]*/'&'/g"
A/c No.
'01700'
'01800'
1 Like
9

Thanks for all the responses

Further I tried to add double quotes to each field and wanted to seperate each field by comma -

This is the expected o/p

Fname|Id No.|Id Name|A/c No.|A/c Type
"Asset Mgt. Co., Ltd.","00100","Inc.","01700","C"
"Fit Mgt. Co., Ltd.","00900","Inc.","01800","U"

Tried the below command but not getting the desired o/p

sed '1n;s/[^|]*/\"&\"/g' tst_file | sed 's/[^"|]*/"&"\,/g'

Also tried with awk but no luck.

awk '
  {
    for(i=1; i<=NF; i++) {             
      gsub([^|]*,"&",$i)          
    }
    print                        
  }
' FS="|" OFS="," tst_file
10

The suggestions do not work because they were created for the output sample you posted in post#1. The output format in your last post has a different format, so then it will not work.

11

Do you suggest me to start a new thread for this.If yes will raise one

12

No that is OK, I misread your post, I thought you were surprised it did not work. But I see now it did work, but you would like to know what needs to be done to obtain different output.

13

Yes Please......Once again i thank you for all the responses

14

Try these adjustments to the earlier suggestions:

cut -d"|" -f1-4,6- tst_file | sed '1n;s/[^|]*/"&"/g; s/|/,/g'
15

can we do it using awk as well

16

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.