Difficulties with RegEx

[Jesse89]

Hi all,

I'd like to parse out some folder names and use them to organize my data.  I'm currently using a directory tool that outputs the filename and uses batch macros to combine multiple files together.  The macro works, now I'm just trying to grab the folder that each file came from as an additional data field.

Currently the File name looks like this:  \\SharedDrive\Organization\Folder Name 1\Folder Name 2\Survey.xlsx|||'Survey 2015$'

I know this can be done with multiple text to column tools using "\" as a delimiter, however, this task I do relatively frequently and would like to know how I can use RegEx to parse out that folder name.  I have previously used [-](.*) regex to treat "-" as my delimiter, but I am unsure on how to use special characters like "\".

Any help is greatly appreciated!

Thank you,

Jesse

[Claje]

Unfortunately I don't have time at this second to offer you a RegEx solution - I'm sure someone else can offer one.

Here's a solution to get the folder name using one formula:

Replace([FILENAME],RIGHT([FILENAME],FINDSTRING(REVERSESTRING([FILENAME]),'\')),'')

Replace the [FILENAME] references with the field you are using.

This will essentially look for anything right of the last "\" character (the name of the file itself), and then replace that with nothing, leaving you with the folder path only.

[MarqueeCrew]

@Jesse89,

Have you tried 

FileGetDir([Path])

Here is some more "stuff" from help ...

FileAddPaths(Path1, Path2)

Adds two file path parts, making sure there is exactly one \ (backslash) between the two paths.

Example

FileExists(Path)

Returns true if the file exists, false if it does not.

Example

FileGetDir(Path)

Returns the directory portion of the path.

Example

FileGetDir(C:\Temp\Data\file.csv) returns "C:\Temp\Data"

FileGetDir(C:\Temp\Data) returns "C:\Temp"

FileGetExt(Path)

Returns the extension of the path, including the . (period).

Example

FileGetFileName(Path)

Returns the name portion of the path, without the extension.

Example

Cheers,

Mark

[Claje]

Wow, I can't believe I literally never looked at these functions before.  That's a much more elegant solution.

[Jesse89]

Thanks Mark,

I was able to use FileGetDir([Path]) to narrow it down.  For the example used, this would give me "\\SharedDrive\Organization\Folder1\".

I then used two additional steps:  

Step 1: ReplaceFirst ([Path], "\\SharedDrive\Organization\", ""),

Output:  "\Folder1\".

Step 2: Trim([Path], "\")

Output: "Folder 1".  

It would be helpful if anyone had a RegEx solution, since the organization name could change in the future, which would render my solution ineffective.

Thank you!

Jesse

[MarqueeCrew · Accepted solution]

Try this:

Regex_replace(Path,".*\\(.*)\\$", '$1')

It might work.

Cheers,

Mark

[Jesse89]

Mark,

This led me down the right path. 

I was able to configure the RegEx Tool to parse the "FileName" field with the following Regular Expression:  .+\\(.*)\\

This let me output a new field that had only contained the folder name.

Thank you again!

Jesse