Creating a variable from a file name

[Anonymous]

How would I create a variable from a file name?

For example,

I have an excel file with the name TimeTrackingReport_08.20.2017.xlsx

I want to create the variable vDate to pull the date depending on the file name

[its_anandrjs]

I believe you have single file then use the FileBaseName(), FileName(0 for this and store in variable.

[its_anandrjs]

Load your table this way with FileBaseName() you can use FileName() also

Ex:-

Tab1:

LOAD Right(FilebaseName(),10) as Date

FROM

[..\TimeTrackingReport_08.20.2017.xlsx]

(ooxml, no labels, table is Sheet1);

LET vDate = Peek('Date',0,'Tab1');

[Anonymous]

That works great, thanks!

Now I just need to format the date as mm/dd/yyyy

(I changed the file names to say  08202017 instead of 08.20.2017)

I tried the following :

Date(DATE#(Right(FileName(),10,'MMDDYYYY'),'MM/DD/YYYY') AS DATE

and got

Error in expression:

Right takes 2 parameters

[Frank_Hartmann]

Date(DATE#(Right(FileName(),10),'MMDDYYYY'),'MM/DD/YYYY') AS DATE

[Anonymous]

Missed that, thanks!

I didn't get an error, but the output is incorrect. It gives me "-"

[its_anandrjs]

Here it is

Date(Date#( Right(FilebaseName(),10),'MM.DD.YYYY'),'MM/DD/YYYY') as Date

Your complete code is

Tab1:

LOAD

Date(Date#( Right(FilebaseName(),10),'MM.DD.YYYY'),'MM/DD/YYYY') as Date

FROM

[..\TimeTrackingReport_08.20.2017.xlsx]

(ooxml, no labels, table is Sheet1);

LET vDate = Peek('Date',0,'Tab1');

[its_anandrjs · Accepted solution]

I suppose this worked for you check this

If you remove dot then number changes

Date(Date#( Right(FilebaseName(),8),'MMDDYYYY'),'MM/DD/YYYY') as Date

[Frank_Hartmann]

Date(DATE#(Right(FilebaseName(),8),'MMDDYYYY'),'MM/DD/YYYY') AS DATE