[resolved] 'Fetching values from lookup tables in excel using Talend

[tYrannoSaurusRex_1]

Hi,
I was trying to load data from an excel sheet which had formulized fields. The values though being displayed were actually just formulas. These values were not being accepted by talend. Is there any way to read them or do I need to convert them into proper values before working on them?

[Anonymous]

Hi,
When talend reads excel file, it will show the data as a result of formula for that cell...
Can you pl put a screenshot of your job which shows the formula on the console and not the data...
Vaibhav

[tYrannoSaurusRex_1]

Sorry for wrong information, actually data in main required sheet is coming from other lookup sheets. Thus it looks like formula.. something like this, =IF(Sheet1!$E$13="",0,Sheet1!$E$13)
I was reading about tUnite... will it help me in this case???

[Anonymous]

Hi,
TalendHelpCenter:tUnite centralize data from various and heterogeneous sources.(the schema structure from various sources must be same ).
Could you please elaborate your case with an example with input and expected output values?
Best regards
Sabrina

[Anonymous]

Hi,
I think still there is some missing info..
Talend has nothing to do with what references you have in your main sheet... it looks only for the results displayed in your main sheet..
If you are not getting the data it means that when you open an excel, there is no data displayed there as well... This implies that the reference sheet is not there in that excel...
Vaibhav

[tYrannoSaurusRex_1]

hi,
Let me explain the structure of excel workbook.
Sheet1
Sheet2
Main_Sheet
Main sheet has no data of its own, it just pulls data from Sheet_1 and Sheet_2 using =IF(Sheet1!$E$13="",0,Sheet1!$E$13) like codes and displays.
In Main sheet, proper values are displayed, say, 100 and so on. But if shown value 100 is pulled from Sheet1, field E,13, then when clicked on 100 in main sheet, the formula bar in excel sheet shows =IF(Sheet1!$E$13="",0,Sheet1!$E$13)
When used in tExcelInput, when selecting sheet, data is previewed as =IF(Sheet1!$E$13="",0,Sheet1!$E$13) when selecting file path. And in next step where encoding and other details are set, there all these values are displayed as 0.

[Anonymous]

Hi,
Messages are contradictory... can you put a screenshots... preview can't display the formula if original sheet displays correct values..
If preview displays forumula it can't display 0 in consecutive screen...
Pl attach screenshots... this will help to understand the issue.
Thanks
Vaibhav

[tYrannoSaurusRex_1]

This is how it looks

[tYrannoSaurusRex_1 · Accepted solution]

Hi all,
Thanks for your help and suggestions but, I found solution to this problem.
The sheets which were being used as lookup, say Sheet1 and Sheet2, were hidden. I just unhide them and tExcelInput starts recognizing the values.
Right click on tab name at bottom of excel sheet and click on unhide from pop up menu. In unhide menu, select the hidden tabs to reveal them. Unhide all and save the excel workbook. Now use it in tExcelInput and values will be visible in preview screen (Step 3/4). In screen (2/4), only formulas will be seen.
I don't know if the go in database properly, need to work on that....
Thanks all...
Hey xdshi,
Any walk around in Talend for this that I ought to know??

[Anonymous]

Hi
Thanks for sharing the resolution.
I don't think there is any workaround to unhide sheets using Talend. This could be a feature request you can do on our Bug tracker though: https://jira.talendforge.org/browse/TDI
If the issue is closed, may I ask you to click the "Set this topic as resolved" link which is right underneath your initial post? This way, other users will be informed that this thread has been resolved.
Many thanks