Incremental Load

[Sharbel]

Hello,

I want to load Excel files using the incremental method. The files are being loaded on hourly basis  (24 times a day) into designated folder in the following manner (starting at 26/01/2023 midnight) :

file loaded on 26/01/2023 time of load 07:01:01 ---> file name "Clock 2023-01-26_07-01-00.csv " this file contains all employees logins up till  7 am (from starting date 10/01/2023 up till day 26/01/2023 )

file loaded on 26/01/2023 time of load 08:01:01 ---> file name "Clock 2023-01-26_08-01-01.csv " this file contains all employees logins up till8 am (from starting date 10/01/2023 up till day 26/01/2023 ) and so forth for files loaded on 02:01:01 and so on...  (see files attached for example).

Please note that file "Clock 2023-01-26_07-01-00.csv " contains 7,542 rows  and file   "Clock 2023-01-26_08-01-01.csv " contains 7,636 rows since it contains all the additional logins made after 07:00 and till 08:00.

I would appreciate help with incremental load.

Many thanks,

Sharbel

[brunobertels · Accepted solution]

Hi 

Rather than an incremental load , why not search for the latest file available in folder ? 

Incremental load is usefull to load additional file and append them to saved files without always load all the file 

Fo your case may be have a look to this script 

Data:
first 1 load FileBaseName() as FileName,
Timestamp(Timestamp#(trim(purgechar(lower(right(FileBaseName(),19)),'data')),'YYYY-MM-DD_HH-MM-SS'),'YYYY-MM-DD hh:mm:ss') as Time
FROM [lib://TESTLASFILE/Clock*.csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

Max:
load FirstSortedValue(FileName,-Time) as Max_Time_FileName
Resident Data;
DROP Table Data;

let vMax_Time_File_Name =Peek('Max_Time_FileName',0,'Max');

Data:
LOAD *
FROM
[lib://TESTLASFILE/$(vMax_Time_File_Name).csv]
(txt, codepage is 28591, embedded labels, delimiter is ',', msq);

[Sharbel]

Hi Bruno,

Works perfect!!

Thanks a lot 

Sharbel