How to parse info field in Excel

Question

[kulvait]

Hi, I have my vcf file and sending it as raw to my colleagues. They open it in Excel and they specifically need to extract one field called AB from INFO field. My INFO fileld looks like this

DP=623;AB=0.058;AO=36;RO=472;QR=10544;QA=561;SRF=472;SRR=0;SAF=36;SAR=0;TYPE=snp;CIGAR=1M1X1M;LEN=1;MQM=60;MQMR=60;PARSER=FREEBAYES

Could you please help me to find Excel function to extract particular info from that to separate cell. Thanks.

[iraun]

Although this seems obvious, if you open the file specifying ; as field separator, you'll visualize each INFO field in separated columns and it could be easy to select only the AB column. Is this what you are looking for?

[kulvait]

Well this will probably do the trick. The problem might be that if there is for example DP record missing then it is not guaranteed that it will be in the same column for all records.

[Pierre Lindenbaum]

I can't resist citing that tweet:

Want to know something terrifying? There are people currently analysing whole exome variant data using Excel. I kid you not.

— Daniel MacArthur (@dgmacarthur) October 25, 2012

[kulvait]

I did have known it is a provocative question! The answer to my question seems to be here http://stackoverflow.com/questions/21674222/extract-characters-after-certain-other-characters-excel

[ivivek_ngs]

If you are colleagues are Biologists with no sense of command line programming you can actually make a table fetching the concerned field with the proper awk command since it is a tab delimited file and send them the metrics.

Answer 1

[Chris Miller]

If that's the field they're interested in, just use a script to extract that field and send it as a tab-delimited file. Perhaps something like:

cut -f 1-3,8 myvcf | perl -a -F'\t' -ne 'if($F[3] =~ /AB=([^;]+)/){print join("\t",(@F[0..2],$1)) . "\n"' >outfile.tsv

Answer 2

[Sinji]

Assuming the AB field is always in the same position in all your rows you can use awk to do this easily.

$ awk -F';' -v OFS='\t' '{print $2}' INPUTFILE > OUTPUTFILE

[Chris Miller]

Sounds like a dangerous assumption to me :) VCFs are often a mess.

[Sinji]

Fair enough, unfortunately with only one line as an example I thought i'd offer a solution anyway.