Processing files in order of filedate

Question

Processing files in order of filedate

Nils Gruson

SSC Rookie

Points: 43
More actions
October 20, 2005 at 5:47 am

#67145

Hi,
I'm using DTS to read flat files into the database. If I have 2 files, the oldest file has to be read first. How can I accomplish this? Now the files are read one by one regardless of the filedate. Is there a way to manipulate the order of the files in the for-loop?
The function that determines the next file to be read looks like this:
Function Main
Dim oPkg
Dim oFSO, oFolder, oFile
Dim iCounter, iPosStart, iPosEnd
Set oPkg = DTSGlobalVariables.Parent
Set oFSO = CreateObject("Scripting.FileSystemObject")
set oFolder = oFSO.GetFolder(DTSGlobalVariables("FilePath").Value)
iCounter = oFolder.Files.count
' ***
' Retrieve the next file to be processed
' ***
If iCounter >= 1 then
For Each oFile in oFolder.Files
If Mid(oFile.Name,1,4) = DTSGlobalVariables("FilePrefix").Value and Mid(oFile.Name,10,1)<>DTSGlobalVariables("RDWHeaderFileChar").Value Then
DTSGlobalVariables("File").Value = oFile.Name
DTSGlobalVariables("FileFullPathName").Value = oFile.Path
DTSGlobalVariables("Log_FileName").Value = oFile.Name
DTSGlobalVariables("Log_FileCreated").Value = oFile.DateCreated
DTSGlobalVariables("Log_FileSize").Value = oFile.Size
iPosStart = InStr(oFile.Path, DTSGlobalVariables("FilePrefix").Value) + 8
iPosEnd = Len(oFile.Path)
DTSGlobalVariables("HeaderFile").Value = Left(oFile.Path, iPosStart) + DTSGlobalVariables("HeaderFileChar").Value + Right(oFile.Path, iPosEnd - iPosStart - 1)
Main = CBool(True)
End If
Next
Else
Main = CBool(False)
End If
Set oPkg = Nothing
Set oFSO = Nothing
Set oFolder = Nothing
Set oFile= Nothing
End Function
Regards,
Nils

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

What we have done here is add the DATETIME of file create to the filename. Inherently, the files are read in the proper order (at least so far (knock on wood) for the last 3+ years)...

File_10202005_0430

File_10202005_0431

File_10202005_0432

Good Hunting!

AJ Ahrens

webmaster@kritter.net

Nils Gruson SSC Rookie Points: 43 More actions · Answer 2

Funny you mention this, the datetime is included in the filename.

The last 4 chars represent the time (20:49), the preceding 3 chars represent the daynumber (290).

whatever_B06622902049.TXT

whatever_B06622911950.TXT

However, the second file was read earlier than the first file. So now I'm wondering what determines the order of the files. It works fine most of the time, but there are apparently no guarantees that the files are read in alphabetical or chronological order. If I didn't smoke already, I'd have started now!

Regards,

Nils

mbuttazzoni Old Hand Points: 314 More actions · Answer 3

hi Nils,

don't forget that a DOS based solution is still possible. You can obtain a list of files in proper order (sorted by creation date) with a command like this:

dir path /b /o:d

Put the result into a recordset and then scroll the recordset.

Quite often we are so concerned into programming tasks that we forget the basic commands of OS. I'm dealing with thousand of files per day, so started from Scripting based (FSO) solution, now I'm moving to more efficient and quick DOS based browsing techniques.

Hope this help

marco buttazzoni

Nils Gruson SSC Rookie Points: 43 More actions · Answer 4

I already wrote a solution for this. My package loops through the files, the file with the oldest date is determined, after the processing of this file this file is moved to a subfolder 'Processed', then the next file with the oldest date is determined, etc.

Thanx for the input and regards,

Nils