Processing files in order of filedate
-
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
-
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 -
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
-
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
-
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
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply