topic Re: Incremental Load in App Development

Incremental Load

Sharbel — Thu, 26 Jan 2023 14:07:32 GMT

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

Re: Incremental Load

brunobertels — Thu, 26 Jan 2023 18:11:43 GMT

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);

Re: Incremental Load

Sharbel — Thu, 26 Jan 2023 20:07:47 GMT

Hi Bruno,

Works perfect!!

Thanks a lot

Sharbel