Extract filename from a location string

[Dayna]

Hello All,

Could you advise on the best method to extract information from a string? I'm looking to get the information of the filename, but the string comes through with the folder location aswell as the filename (and filetype). Ideally, the only piece of information I want is the filename.

The below shows some example data, as you can see the number of folders may change as does the length of the string and the fileattachment.

C:\Temp\Folder1\Outbound\filename1.xml

C:\Temp\Folder2\Outbound\filename_test1.xml

C:\Temp\Folder1\Inbound\filename_test2.txt

C:\Temp\Folder3\Outbound\filename2.xlsx

C:\Temp\Folder1\Outbound_Test1\data1.doc

C:\Temp\Folder2\Outbound_Test2\filename11.docx

C:\Temp\Folder2\Dev\Outbound\filename12.xml

C:\Temp\Folder4\Live\Inbound\filename31.xml

C:\Temp\Folder4\Dev\2601\Outbound\Customer1\filename41.xml

So for "C:\Temp\Folder4\Dev\2601\Outbound\Customer1\filename41.xml" I would like the result of "filename41".

Any help you could provide would be great!

Kind Regards,

Dayna

[Miguel_Angel_Baeyens]

Hi,

In the LOAD statement use the function FileBaseName(). That will store into a field the value you are looking for.

Hope that helps.

Miguel

[Dayna]

Miguel,

I tried filebasename(FILESOURCENAME) AS filename - but it comes back as blank. The filename is stored within a field within the table, does this matter?

Kind Regards,

Dayna

[Miguel_Angel_Baeyens · Accepted solution]

Hi Dayna,

Another option:

=SubField(
Mid(
'C:\Temp\Folder4\Dev\2601\Outbound\Customer1\filename41.xml'
, Index('C:\Temp\Folder4\Dev\2601\Outbound\Customer1\filename41.xml', '\', -1) +1)
, '.', 1)

Hope that helps.

Miguel

[Gysbert_Wassenaar]

maybe like this: subfield(strFullPath,'\',-1) as filename

[Dayna]

Miguel,

I tried pasting your example, is it missing a closing bracket?

Gwassernaar, I will test it and let you know!

Kind Regards,

Dayna

[Miguel_Angel_Baeyens]

Hi Dayna,

Yes it was wrong. I have checked it.

Miguel

[Dayna]

Great, thank you both!

[ysalvi]

Hello 

you can use Filename() function 

You can create the columns subfield(Filename(),'.',1) as File_name 

Thanks