lcb lcb - 1 year ago 58
Bash Question

split up (with specified delimiter) a selected column

I have a tab-delimited file and want to modify it. The last column is pipe-delimited and I'd like to split that column up (from pipe to tab) while avoiding splitting up other columns with pipes.

This works to convert pipe to tab but I'm unable to have it do the splitting only on a selected column 13. Is there a way to have this work just on the last column without having to specify it?

awk -F'|' '$13=$13' OFS="\t" inputfile.tsv > split.tsv

Answer Source

Let's consider this tab-delimited test file:

$ cat file
a|b     c|d     e|f     g
one     two     three   four

To break up the third column on |:

$ awk -F'\t' '{gsub(/[|]/, "\t", $3)} 1' OFS='\t' file
a|b     c|d     e       f       g
one     two     three   four

For your file, you will want to replace $3 with $13.

awk -F'\t' '{gsub(/[|]/, "\t", $13)} 1' OFS='\t' file

Or, to replace the last column, whatever column it is, use:

awk -F'\t' '{gsub(/[|]/, "\t", $NF)} 1' OFS='\t' file

How it works

  • -F'\t' sets the field separator on input to a tab.

  • gsub(/[|]/, "\t", $13) replaces | with a tab in field $13.

  • 1 is awk's cryptic short-hand for print-the-line.

  • OFS='\t' tells awk to use a tab as the field separator on output.

Alternate form

It may be clearer and easier to maintain if \t is coded just once instead of three times. In that case (hat tip: Ed Morton):

awk 'BEGIN{FS=OFS="\t"} {gsub(/[|]/, OFS, $NF)} 1' file
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download