topic Re: How to read a file from folder based on file name in QlikView

How to read a file from folder based on file name

mwscott1 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: How to read a file from folder based on file name

maxgro — Tue, 17 Oct 2017 21:54:23 GMT

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

Re: How to read a file from folder based on file name

vadim_pozdnyakov — Tue, 17 Oct 2017 22:23:47 GMT

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