One Star

How to Expose Excel Hyperlink URL as Text

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
13 REPLIES
Five Stars

Re: How to Expose Excel Hyperlink URL as Text

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

Re: How to Expose Excel Hyperlink URL as Text

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

Re: How to Expose Excel Hyperlink URL as Text

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
One Star

Re: How to Expose Excel Hyperlink URL as Text

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

Re: How to Expose Excel Hyperlink URL as Text

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.
One Star

Re: How to Expose Excel Hyperlink URL as Text

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
Seventeen Stars

Re: How to Expose Excel Hyperlink URL as Text

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.
Seventeen Stars

Re: How to Expose Excel Hyperlink URL as Text

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

Re: How to Expose Excel Hyperlink URL as Text

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
Seventeen Stars

Re: How to Expose Excel Hyperlink URL as Text

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.
One Star

Re: How to Expose Excel Hyperlink URL as Text

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".
Seventeen Stars

Re: How to Expose Excel Hyperlink URL as Text

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
One Star

Re: How to Expose Excel Hyperlink URL as Text

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