topic Re: [resolved] 'Fetching values from lookup tables in excel using Talend in Talend Studio

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

tYrannoSaurusRex_1 — Thu, 03 Apr 2014 06:38:00 GMT

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?

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

Anonymous — Thu, 03 Apr 2014 07:45:55 GMT

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

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

tYrannoSaurusRex_1 — Thu, 03 Apr 2014 08:28:40 GMT

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???

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

Anonymous — Thu, 03 Apr 2014 08:37:23 GMT

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

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

Anonymous — Thu, 03 Apr 2014 08:43:13 GMT

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

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

tYrannoSaurusRex_1 — Thu, 03 Apr 2014 08:57:20 GMT

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.

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

Anonymous — Thu, 03 Apr 2014 09:20:01 GMT

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

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

tYrannoSaurusRex_1 — Thu, 03 Apr 2014 10:36:41 GMT

This is how it looks

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

tYrannoSaurusRex_1 — Thu, 03 Apr 2014 10:45:27 GMT

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??

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

Anonymous — Thu, 03 Apr 2014 11:02:41 GMT

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

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

tYrannoSaurusRex_1 — Fri, 04 Apr 2014 09:06:46 GMT

Hi Esabot,
I have marked this topic as resolved. Please change the title to 'Fetching values from lookup tables in excel using Talend'. I made the post when I myself was not sure what the problem exactly was. The current title could be misleading for other people.

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

tYrannoSaurusRex_1 — Fri, 04 Apr 2014 11:27:47 GMT

Hi All,
Just made another observation in this case. Though unhiding all the sheets in excel turns out useful, it doesn't work on dates. Integers, floats and string based values have no issue when it comes to load the data in database. But it doesn't work with dates.
To tackle this, I tried making multiple logics in java, sql and in talend. But after spending numerous minutes and hours I found a tiny option in tExcelInput.
In Advanced settings of tExcelInput check the 'Convert date column to string' and select the columns with date. It helps in parsing dates easily.

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

Anonymous — Fri, 04 Apr 2014 11:34:00 GMT

Hi
You can edit the title of your thread by editing your initial post (I just did it for you on this one).
Regarding the use of "Convert data column to string", you may want to share this use case as best practice in the dedicated forum: http://www.talendforge.org/forum/viewforum.php?id=40
Up to you.
Many thanks
Elisa

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

tYrannoSaurusRex_1 — Fri, 04 Apr 2014 15:09:58 GMT

Hi Esabot,
I tried accessing above link for dedicated forum. It seems I don't have permission to enter it....