Load the latest file of a folder

[#1]

I have a folder called Folder1, and in the folder files calld a-13-01-2009.txt and so on with different dates. I can read with s FOR all the files, but takes me too long to determine which one is the latest one so I can load it. Any ideas how I can read a sorted folder so the first file I find to load is the latest and I don't have to loop through the whole folder?

Any ideas would be much appreciated.

[disqr_rm · Accepted solution]

Would this be fast enough?

for each File in filelist ('C:\myFolder\*.*')
Folder:
Load '$(File)' as Name,
 FileTime( '$(File)' ) as FileTime
 autogenerate 1;
next File
LatestFile:
first 1
Load
 Name,
 FileTime,
 1 as dummy
Resident Folder
Order By FileTime DESC;
drop table Folder;

Then you can peek into Name and store into a variable, then use variable to load the data.

[#3]

Hi Rakesh,

Thanks for your reply.

I had something similar to that but the problem is that I have somethings like 2000 files in that folder, so the loop will take a bit long. I was thinking that may be something like filelist but reading the first entry if the filelist could return a csv file with the filenames sorted already by last modified or file created or something like that. Let's keep the post open and see if we get another idea, if not the way you suggest which is similar than the one I have at least works. cheers.

Question still open...

[#4]

Hi Fed, I don't have a technical solution, but more a generic one. If you are interested in loading only the latest file, why don't you consider archving the other files to a separate folder. That way, you can avoid unwanted looping.

[prieper]

Hi,

had posted recently a sniplet using the Filelist:

http://community.qlik.com/forums/t/20414.aspx

Believe that the above solution opens each file. We use similar script like the above for some hundreds pretty big text-files located on a distant server with rather low connection-speed

Have Fun!
Peter

[disqr_rm]

oh! I see your problem.

The best solution I can think of is

1. create a bach file (as below) and place it in the directory. Make sure to change extension in the script. I used csv as an example:

@echo off
rem *** Delete old autogenerated files
del AllFiles.tmp
del LatestFile.tmp
rem *** Write list of all files in AllFiles.tmp forted descending by datetime
dir /b/o-d *.csv > AllFiles.tmp
rem *** Transfer first line from AllFiles.tmp to LatestFile.tmp
for /f "delims=" %%a in (AllFiles.tmp) do (
 echo %%a > LatestFile.tmp
 exit /b
)

2. Then in your QV Script using EXECUTE command, you can execute this batch file and after execution is done you can read LatestFile.tmp. This will have the name of the latest file. Or you can always remove FOR loop in the batch and read first line of AllFiles.tmp in your QV script.

Hope this makes sense.

[#7]

Hi Guys,

Thanks for all your comments and the time spent on this subject, I appreciate that.

The idea of the batch file is interesting, although the folder with the text files is read-only, despite the fact I could create a folder locally, schedule a task and keep the name of the latest file, that is an interesting approach and will keep it in mind, but mantaining in extra file is one more step, that would be the draw back.

One clarification is that I will just read the latest text file and will not keep history of what has been read, everytime I run the reload then the latest file will be reloaded, no qvd files will be kept.

At the moment my script is working (similar to what Rakesh mentions) , but in the future I will have thousands of files there and the speed finding the latest file could be an issue.

[#8]

add this macro then call it from your script

Function GetNewestFile(ByVal sPath)

sNewestFile = Null ' init value

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFolder = oFSO.GetFolder(sPath)

Set oFiles = oFolder.Files

' enumerate the files in the folder, finding the newest file

For Each oFile In oFiles

On Error Resume Next

If IsNull(sNewestFile) Then

sNewestFile = oFile.Path

dPrevDate = oFile.DateLastModified

Elseif dPrevDate < oFile.DateLastModified Then

sNewestFile = oFile.Path

End If

On Error Goto 0

Next

If IsNull(sNewestFile) Then sNewestFile = ""

GetNewestFile = sNewestFile

End Function

[#9]

Thanks everyone for your input, I got some really good ideas.