topic Re: Excel formula not working in Talend in Talend Studio

Excel formula not working in Talend

Anonymous — Sat, 19 May 2018 07:36:36 GMT

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)))"

Re: Excel formula not working in Talend

Jesperrekuh — Mon, 21 May 2018 01:20:14 GMT

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

unzip_xlsxl_browse_sheetname.jpg

Re: Excel formula not working in Talend

Anonymous — Mon, 21 May 2018 05:56:36 GMT

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

Re: Excel formula not working in Talend

Anonymous — Mon, 21 May 2018 07:20:17 GMT

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

Re: Excel formula not working in Talend

Jesperrekuh — Mon, 21 May 2018 16:04:33 GMT

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.

Re: Excel formula not working in Talend

Anonymous — Tue, 22 May 2018 05:12:58 GMT

how to unarchive, please elaborate