identifying, copying, and renaming the newest CSV file for import

  • Thread starter Thread starter Bill R via AccessMonster.com
  • Start date Start date
B

Bill R via AccessMonster.com

Every hour, 2 CSV files, with a naming convention reflecting the date and
time of their creation, are added to a directory on a server. I can easily
determine which one of the 2 I am interested in with an instr function which
will return 0 for the one I'm looking for. However, narrowing it down to the
2 newest versions is where I'm having the problem.
I need to look in the directory (I need some help with that, first of all),
find the 2 newest files in the directory (I need help with this, too), select
the one I'm interested in (instr function should do that for me), and then
copy it and rename it (to "DMS_Report.CSV") within the directory, replacing
the previously copied DMS_Report.CSV.
Then all my pre-existing code will run just fine to update the old or add the
new records in the CSV file to a table in my DB.

Thanks,

Bill
 
This is doable. To give you a complete answer, I need to know what the
naming convention of the .csv files is and how you know which is the one you
want. In the mean time, go into VB Help, select the Object Browser, and read
up on the FileSearch object.
 
The file I want is:

"11_29_05_1030AM_DMS.csv"

The file I don't want is:

"11_29_05_1030AM_DMS_Meth_Full.csv"

Every hour new files are added to the directory. It might be preferable to
use the latest modified dates rather than the filenames. What say you?

I just posted a new thread, "Code doesn't find newly imported data in a
table", that's related to this module as well. Any suggestions there would be
deeply appreciated. After importing the CSV contents, the data doesn't seem
to be readable in subsequent code.

Thanks,

"Gort"

"Barada nikto"
This is doable. To give you a complete answer, I need to know what the
naming convention of the .csv files is and how you know which is the one you
want. In the mean time, go into VB Help, select the Object Browser, and read
up on the FileSearch object.
Every hour, 2 CSV files, with a naming convention reflecting the date and
time of their creation, are added to a directory on a server. I can easily
Click to expand...
[quoted text clipped - 12 lines]
Bill
Click to expand...
Click to expand...
 
I think your idea of looking for the most recent is good provided you are
confident you will alway get the correct file. So, here is how to use the
FileSearh object to do what you want:

Sub GetLatestFile
Dim fs as Object
Dim lngFileCount as Long
Dim dtmHighTime As Date
Dim strHighName As Sting
Dim strCurrFile As String
Dim strPath as String
Dim strOldFile as String

'Set up the search
set fs = application.FileSearch
With fs
.newsearch
.lookin = "C:\PathToFilesYouWant"
.filename = "*.csv"
.searchsubfolders = false
.execute
'Look for the Newest file
For lngFileCount = 1 to .foundfiles.count
strCurrFile = .foundfiles(lngFileCount)
If Instr(strCurrFile, "Meth_Full" = 0) Then ' We Dont Want this
one
If FileDateTime(strCurrFile) > dtmHighTime Then ' It is Newer
dtmHighTime = FileDateTime(strCurrFile)
strHighName = strCurrFile 'Keep track of the most
recent file
End If
End If
End With

