Magic ETL - string operations help

Reply
Highlighted
White Belt

Magic ETL - string operations help

If i have an excel spreadsheet that contains the following column called file path:

FilePath
BUILDING\200303379 Files\Receipt20181213_20200107_133244.pdf
BUILDING\200303379 Files\Receipt20181213_20200107_133309.pdf
BUILDING\200410340 Files\HIDAWAY AVE 27227 (200410340)_CASE DOCUMENT.pdf
BUILDING\29700587 Files\Receipt20181213_20200107_133333.pdf
BUILDING\29700587 Files\Receipt20181213_20200107_133350.pdf
BUILDING\29804878 Files\Receipt_20181213_144740.pdf
BUILDING\29804878 Files\Receipt_20181213_144805.pdf
BUILDING\29804878 Files\Receipt_20181213_144809.pdf
BUILDING\29804878 Files\Receipt_20181213_144810.pdf
BUILDING\29903009 Files\Receipt_20181204_090007.pdf
BUILDING\BLD05-04079 Files\Receipt20181213_20191223_095745.pdf
BUILDING\BLD05-04079 Files\Receipt20181213_20191223_095813.pdf
BUILDING\BLD06-02296 Files\Receipt20181213_20190523_101859.pdf

 

Is there a way to use magic etl string operations to only give me the file name?

FileName
Receipt20181213_20200107_133244.pdf
Receipt20181213_20200107_133309.pdf
HIDAWAY AVE 27227 (200410340)_CASE DOCUMENT.pdf
Receipt20181213_20200107_133333.pdf
Receipt20181213_20200107_133350.pdf
Receipt_20181213_144740.pdf
Receipt_20181213_144805.pdf
Receipt_20181213_144809.pdf
Receipt_20181213_144810.pdf
Receipt_20181204_090007.pdf
Receipt20181213_20191223_095745.pdf
Receipt20181213_20191223_095813.pdf

 

Any ideas?  Thanks.

Highlighted
Red Belt

Re: Magic ETL - string operations help

You can do this with the Split Columns function in the ETL. Assuming your structure has 2 folders and then the filename, it would look like this:

splitcolumn.PNGThis would result in the following based on your data:

filepath.PNG




**Make sure to like any users posts that helped you and accept the ones who solved your issue.**
Highlighted
Purple Belt

Re: Magic ETL - string operations help

If you don't care about the folders then you can use the Replace Text widget with the following regular expression (and replace with the empty string). It will handle any number of folders.

 

^.*\\

See the attached screenshot for the configuration options.

 

Screen Shot 2020-01-30 at 3.42.52 PM.png



**Was this post helpful? Click the heart icon**

**Did this solve your problem? Accept it as a solution!**
Announcements
Win free lodging at Domoapalooza! We want you to share you favorite Domo tips and tricks. Click here for more details!