Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Load the latest file of a folder

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.

1 Solution

Accepted Solutions
disqr_rm
Partner - Specialist III
Partner - Specialist III

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.

View solution in original post

8 Replies
disqr_rm
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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...

Not applicable
Author

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
Master II
Master II

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
Partner - Specialist III
Partner - Specialist III

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.

Not applicable
Author

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.

Not applicable
Author

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



Not applicable
Author

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