Capturing Date from a FileName as a Table Value

[ERPReporter]

Date is part of the filename containing data that I am loading.  The filename is:

Open Orders Report_20220801.csv

I would like to capture the date "20220801" and add it to the table data for each row.

Any examples would be appreciated.

[deepanshuSh · Accepted solution]

Hi @ERPReporter You have to use a mixture of two functions one is filebasename() and another is the left/right function or subfield if there is specific pattern in the name of the file. 

I have attached some screenshots for the functionality and its usage. Hope it helps. 

T1:
LOAD
FileBaseName() as Filebase_name,
Country,
Institution,
"Year"
FROM [lib://DataFiles/Task_Lookup_2312.xlsx]
(ooxml, embedded labels, table is Sheet2);

NoConcatenate

Load *,
Right(Filebase_name, 4) as Filebase_Date
Resident T1;

Drop Table T1; 

You can do more things with the date to isolate actual dates and year from that using more analysis and similar left/right functions. 

[ERPReporter]

Thanks.  That helps!