Excel formula not working in Talend

[Anonymous]

Talend flow is given below

InputDBTable-->Excel-->tMap-->OutputDBTable (Input and output table is same)

Here, excel has formula in last 2 column's and i need to pull those results back to OutputDBTable. I have given the below formula in tMap but its showing me an error. Have anyone gone thru this scenario? Please suggest a solution to it.

"=IF(ISBLANK(B5),"",IF(OR(AND(ISBLANK(M5),ISBLANK(I5)),ISNA(VLOOKUP(B5,Demographics!C:F,4,FALSE))),0,IF(AND(OR(C5="Yes",ISTEXT(VLOOKUP(G5,CancerDx,1,FALSE))),ISNUMBER(D5),D5<=MPend,OR(AND(OR(H5="Yes",ISTEXT(VLOOKUP(K5,RadEnc,1,FALSE))),I5>=MPstart,I5<=MPend),AND(OR(L5="Yes",AND(ISTEXT(VLOOKUP(O5,PainEnc,1,FALSE)),ISTEXT(VLOOKUP(R5,Chemo,1,FALSE)),ISTEXT(VLOOKUP(U5,Chemo,1,FALSE)))),M5>=MPstart,M5<=MPend,IF(ISERR(DATE(YEAR(M5),MONTH(M5),DAY(M5)-30)),,P5>=DATE(YEAR(M5),MONTH(M5),DAY(M5)-30)),P5<=M5,S5<=DATE(YEAR(M5),MONTH(M5),DAY(M5)+30),S5>=M5))),1,0)))" 

[Jesperrekuh]

Unzip your xlsx, find your sheetname as .xml file, its pretty easy!

unzip_xlsxl_browse_sheetname.jpg

[Anonymous]

I didn't get that, can you please elaborate? Are you asking me to convert xlsx to xml and then use it in Talend?

[Anonymous · Accepted solution]

issue has been resolved by giving the formula in context variables in tMap.

[Jesperrekuh]

A .xlsx  file is a zip archive. No conversion is needed. just unarchive.
So if you unzip the file... there are multiple xml files which you could read with xml reading.

[Anonymous]

how to unarchive, please elaborate