topic Re: Extract filename from a location string in QlikView

Extract filename from a location string

Dayna — Fri, 28 Sep 2012 11:03:46 GMT

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

Re: Extract filename from a location string

Miguel_Angel_Baeyens — Fri, 28 Sep 2012 11:13:58 GMT

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

Re: Extract filename from a location string

Dayna — Fri, 28 Sep 2012 11:16:34 GMT

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

Re: Extract filename from a location string

Miguel_Angel_Baeyens — Fri, 28 Sep 2012 11:19:49 GMT

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

Re: Extract filename from a location string

Gysbert_Wassenaar — Fri, 28 Sep 2012 11:22:23 GMT

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

Re: Extract filename from a location string

Dayna — Fri, 28 Sep 2012 12:12:57 GMT

Miguel,

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

Gwassernaar, I will test it and let you know!

Kind Regards,

Dayna

Re: Extract filename from a location string

Miguel_Angel_Baeyens — Fri, 28 Sep 2012 12:17:59 GMT

Hi Dayna,

Yes it was wrong. I have checked it.

Miguel

Re: Extract filename from a location string

Dayna — Fri, 28 Sep 2012 12:21:18 GMT

Great, thank you both!

Re: Extract filename from a location string

Crimson_BI — Thu, 15 Feb 2024 08:09:26 GMT

Hello

you can use Filename() function

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

Thanks