topic Load the latest file of a folder in QlikView

Load the latest file of a folder

Sat, 19 Sep 2009 05:59:23 GMT

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.

Load the latest file of a folder

disqr_rm — Sat, 19 Sep 2009 10:52:33 GMT

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.

Load the latest file of a folder

Sat, 19 Sep 2009 22:23:50 GMT

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

Load the latest file of a folder

Sat, 19 Sep 2009 22:50:20 GMT

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.

Load the latest file of a folder

prieper — Sat, 19 Sep 2009 23:06:34 GMT

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

Load the latest file of a folder

disqr_rm — Sat, 19 Sep 2009 23:32:47 GMT

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.

Load the latest file of a folder

Sun, 20 Sep 2009 00:50:59 GMT

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.

Load the latest file of a folder

Mon, 21 Sep 2009 11:41:13 GMT

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

If IsNull(sNewestFile) Then sNewestFile = ""

GetNewestFile = sNewestFile

End Function

Load the latest file of a folder

Wed, 23 Sep 2009 17:39:11 GMT

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