Assign Excel file name to a variable

[divya_anand]

Hi,

I have excel files with the name that include the date. I would like to extract this date in the file name and assign it to a variable.

Any thoughts how I could achieve this?

I use the below expression to extract the date

Date(Date#(SubField(FileBaseName(),'-',1),'YYYYMMDD'),'DD-MMM-YYYY')

Thank you.

[Anonymous]

Can you provide the sample Filename??

[Anonymous]

Hi divya,

Your expression looks fine. To assign a value to a variable you can use Peek() after the load statement.

[Anil_Babu_Samineni]

Try to split expression and check then date and date#

[Anonymous]

you cant assign variable properly in a load statement.

can you do the assignment of variable and load statement separately?

[Ralf_Heukäufer]

Hello,

is it one file or more?

If it is one File and the data is in your table you can try something like this:

Date(Date#(SubField(FileBaseName(),'-',1),'YYYYMMDD'),'DD-MMM-YYYY')  as Filename

Temp_Filename:

Load Max(Filename) as tempMax Resident NameofyourTable;

let v_Filename = FieldValue ('tempMax',1);

Drop Table Temp_Filename;

In the case you have more than one filenames you must work with a for each loop and a variable instead of the filename.