How to retrieve most recent file from local directory?

Hi,

Here's my situation. Every day I will be downloading extracts to a folder. The extracts are named:

20070529.Extract1.csv

20070528.Extract1.csv

20070527.Extract1.csv

20070529.Extract2.csv

20070528.Extract2.csv

20070527.Extract2.csv

So, on any given day, I will want to find the most recent versions of Extract1 and Extract2, for example:

20070529.Extract1.csv & 20070529.Extract2.csv

How would I go about doing this?

Thanks much

How about a ForEach loop on the files in the directory, with a script task inside to compare each filename to a variable. If the filename is "greater", store it in the variable, otherwise go to the next filename. You'd need two variables, one for Extract1 and one for Extract2.

After the ForEach, your variables should hold the correct filenames.

|||

I have an example that you may find helpful:

http://rafael-salas.blogspot.com/2007/02/ssis-loop-through-files-in-date-range.html

|||

Hi Rafael,

I looked at your example. I'm afraid I don't understand how you set the EndDate and StartDate variables. I see that there is hardcoded value in the variables, but what if I don't want to do this?

In my case, on any given day the file will have the previous day's timestamp.

So if today is May 31, the file will have the name:

20070530Extract1.csv

So what I want to do is just get the MOST RECENT file that is less than today's date (since on Monday the time lag will be longer than just one day, due to no files being generated on weekends).

So I don't really need EndDate, just StartDate, and in my case, StartDate must be dynamic, ie today's date.

How do I do this?

Thanks

|||

Actually, I probably do need Start and End Dates. But this becomes complicated due to weekends. That is, on Monday, the file I want is from the previous Friday.

If there's an easy way to solve this problem, I'd love to find out what it is.

Meanwhile, I am wondering if I can leverage SQL Server to help me find what I need. That is, run an EXEC SQL Task, and store the result of my query into a variable. That would solve my problem very nicely. Then it doesn't matter what day of the week I'm running this, nor would I have to concern myself will setting start dates and end dates for comparison.

Here is part of an old backup script I used to use at another job:

CREATE TABLE #FileList (FileName SYSNAME NULL)
/* populate temp table with names of backup files from local backup directory */
INSERT INTO #FileList EXEC master..xp_cmdshell 'dir /B D:\Data\MSSQL\DB_BACKUP\DBServer\*.BAK'

/* select most recent backup file from temp table for recovery */
SET @.NewestFile = (SELECT TOP 1 FileName FROM #FileList WHERE FileName IS NOT NULL AND FileName LIKE 'myFile[_]%' ORDER BY FileName DESC)

So then my question would be, how do I save the "FileName" from the above query into a variable?

Thanks|||

Well, another thought. I don't want to have to use SQL Server to solve this problem.

I just need a script that will find the most recent file from a directory. I'm not even sure I want (or need to) to bother with end dates and start dates at all. There's gotta be a very simple script that can do this?

|||

sadie,

There is more than one way to get the latest file in the directory. Perhaps you can use the first part of my blog post to loop over the files in the folder capture the date part out of the file name. Then follow Jwelch suggestion, use a script task to compare the date of the current iteration with the previous one until you get the latest one.

A simpler approach may be to implement a table where you keep the latest file date you has processed, then have the package to retrieve that value to a variable and with a For Each loop, loop through all files in the folder. Depending in your requirements you write the rest of the logic by comparing the date of the current iteration against the date in the table. At the end of the process the table should be updated with the new latest date.

|||

You may find this thread helpful:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=789950&SiteID=1

|||

Hi,

Well, I can't believe it but I figured it out myself, but thanks to Francesco Balena's "Programming Visual Basic...", I found a code snippet that worked for me.

Here is the solution to my problem. A very simple script, and I just need to output the file name to a variable:

Public Sub Main()

Dim rootDi As New DirectoryInfo("d:\myDir")

Dim newestFile As String

Dim fileDate As Integer

Dim fileDateSaved As Integer = 0 'initialize variable

For Each fi As FileInfo In rootDi.GetFiles("*.csv")

fileDate = CInt(Left(fi.Name, 8)) 'set to current file date in loop

If fileDate > fileDateSaved Then

fileDateSaved = fileDate

newestFile = fi.Name 'set variable to fName loop variable

End If

Next

MsgBox(newestFile)

Dts.TaskResult = Dts.Results.Success

End Sub

|||

Congrats! Best way to learn, as Phil says.

Aren't you missing handling for two different files? Extract1 and Extract2? The code above will only give you one filename.

Simple fix:

Repeat your For Each loop, but use a different filter for each of them:

For Each fi As FileInfo In rootDi.GetFiles("*Extract1.csv")

For Each fi As FileInfo In rootDi.GetFiles("*Extract2.csv")

You'd also need 2 variables, one per filename to store.

|||

I should probably mention, this only works because I created a separate directory for each type of file I'm receiving.

That is, Extract1's goes in their own folder, Extract2's go in another folder.

|||That does make it a bit easier |||I'm trying to use this code but when i go to type dim rootdi as new directoryinfo("c:\mydir")

it does have directoryinfo available as a command i'm using vb6|||sorry it does not have directoryinfo as a command option