Amend hyperlink

[adombayeva]

Hello community,

Could you please help to solve this issues: 

1. The excel input file contains columns with hyperlinks, but when I open the file in the Alteryx, all hyperlinks shown as a text. How to make them visible as a hyperlink in the Alteryx output? 

2. Some hyperlinks require amendment, mainly partially removing/deleting the:  =HYPERLINK("  from the beginning of the hyperlink statement and from the end:    ","TD Chart"  . Could you please advise how to cut the hyperlink from the both ends.

Example: =HYPERLINK("http://login.GoodsReviews.com/public/ViewTdData/?goodsid=1907276486","TD Chart")

Thanks,

Anar

[Jonathan-Sherman · Accepted solution]

Hi @adombayeva,

You could use a regex tool to solve this using an expression of:

=HYPERLINK\("(.*?)","TD Chart"\)

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan

[adombayeva]

Hello @Jonathan-Sherman,

Thanks for prompt respond,

Sorry if I was not clear before,

Your solution is exact for issue #2, but before that I wanted to make the hyperlink visible as a hyperlink but not as a text (TD Chart or TD Data under the columns "TD Chart" & "TD Data" in the Alteryx when I upload the Excel file into it.

Please find in the attachment:

1. Excel input file: please see the Alteryx Input Tab (this is how the input file looks like), Alteryx Output Tab (this is how the alteryx output file should look like afterwards).

PNG file: When I upload Excel as an input file and download Alteryx output file, none of them do not showcase the hyperlinks at all for me to make a corrections. 

Kind regards

[danilang · Accepted solution]

hi @adombayeva 

This was a fun early morning exercise.

The key to this workflow is to import the .xlsx as zip file, and then read the contained xml files as csv with no delimiters, no field names and a long field length

Once you get worksheet and shared string xml files, its just a matter of parsing them to extract the rows and columns, and joining the shared strings to get the values.  This workflow has an extra step of parsing the hyperlink to extract the target.

The results look like this 

 

Note: The Attached package contains your sample excel file with the Alteryx Output tab removed.  This was my first time parsing the internal structure of an Excel file and I wanted to start with a simpler file

Dan

[adombayeva]

Hello @danilang ,

Sorry for delayed response and thank you for your help! 

I am going to apply your workflow and let you know the soonest possible.

Best regards,

Anar

[adombayeva]

Hi @danilang,

I went through the proposed Alteryx workflow, at the end is it possible to leave columns C, D, E as an active hyperlinks having only the names like Data Type, Year & Country rather than a full string of hyperlink at each column?

Current output (snapshot from your workflow):

Desired output:

Thanks in advance,

Anar

[N_FirstMileAnalytics]

@adombayeva Any luck?