Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Recognize file type with FindString

ulrich_schumann
8 - Asteroid

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

4 REPLIES 4
jdunkerley79
ACE Emeritus
ACE Emeritus

Use a REGEX_Replace:

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

This will extract the bit after last .

ulrich_schumann
8 - Asteroid

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

Uli

jdunkerley79
ACE Emeritus
ACE Emeritus

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...

 

NeilR
Alteryx Alumni (Retired)

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.

Labels