topic Re: How to Expose Excel Hyperlink URL as Text in Talend Studio

How to Expose Excel Hyperlink URL as Text

Anonymous — Tue, 30 Jul 2013 22:48:27 GMT

New Member here... apologies if I'm missing something obvious.
I have a MS Excel input file with a column that contains a Hyperlink URLS. So what you see / is visible within the cell is "Hello World", but there is a URL link within that text that contains " https://helloworld.com".
What I need to do is exposed the embedded URL from this column as text. After which I can parse text string to extract a key value.
After doing some research, believe I can do the parsing via "tExtractRegexFields". What I have not been able confirm is now to expose the hyperlink URL as text so I can parse.
Any thoughts or suggesting?
Thanks,
Don Miller

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Wed, 31 Jul 2013 05:27:08 GMT

you can used tFileInputExcel and then use tMap to make key pair value. hope this will help

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Wed, 31 Jul 2013 07:12:27 GMT

I will check, if the Apache POI API provides the actually URL link. If I succeed with it, I can provide a solution.

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Wed, 31 Jul 2013 21:56:42 GMT

I have added the necessary capabilities to my component tFileExcelSheetInput.
You need also the component tFileExcelWorkbookOpen
http://www.talendforge.org/exchange/index.php?eid=663&product=tos&action=view&nav=0,1,1
http://www.talendforge.org/exchange/index.php?eid=623&product=tos&action=view&nav=0,1,1

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Thu, 01 Aug 2013 01:46:07 GMT

Thanks so much for the enhancement Jlolliing. Can you point me to some written instructions on how to apply the new features to my installed instance on my PC?

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Thu, 01 Aug 2013 06:49:06 GMT

Here is a help article about installing user components.
https://help.talend.com/search/all?query=Installing+a+custom+component&content-lang=en
I recommend using the Exchange feature in Studio in case of you use the open source edition.

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Sun, 04 Aug 2013 23:13:26 GMT

Hi Jlolling,
So within Talend Open Studio, went to the Exchange link downloaded "tFileExcelWorkbookOpen" and "tFileExcelSheetInput".
Installed "tFileExcelWorkbookOpen" first. Then "tFileExcelSheetInput".
In can search and see "tFileExcelWorkbookOpen" on the Palette under File/Spreadsheet. But unable to search or find "tFileExcelSheetInput".
Any suggestions?
Don

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Mon, 05 Aug 2013 20:33:46 GMT

Please describe the steps you followed to install the component. Did you used the Exchange view in studio or did you followed the instruction in the article?
The best and reliable way is using the exchange view.
Let us assume you installed the components in the user component folder (e.g. c:\user_components) then check if you have sub dirs with the name of the components. The article unfortunately does not explain you have to extract the zipped component file with the included directory.
After coping the components into the user component folder you have to open your preferences: Talend->Components and click on Apply even if you have changed nothing and the path was already correct.

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Mon, 05 Aug 2013 20:36:01 GMT

Please check the directory (here the path on my computer):
/Volumes/Data/Talend/TOS_DI-r105421-V5.4.0M1/plugins/org.talend.designer.components.localprovider_5.4.0.M1_r105421/components/ext/exchange
In this dir you should find for every installed component a directory.
BTW which Talend release do you use?

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Tue, 06 Aug 2013 04:43:48 GMT

I'm using Open Studio TOS_DI-Win32-r104014-V5.3.1 via the "TOS_DI-win-x86_64.exe".
Loaded the components via the "Exchange" link within Open Studio.
See the 2 installed component directories here:
C:\Talend\TOS_DI-Win32-r104014-V5.3.1\plugins\org.talend.designer.components.localprovider_5.3.1.r104014\components\ext\exchange
One exist for the tFileSheetInput:
C:\Talend\TOS_DI-Win32-r104014-V5.3.1\plugins\org.talend.designer.components.localprovider_5.3.1.r104014\components\ext\exchange\tFileExcelSheetInput
Here the tFileExcelWorkbookOpen:
C:\Talend\TOS_DI-Win32-r104014-V5.3.1\plugins\org.talend.designer.components.localprovider_5.3.1.r104014\components\ext\exchange\tFileExcelWorkbookOpen

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Tue, 06 Aug 2013 21:06:36 GMT

Your file structure is OK. I cannot see any problems. It looks like mine studio.
What about the content of the component directories? Could you post the file list here?
Can you please take a look into the log file of the studio:
C:\Talend\TOS_DI-Win32-r104014-V5.3.1\workspace\.metadata\.log
Is there any message related to our problem?
It would be interesting if some - so called - translators classes was created:
C:\Talend\TOS_DI-Win32-r104014-V5.3.1\workspace\.JETEmitters\src\org\talend\designer\codegen\translators\file\spreadsheet\
Here you should find a file like TFileExcelSheetInputBeginJava.java
(I assume your workspace in the default location)
I have tested the components also under release 5.3.1 und currently under 5.4.0M1.

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Tue, 06 Aug 2013 22:59:17 GMT

Which components and the directories specifically do you need? Also, I'm not seeing a method to post physical files to this discussion topic.
Log file has nothing specific to "tFileExcelSheetInput". I do see entries for "", "tFileInputExcelProcess", but these look like on going logging vs. errors.
No 'spreadsheet" directory exists after "C:\Talend\TOS_DI-Win32-r104014-V5.3.1\workspace\.JETEmitters\src\org\talend\designer\codegen\translators\file".
When I search for "tFileExcelSheetInput" in the Pallet on the left side of Open Studio, the only hit I get is in the "Misc" folder and the only element that shows is "Note".

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Wed, 07 Aug 2013 13:14:44 GMT

Hi Don,
I am running out of ideas what happens in your installation. What about a WebEx session to take a look at you installation and whats wrong? I am probalby not the best supporter though thats why I prefer a personal view on the problem. We can plan appointments via email: jan.lolling@cimt-ag.de. I would be able to make a session this evening from 20:00. It is ok for you or to late? The only spare time earlier is on Friday after noon.
Best regards
Jan Lolling

Re: How to Expose Excel Hyperlink URL as Text

Anonymous — Thu, 08 Aug 2013 00:36:20 GMT

Thanks you for all of your help so far. What time zone are you in?