Extract Hyperlink

[jdmalagon]

Hello,

I have an Excel file and in the first column, I have text with a hyperlink, the macro I use it creates a column next to it and then extracts the HL from the text.

How can I do this on Alteryx?, I have tried R but it's not working. 

Thank you!

[BenMoss]

Is your input source an xlsx file? .xlsx files are actually a collection of different files which Excel puts together upon opening. In contrast, tools such as python, R and Alteryx will just fetch the data layer from the file.

My understanding is that when you input an .xlsx file into Alteryx it removes any formatting contained in the doc, such as conditional formatting, hyperlinks, text size and so on, as it is a visual layer.

However, according to the R script below (which performs this task), if we take our xlsx file and convert the extension to .zip, we can then extract the hyperlinks from the xml file for the sheet that exists within that.

Alteryx allows you to import .zip files and then you can select the sheet element, as per the script below, before performing some parsing which should pull out the element you are looking for.

The reason this is much easier in VBA is because its developed by microsoft of course and they know how there tool works and have built commands to deal with this.

Ben

library(XML)

# rename file to .zip
my.zip.file <- sub("xlsx", "zip", my.excel.file)
file.copy(from = my.excel.file, to = my.zip.file)

# unzip the file
unzip(my.zip.file)

# unzipping produces a bunch of files which we can read using the XML package
# assume sheet1 has our data
xml <- xmlParse("xl/worksheets/sheet1.xml")

# finally grab the hyperlinks
hyperlinks <- xpathApply(xml, "//x:hyperlink/@display", namespaces="x")

[BenMoss]

Actually the resulting hyperlink data sits in the 

\worksheets\_rels\[SheetName].xml.rels file which you can import and parse with Alteryx.

Ben

[BenMoss]

Fun challenge, see attached. With this you don't have to convert the file to a .zip first as we can just bring the .xlsx in with alteryx reading it as a zip (which it is).

It assumes that the hyperlink column is A.

Ben

[sdevireddy]

Hi jdmalagon,

Did you got any solution for this?

Regards,
Srinivas.

[szkz]

I have similar case where attached is the input excel file where column document name and document number is having hyperlink attached to it.  Need help in related to how to retain hyperlinks in those two column in the output excel file