concatenat and loop

[somthingmendi123]

In honor of the dear Click Sense developers
I have tables of goals for 2018 and 2019 in the model, which I chained, but I want to create a situation where every new goal file that arrives in a folder on the computer will be automatically chained to the model. That is, I have to create a loop and connection of all the data in an orderly manner.

I want to generate the script so that it is generic. that if a file from the year 2020 / 2021 is added for example to the folder - he will upload it to me as well.
I need to use a loop that will extract the year from the name of the file into a variable and use the variable to extract the appropriate tab from Excel.

How do I write the script?

[udit_k]

Below script works for sharepoint as source for excel files:-

let vQwcConnectionName = 'lib://SharePoint_Connection_Test';
let vgetfileconnection = 'lib://GetExcelFiles';

Office365Connector_ListFiles:
LOAD
    UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [web connector URL, it tripped the spam filter so removing it], qvx);

for i=0 to NoOfRows('Office365Connector_ListFiles') - 1
let vField = Peek('ListFiles_UniqueId',$(i),'Office365Connector_ListFiles');
LOAD
    *
FROM [$(vgetfileconnection)$(vField)]
(txt, utf8, embedded labels, delimiter is ',', msq)
;
NEXT i

[somthingmendi123]

I thank you very much for the answer and I am starting to work with the code and try to solve my problem
Could you please explain more what each line of code does?
Thank you

[udit_k]

Hello , 

Please find comments against the lines to define code.

let vQwcConnectionName = 'lib://SharePoint_Connection_Test';          //SharePoint Connection String
let vgetfileconnection = 'lib://GetExcelFiles';        //Excel Files connection string

Office365Connector_ListFiles:           //Table to fetch nos of excel files, there unique id
LOAD
UniqueId as ListFiles_UniqueId
FROM [$(vQwcConnectionName)]
(URL IS [web connector URL, it tripped the spam filter so removing it], qvx);

for i=0 to NoOfRows('Office365Connector_ListFiles') - 1   // loop to itereate for fetching details from each excel file
let vField = Peek('ListFiles_UniqueId',$(i),'Office365Connector_ListFiles');            //Name of each excel file
LOAD
*
FROM [$(vgetfileconnection)$(vField)]             //Loading excel files from source
(txt, utf8, embedded labels, delimiter is ',', msq)
;
NEXT i