How to read a file from folder based on file name

[mwscott1]

I have a folder O:\Text Files with many text files. All the text files have the same name schema "REINSTATED_2017.10.17.txt", where the date in the file name changes for each file that is created. How can I read the files in this folder and pull back the most resent file.

[maxgro]

// all the files

tmp:

first 1 LOAD

     filebasename() as fbn

FROM

     [REINSTATED_????.??.??.txt]

     (txt, codepage is 1252, no labels, delimiter is '\t', msq);

// the most recent file

tmp2:

NoConcatenate first 1 load

     fbn

Resident tmp

Order By fbn desc;

DROP Table tmp;

let vfiletoread = Peek('fbn');

// read the file most recent

load

     *

from

     [$(vfiletoread).txt]

     (txt, codepage is 1252, no labels, delimiter is '\t', msq);

[vadim_pozdnyakov]

Hi, Melvin!

Try this script:

SET sMaxFileDate = 0;

SET sRecentFilePath = '';

For Each sFile in FileList('O:\Text Files\*')

    LET sFileDate = Date#(Subfield(Replace(Subfield(sFile, '\', -1), '.txt', ''), '_', -1), 'YYYY.MM.DD');

    If sFileDate > sMaxFileDate Then

        LET sMaxFileDate = sFileDate;

        LET sRecentFilePath = sFile;

    End If

Next sFile

If sRecentFilePath <> '' Then

    LOAD *

    FROM [§(sRecentFilePath)] (...correct specifiers) ;

End If

Best regards,

Vadim