Magic ETL - string operations help

Moxciano1
Moxciano1
in Dataflows

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.

Comments

  • MarkSnodgrass
    MarkSnodgrass Portland, Oregon 🥷

    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 <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • GrantSmith
    GrantSmith Indiana 🥷

    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 Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

Categories