how to extract the date part from excel file names and store it as a column?

[hosasahana]

hi all,

I request your help  to extract the date part from excel file names and store it as a column. Basically I have multiple files, almost 100's of them .I want to extract the date (in any date format )from their file name and place it as a date column and finally store all the columns into one qvd, so that I can identify individual file in my final data set.

here are the file names eg: I want to extract the yellow part and store.

regards,

Sahana

[Vegar · Accepted solution]

If you already have a loop in order to read all the files then you can use filebasename() when reading the file. You will get a string similar to this: 'Daily Inventory__5000000_20210105070700'

You will need to extract the desired substring to get your desired date. This could be done in many ways, depending on the variation on your file names, but for your sample this should work:

Left(SubField(filebasename(), '_', - 1),8) as Date

[Vegar]

You could also take a look at this page for other string manipulating funtions: https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/Scripting/Strin...

[hosasahana]

hi Vegar,

thanks for your response. I tried the solution you sent but it gives me an error at the bracket. Not sure about the reason. Can you help?

Regards,

Sahana

[settu_periasamy · Accepted solution]

Hi @hosasahana ,

Try to use SubField instead of Substring.

[hosasahana]

thankyou very much Settu. It works.

[Vegar]

Thank you for spotting my mistake. I've edited my previous response to SubField()