strPath = left(strHighName,instrrev(strHighName,"\")) 'Get the path
strOldFile = strPath & "DMS_Report.CSV" 'Add the file name
Kill strOldFile 'Delete the old file
FileCopy strHighName, strOldFile Copy the new file

End Sub

Bill R via AccessMonster.com said:
The file I want is:

"11_29_05_1030AM_DMS.csv"

The file I don't want is:

"11_29_05_1030AM_DMS_Meth_Full.csv"

Every hour new files are added to the directory. It might be preferable to
use the latest modified dates rather than the filenames. What say you?

I just posted a new thread, "Code doesn't find newly imported data in a
table", that's related to this module as well. Any suggestions there would be
deeply appreciated. After importing the CSV contents, the data doesn't seem
to be readable in subsequent code.

Thanks,

"Gort"

"Barada nikto"
This is doable. To give you a complete answer, I need to know what the
naming convention of the .csv files is and how you know which is the one you
want. In the mean time, go into VB Help, select the Object Browser, and read
up on the FileSearch object.
Every hour, 2 CSV files, with a naming convention reflecting the date and
time of their creation, are added to a directory on a server. I can easily
Click to expand...
[quoted text clipped - 12 lines]
Bill
Click to expand...
Click to expand...
Click to expand...
 
Works like a charm! Thanks a million. Western civilization is saved!
I think your idea of looking for the most recent is good provided you are
confident you will alway get the correct file. So, here is how to use the
FileSearh object to do what you want:

Sub GetLatestFile
Dim fs as Object
Dim lngFileCount as Long
Dim dtmHighTime As Date
Dim strHighName As Sting
Dim strCurrFile As String
Dim strPath as String
Dim strOldFile as String

'Set up the search
set fs = application.FileSearch
With fs
.newsearch
.lookin = "C:\PathToFilesYouWant"
.filename = "*.csv"
.searchsubfolders = false
.execute
'Look for the Newest file
For lngFileCount = 1 to .foundfiles.count
strCurrFile = .foundfiles(lngFileCount)
If Instr(strCurrFile, "Meth_Full" = 0) Then ' We Dont Want this
one
If FileDateTime(strCurrFile) > dtmHighTime Then ' It is Newer
dtmHighTime = FileDateTime(strCurrFile)
strHighName = strCurrFile 'Keep track of the most
recent file
End If
End If
End With

strPath = left(strHighName,instrrev(strHighName,"\")) 'Get the path
strOldFile = strPath & "DMS_Report.CSV" 'Add the file name
Kill strOldFile 'Delete the old file
FileCopy strHighName, strOldFile Copy the new file

End Sub
The file I want is:
Click to expand...
[quoted text clipped - 28 lines]
Click to expand...
 
Wow! and without even being able to test it. I'm sure you had to fix at
least some syntax errors.

Western civilization? Does that require boots, a cowboy hat, and a large
belt buckle?

Bill R via AccessMonster.com said:
Works like a charm! Thanks a million. Western civilization is saved!
I think your idea of looking for the most recent is good provided you are
confident you will alway get the correct file. So, here is how to use the
FileSearh object to do what you want:

Sub GetLatestFile
Dim fs as Object
Dim lngFileCount as Long
Dim dtmHighTime As Date
Dim strHighName As Sting
Dim strCurrFile As String
Dim strPath as String
Dim strOldFile as String

'Set up the search
set fs = application.FileSearch
With fs
.newsearch
.lookin = "C:\PathToFilesYouWant"
.filename = "*.csv"
.searchsubfolders = false
.execute
'Look for the Newest file
For lngFileCount = 1 to .foundfiles.count
strCurrFile = .foundfiles(lngFileCount)
If Instr(strCurrFile, "Meth_Full" = 0) Then ' We Dont Want this
one
If FileDateTime(strCurrFile) > dtmHighTime Then ' It is Newer
dtmHighTime = FileDateTime(strCurrFile)
strHighName = strCurrFile 'Keep track of the most
recent file
End If
End If
End With

strPath = left(strHighName,instrrev(strHighName,"\")) 'Get the path
strOldFile = strPath & "DMS_Report.CSV" 'Add the file name
Kill strOldFile 'Delete the old file
FileCopy strHighName, strOldFile Copy the new file

End Sub
The file I want is:
Click to expand...
[quoted text clipped - 28 lines]
Bill
Click to expand...
Click to expand...
Click to expand...
 
Not required, but certainly helpful! ;-)
Wow! and without even being able to test it. I'm sure you had to fix at
least some syntax errors.

Western civilization? Does that require boots, a cowboy hat, and a large
belt buckle?
Works like a charm! Thanks a million. Western civilization is saved!
Click to expand...
[quoted text clipped - 44 lines]
Click to expand...
 
Back
Top