Adding info to end of line if two columns match from files with different separators

Shell Programming and Scripting
1

I have two files (csv and vcf) which look exactly like this

S1.csv

func,gene,start,info
"exonic","AL","2309","het"
"exonic","NEF","6912","hom"

S1.vcf

##fileinfo
#CHROM POS ID INFO
chr1      4567     rs323211     1/1:84,104,99
chr4      2309     rs346742     1/1:27,213,90
chr6      5834     rs234492     0/1:22,765,22
chr8      6912     rs239299     1/1:56,765,13

I want to add the fourth line in the second file to the end of the line in the first file if the number in column 2 (file2) matches the number in column 3 (file1)

Also the first file is seperate using , and " (except for first line) while the second is tab seperated

The final file should look like:

func,gene,start,info
"exonic","AL","2309","het","1/1:27,213,90"
"exonic","NEF","6912","hom","1/1:56,765,13"

I have to do this for 200 files, each pair has the same name with either .csv or .vcf

2

Try this:

awk 'NR==FNR{a[$2]=$4; next}a[$6]{$NF="," FS a[$6] FS}1' S1.vcf OFS=\" FS=\" S1.csv
3

That works for the exact files I gave you, but not for my actual files.

The main difference is that my actual files are bigger and the columns for the first file are in a different place - the number to be searched is in column 34

also the column I want copied over is actually in column 10, I assume I change to $4 to $10 to get this right.

What's the $6 for?

4

Yes.

For the file S1.csv I've use the double quote as field separator. The "3th" field with a comma separator is the 6th field with the double quote as field separator.

5

ok so as it turns out, due to the csv coming from excel, my seperators look more like this:

"exonic","ALS",4567,112,44,text,"moretext","34","text,with,comma"

I got the code to work some of the time using a comma instead of quotation mark, for example if I change $6 to $34 I get three correctly adding info, if I add $36 I get a differnt five correctly adding info

can you say multiple columns could have the number in?

Thanks in advance

6

Try this. It's based on the file samples of your first post. For the second file the field separator is setted to a comma:

awk 'NR==FNR{a[$2]=$4; next}{s=$0;gsub("\"",x)}a[$3]{$0=s ",\"" a[$3] "\""}1' S1.vcf FS=, S1.csv