Alteryx Designer Desktop Discussions

ulrich_schumann · ‎05-23-2016

Hello, I am collecting data from a SharePoint via the 'Directory' tool and want to detect the file type via FindString to separate the file extension (like e.g. DOCX). I useed the formula: Right([FileName], (Length([FileName])-(FindString([FileName], '.'))-1)). This is working as long no '.' are used within the file name. Once there is a '.' in the file name my formula is no longer working. Is there any kind of 'find the last string' of the expression or trigger Alteryx to start searching the string from the right? Or any ideas how to separate the file extension?

Thanks, Uli

jdunkerley79 · ‎05-23-2016

Use a REGEX_Replace:

REGEX_Replace([FileName], ".*\.([^.]+)$", "$1")

This will extract the bit after last .

ulrich_schumann · ‎05-23-2016

Thanks, it is working great. Unfortunately I do not understand the syntax. Could you please give me some inside?

Uli

jdunkerley79 · ‎05-23-2016

Of course.

REGEX_Replace([FileName], ".*\.([^.]+)$", "$1")

The REGEX_Replace function allows you to replace a string with pattern matching.

The first argument is the string to work on.

The second is the pattern. Will break this down below.

The last is the replacement. In this case $1 means replace match with first marked section.

Looking at the pattern:

".*\.([^.]+)$"

The .* at the beginning says match any character. The * means 0 or more matches. This is a greedy expression so will take as much as it can.
The next \. says find a ..
The brackets around the next section, create a marked section. The [^.]+ says to match more than 1 character not equal to a .
The final $ says that it should be until the end of the string.

This should work on all standard windows filenames but would also match .gitignore for example.

Hope that gives you some understanding. Take a look at http://community.alteryx.com/t5/Alteryx-Knowledge-Base/How-To-Get-Started-With-Regular-Expressions/t...