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
Solved! Go to Solution.
Use a REGEX_Replace:
REGEX_Replace([FileName], ".*\.([^.]+)$", "$1")
This will extract the bit after last .
Thanks, it is working great. Unfortunately I do not understand the syntax. Could you please give me some inside?
Uli
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:
".*\.([^.]+)$"
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...
Alteryx has a group of functions available to use in the Formula tool for dealing with file paths. The one you're after is FileGetExt.