awk field separtor

Question

I have a file like this :

and I want the output as:

I tried using awk :

awk 'BEGIN { OFS=FS="\t" } { sub("\\..*", "", $1); print }'

and it prints the first id ENST. But when I change it to:

awk 'BEGIN { OFS=FS="\t" } { sub("\\..*", "", $5); print }'

it doesnt work.

What is wrong that I am doing here. Kindly help.

Answer 1

Assuming you want to only show the fifth id from the first field, the following Perl script should work:

perl -pe 's/^(.*?)\t//; @F=split("\\|", $1); print(@F[4]."\t")' input.tsv > output.tsv

Explanation of the Perl code:

1. Loop over the lines in the input file (this is enabled by the command-line option -p)
2. Remove everything up to the first tab character from the input line, and save the removed part (excluding tab character) into the grouping variable $1 [identified by brackets].
3. Split variable $1 at the pipe character, |, saving the split components into the array @F
4. Print the fifth field [0,1,2,3,4] from the array @F [without a line break at the end]
5. Print the remainder of the input line (this is also enabled by the command-line option -p)

Answer 2

You need the first column's 6th component when split by | and then the remaining columns of the file where \t is the delimiter. Use awk.

awk -F "\t" -vOFS="\t" 'NR==1 {print} NR>1{split($1,arr,"|"); print arr[6],$2,$3,$4,$5}' your_file

Answer 3

You could just do this in libreoffice calc using text to columns (data menu)

If you want to use awk (or maybe gawk) you could use capture groups; but rather that mess around with a fairly complex regex I'd probably do it in two stages like below

awk -F"|" '{print $5,$0}' OFS="\t" your_file|\
awk -F"\t" '{print $1,substr($0,index($0,$3))}'

The second awk prints everything except field 2