Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!

Extract Hyperlink

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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")
Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

Actually the resulting hyperlink data sits in the 

 

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

 

Ben

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

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

Highlighted
6 - Meteoroid

Hi jdmalagon,

 

Did you got any solution for this?

 

Regards,
Srinivas.

Labels