RegEx Assistance Needed

[thenning]

Hi everyone!  I'm very new to using RegEx and cannot seem to figure out how to accomplish the following with these file names from the Directory tool:

1. Only looking to pull in the .csv files with the naming convention of CPSC_Contract_Info_YYYY_MM.csv or CPSC_Enrollment_Info_YYYY_MM.csv (don't need any that are labeled as "old", etc.)
2. Want to change the underscore in between YYYY_MM to a hash mark (YYYY-MM)
3. Isolate YYYY-MM in its own column

Any assistance would be greatly appreciated.  Thanks!

[Luke_C · Accepted solution]

Hi @thenning 

Here's some examples:

Filter files: 

REGEX_Match([FileName], 'CPSC_Contract_Info_\d{4}_\d{2}.csv')
OR
REGEX_Match([FileName], 'CPSC_Enrollment_Info_\d{4}_\d{2}.csv')

Replace underscore: 

REGEX_Replace([FileName], '(.*\d{4})(_)(\d{2}.*)','$1-$3')

Isolate yyyy-mm:

REGEX_Replace([FileName], '.*(\d{4}.\d{2}).*','$1')

[thenning]

Thanks @Luke_C, that did it.  I appreciate your help.

[mark007]

Hi @thenning - as someone who has only recently fallen down the rabbit hole of regex, I find this website a great source for regex.  https://regex-generator.olafneumann.org/. You can paste your expression in and using the UI, isolate how you want to identify it and it will produce the required regex.  It is by no means foolproof, but a good place to start and then make further tweaks to get to what you finally need.

[tandon]

This site is really great to test regex  https://regex-generator.olafneumann.org/. Thanks @mark007