community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Full path file name - regex help

Alteryx Partner

Hi - looking for some help using regex to extract the file name and sheet name in the full path below.  The folders will always change so needs to be dynamic.  Thought this would be something I could find in the community but wasn't able to. 


C:\Users\cireost001\Desktop\ABC\Folder\Test\Samples\Property Q4 2018 - FINAL.xlsx|||`Data$`

 

Field 1

Property Q4 2018 - FINAL 

 

Field 2

Data


Thanks!

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@cireost,

 

I'm concerned about the curly apostrophe, so I wrote the following expressions:

 

FileGetFileName([FullPath])

let's first use a simple function to get the filename for field1.

 

Then let's use the file function: FileGetExt to get the rest.  But we need to remove .xlsx||| and get rid of those extra characters.

REGEX_Replace(
	replace( 
		FileGetExt([FullPath]),".xlsx|||",''),".(.*).",'$1')

Cheers,


Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Alteryx Partner

@MarqueeCrew wrote:

 

I'm concerned about the curly apostrophe

 


I'm pretty sure you only need it when there's spaces in the sheet name, I never use it but I always create sheet names that don't contain spaces :) 

Magnetar
Magnetar

A nifty, if somewhat verbose method that I use is the following formula:

RIGHT([FULLPATH],FINDSTRING(REVERSESTRING([FULLPATH]),'\'))

This will return every character to the RIGHT of the last '\' character in the path.

Since (at least for windows), directories all utilize the '\' symbol, we can use this to isolate file names regardless of other characters.

 

EDIT:

This can also be applied to split the sheet name, as follows:

RIGHT([FULLPATH],FINDSTRING(REVERSESTRING([FULLPATH]),'|'))

Then a REPLACECHAR function, replacing ` and $ with nothing will get you to "Data".

 

The final formula for "Field2" might look something like this:

REPLACECHAR(RIGHT([FILENAME],FINDSTRING(REVERSESTRING([FILENAME]),'|')),'`$','')
Alteryx Partner

The directory tool can help you with it, just enter the folder path and it will change the results every time. If you want just to change it to output one filename you can always sort and sample the workflow.

It gives you all the information needed, including path and file name:

Untitled2.png

Use then the regex tool as in the regex description

 (\<\w+\>)(\$) 

It should work:

Untitled.png

Hope it helps!

Labels