Trying to get Input Data tool to output excel File Path without the sheet name

[Ekrasnow]

I am building an Alteryx workflow to process audits which are saved individually on an excel template in a folder.  There is a separate excel file for each audit and the goal is that I will move the files to a processed folder once the workflow is complete.  In order to do that I need the File Path but my workflow isn't reading the file Paths when they include the sheet name. 

What I get:  K:\Audit Results\TESTFOLDER\Test Tausits\I_Audit_076543210.xlsx|||'Initials$'

What I want: K:\Audit Results\TESTFOLDER\Test Tausits\I_Audit_076543210.xlsx

Attached is the Macro I am using to process the data from each excel file and would like the file path to be included in each row like the data that's Cross Tabbed and Appended in the top of the container.  How would I go about getting the Input Data tool to output the file Path without the sheet name? I've also attached the excel template I am reading with this macro. 

Thanks for any help - I am new to Alteryx and data/analytics but have been trying to model this off an existing workflow and keep running into issues. 

[Kenda · Accepted solution]

Hi @Ekrasnow 

Can you add a formula tool after your input tool to edit the FileName field?

REGEX_Replace([FileName], "(.*?)\|.*", "$1")

In the above expression, regex is used to keep only the part of the string before the pipe delimitator. 

Hope this helps!

[Ekrasnow]

@Kenda 

That is exactly what I was looking for, thank you! 

[BobR · Accepted solution]

You can use the regex_replace() function in the formula tool to strip off the sheet name part of the file name. For example

regex_replace([field_w_filename], '\|\|\|.*$', '')

That will get rid of everything after the ||| to the end of the string. If that doesn't exist it won't modify the string at all.

Bob