How to trim filepath to get from Wildcard XLSX Input

[anthony123]

Hello,

I am using the Wildcard XLSX input to union agrowing number of excel files. I want to add a column name that contains the file name of each file, which happens to contain a date.  Ideally, I would like to output the file name so I could parse out the date and then use a date/time field to convert it into an actual date field.

However, it appears the tool only outputs the entire file path. How can I trim this to grab the date, which I would then customize using the date/time tool?

For example, how would I trim the below field to grab the date, which is outputted by the Wildcard XLSX input tool:

Z:\Alteryx Input\EB-ONE\New Item Tool Exports\NewItemApplicationWeeklyReport_03022020.xlsx|||Sheet1

[Jonathan-Sherman · Accepted solution]

Hi @anthony123,

Is this what you're trying to achieve?

Piece of Regex (in parse mode): (\d+)\.xlsx

Formula to parse out date in date data type: DateTimeParse([DateValue],'%d%m%Y')

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

[Blake]

Hey @anthony123 

This should work for pull those 8 digits (the date).