How to add a date field with dynamic load of files from the folder

[nishanthi_8]

Hi the scenario is I  should load the excel files from the folder dynamically but there doesnt exist a date field so how to add that with the help of for loop ?

Here goes my script :

LET PATH = 'C:\CompetencyDashBoard_Source_Files\New folder\Headcount Report *.xlsx';

LET vDate= Date(MakeDate(2016,1),'YYYYMM');

for each File in filelist (PATH)

      AllData:

      LOAD [First name],[Birth date] FROM $(File) (ooxml, embedded labels, table is [Sheet1]);

next File

Here as the load continues each date field should be added to the respective file. That is if the file is loaded for the first time the date field should be Jan-2016 and for the second time Feb-2016 in this each field should be added to the respective table.

[swuehl]

Not sure I understand what you are trying to do.

You want to create a new field in the script that uses a date based on the number of times you are executing the script?

How do your excel files look like and how are they named alike? Is there maybe a date or hint to a date in the file name?

[nishanthi_8]

Yes the file name format is "Filename_Date.xlsx"

[swuehl]

Then try to extract that date from the filename, something like

for each File in filelist (PATH)

Let vFileDate = SubField(SubField('$(File)','_',-1),'.',1);

      AllData:

    LOAD [First name],[Birth date] , '$(vFileDate)' as FileDate

      FROM $(File) (ooxml, embedded labels, table is [Sheet1]);

next File

There are other ways to extract the date, just have a look at the string functions in QV, for example Textbetween().

And you may want to interpret the vFileDate value as date using Date#() function.

Get the Dates Right