This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
I'm having the following problem when trying to load an excel file that contains hyperlink and a display text for the hyperlink. The problem is that Alteryx only show the Display text and does not load the hyperlink.
Does any one have any idea on how to solve this problem i need the Hyperlink. Please see the attached sample excel file
I'm curious to see if anyone comes up with anything else, as the problem comes in that Excel doesn't store the hyperlinks as values for the field, but rather an external relationship in a relationship xml inside the XLSX package. So, that being said, when bringing in Excel files, we bring in the values, not look for external relationships.
I have a manual way of getting these out as a reference to the cell, however it is not a guarantee, as I don't know everything about how Excel may store theses relationships, and whether having other relationships in the file will effect retrieving these ones. It works on your example. So, it really depends on whether you are just trying to get the hyperlinks once or twice, or whether you will have to do this on a variety of sheets regularly.
In the attached, I have read the Excel file twice as a zip file, bringing in a different XML from the package each time as Sheet1.xml.rels.xml contains the hyperlinks and links them to a relationship ID, whereas Sheet1.xml contains the cell ID and links that to the Relationship ID.
Take a look at the attached package to get a better idea of what I mean. If you just need to get the links on an ad-hoc basis, then this might be the way... I've shown a quick example of using CharToInt in order to get the column number as well (might mean more than B5 for instance)
Note: This is taking advantage of the way an Excel package is structured, an XLSX file is essentially just a bunch of XML files zipped up. Try unzipping a XLSX file to see what I mean. That's what the X on the end stands for...
I've got a similar instance where I am exporting a SharePoint list to xlsx. The SP list contains hyperlinks to sub-sites which are displayed as a name vs the full path. I am attempting to merge that export with some additional data and want the hyperlinks to remain active. I took a look at your workflow and I see how you are getting to the data but I am a little lost as to how I can accomplish this.
I don't have a dynamic way of achieving it. Not knowing just what your files look like, not sure what I could advise differently to above. The method I've mentioned above is really an ad-hoc kind of work-around and it may not necessarily always apply.