Hi, I didn't get exactly the logic of the exclusion part, but as for working with the files I'd read all the folder with a * and only read the filename
Then you will have a table with all filenames.
After that I'd keep on a table all the valid names that already exist and load the files like this:
Where not exists(actualFileBaseName,SavedFileBaseName)
Hope it helps!
but I could have file with same Scenario,Year and Period but a different Progressive,
for example: ACT_2017_08_20170801_00 and ACT_2017_08_20170801_01 and I want replace data, instead if I have a file name like ACT_2017_12_20170801_00 i want concatenate the file that just exist from first load.
If I understand:
Load the distinct file names from history and from the directory
in a table with two fields
Left join this table grouping by field 1 to find the max of field2 (by field1) and add a flag field
You get a table with the file to load (flag)
field1, field2, flag
ACT_2018_01_20180131, 01, 1
ACT_2017_08_20170801, 01, 1
Load from history and from the directory the record or files with the flag = 1
Script below, files in the attachments
(txt, codepage is 1252, embedded labels, delimiter is ',', msq);
Right Keep (File)
Max(Progressivo) as Progressivo
DROP Table rk;