Parse File Name to Add to Table

[kthakk7]

Hello,

New to Alteryx...

I am using the directory tool to pull in a folder filled with files and need to filter for file names that consist of "Tax Return" . I was able to pull the directory tool - locate the folder - add filter tool - filter for Tax Return files. At this point, I have a date and state name in each of the file names that I need to extra and join to the data table.

Is this possible? I was trying to find a post with a similar issue, but did not find any. Can I get some guidance please?

Thanks

[Luke_C]

@kthakk7 Can you provide some sample file names? This is something I routinely do so it's definitely possible. 

[kthakk7]

Sure. Attached are 3 sample files I put together.

I am looking to parse so I am left with the year, pt1, the combined date, city name. I reached this point and added the select tool to rename the columns, but not sure how to now take those parsed field names mentioned and join them back to the data. The data that I need those field names to join to is on the result tab of each of the excel files.

Overall, I should end up with the following columns: All the data from the result tab + year + pt1 + combined date + city name. Then I need to take the three separate files and join them together into 1 single file if possible...

[Luke_C]

@kthakk7 Can you check if the attachments went through?

Edit - see them now.

[Luke_C]

Hi @kthakk7 

Take a look at the attached. I had to use a batch macro to read in all the files since they had different schemas, but the key here is in the input tool to select the option to include the file name in the data. From there it's just a matter of parsing out the file name. I used regex to do this which assumes a standard file naming convention. If you need the date further formatted you can use the datetime functions, let me know if you have any questions.

[kthakk7]

Looks like I am unable to download the attachment. For the regex tool what selections were made there to parse and see the data joined like your last screenshot?

[Luke_C]

Hi @kthakk7 

Don't see any issues on my end with the attachment. See below for the Regex. No joining is occurring. This data is coming in from the input settings noted above.

(\d{4})\s(\w+)\s(\d+)\s\d+\s(.*)