How to Load Latest .csv file in comparing between the two folder paths

[krishna20]

Hi Folks,

I have two folders which consists of multiple .csv files , I need to compare the both folders to load only the latest file among them.

For Eg:

FolderA:

ASDF_07092022.csv

FolderB:

ASDF_0909202222.csv

(The file name ends with date format DDMMYYYY).

My script needs to load the file from FolderB. 

Could anyone suggest the optimized solution with script to achieve this story.

Thanks

Krishna

[Zapparoli · Accepted solution]

Hi Krishna.

Check the attached file for the Solution.

You just need to Change vFolderA and vFolderB for your Folder Connection Name.

SET vFolderA = 'FolderA';

SET vFolderB = 'FolderB';

Basically I read all files from both folders and get the name of them with the FileBaseName() Function

FolderA:

LOAD

    FileBaseName() AS FileNameA

FROM [lib://$(vFolderA)/ASDF_*.csv]

Then I get the most recent one using MaxString Function: 

FolderA_Temp:

Load

MaxString(Right(FileNameA,8)) as LastFileA

Resident FolderA;

After that we use the Peek() function to get the value and store it in a variable.

LET vFileA = Peek('LastFileA', 0 , FolderA_Temp);

Repeat the Same for Folder B. Now I compare both variables to see witch one is bigger: 

LET vFinalFile = if($(vFileA) > $(vFileB),

                                            '$(vFolderA)/ASDF_' & $(vFileA),

                                            '$(vFolderB)/ASDF_' & $(vFileB)

                    );

Lastly we use this variable in the Table load:

[YourTable]:

LOAD

    YourField1,

    YourField2,

    YourField3

FROM [lib://$(vFinalFile).csv]

(txt, utf8, embedded labels, delimiter is '\t', msq);

Full code is on the Attached File.

Let me know if it helps.

[krishna20]

Thanks a lot Matheus_Zapparoli . I will check your solution and get back to you on the same. Once again appreciate your efforts.

Thanks

Krishna

[krishna20]

Thanks  Matheus_Zapparoli . The logic works fine with few minor changes.