Parsing out the file name

Question

Hello all,

I am trying to do some file name validations and I need some help in parsing out just the filename and the date.

I essentially need the output to be as follows:

File NameDateAL01312017BAR01312017BR01312017BRT01312017HDR01312017Test01312017TL01312017TT01312017

Text-to-Column would typically work using "_" as the delimiter, but this can cause issues if the user inputs the file name wrongfully (i.e. misses an "_" or inputs "__").

Any assistance would be appreciated!

Thanks

tcroberts · Accepted Answer

I've used a regex here, let me know if it helps, or if you need some clarification of what's happening:

tcroberts · Accepted Answer

No problem!

"\d\_*" says: look for a digit, followed by any number of underscores. The "\" before the underscore tells the RegEx to look for the literal character "_". This means that it would match: "6_", "5___", "1"

The round brackets are what's called a Capturing Group, meaning, parse this bit out into a new column.

"([A-Z]+)\_*" says: One or more capital letters, followed by any number of underscores. Notice that the \_ is outside of the capturing group, so that we do not keep this in our parsed column.

"(\d+)\_*.*" says: One or more digits, followed by any number of underscores, followed by any other characters.

Hopefully this was clear, let me know if there's anything you want me to expand on.

tcroberts · Accepted Answer

In that case, you'd use:

"(.*\d)\_*([A-Z]+)\_*(\d+)\_*.*"

The difference is that I've added an additional capturing group (), with .* before the \d, meaning that it will catch any number of any characters, ending with a digit.

Is this what you were looking for?