Importing multiple csv based on Modified Date (1 Viewer)

[jfgambit]

I have several .csv files in a common directory that are sporadically updated. Utilizing a Start Date prompt and an End Date prompt, I want to import the files in the directory that coincide to those prompted dates.

Start date: 7/12/2010
End date: 7/14/2010

Files in directory:
EXP_TRANSFER_20100708 (Modified Date: 7/12/2010 8:45:00) <Will get imported
EXP_TRANSFER_20100709 (Modified date: 7/13/2010 14:56:45) <Will get imported
EXP_TRANSFER_20100710 (Modified date: 7/10/2010 8:45:00) <Will not get imported
EXP_TRANSFER_20100714 (Modified date: 7/14/2010 23:59:59)<Will get imported.

I’ve searched previous posts, but can’t seem to find anything helpful.

Thanks in advance.

 

[DCrake]

Re: Importing multiple cvs based on Modified Date

You can get the Date Last Modified from the FileSystemObject of a file and use that as the comparison.

 

[jfgambit]

Re: Importing multiple cvs based on Modified Date

DC:

I tried but it doesn't import any files. If I swap out the "<" and ">" under If myFTime < stDate > edDate Then it imports everything except the files I want...any ideas?? Could it be because there is no time on the Start and End dates??

Dim myFName As String
Dim myDName As String
Dim myFTime As String
Dim LatestTime As Date
Dim LatestFile As String
Dim stDate, edDate As Date
'Set directory to search
myDName = "\\EXT\EXT830\"
'Prompt for the START and END import dates
stDate = InputBox("What is the START DATE for Visteon EDI 830 import?", "START DATE")
edDate = InputBox("What is the END DATE for Visteon EDI 830 import?", "END DATE")
myFName = Dir(myDName6, vbNormal)
'Loop through directory
Do While myFName <> ""
myFTime = FileDateTime(myDName & myFName)
'Check current file - if it is between dates then import file
If myFTime < stDate > edDate Then
LatestTime = myFTime
LatestFile = myFName
DoCmd.TransferText acImportDelim, "EXTImportSpec", "tempEXT", myDName & LatestFile6, True
Else
'Do nothing
End If
myFName = Dir
Loop

 

[jfgambit]

In case anyone wants the code....this will extract out the date from a file name (ex: EXT_EXTIMPORT_20100708.txt) from multiple files and import them into your table for a date range....

HTH...

Dim stDate, edDate As Date
Dim myConvtName As Date

'Prompt for the START and END import dates
stDate = InputBox("What is the START DATE for EXT import?", "START DATE")
edDate = InputBox("What is the END DATE for EXT import?", "END DATE")

myDName = "\\<Your Path>\"

myFName = Dir(myDName, vbNormal)

'Loop through directory
Do While myFName <> ""

'Check current file - if it is between stDate and edDate import file
LatestFile7 = myFName7

myConvtName = Mid(LatestFile, 25, 2) & "/" & Mid(LatestFile, 27, 2) & "/" & Mid(LatestFile, 23, 2)
If myConvtName <= edDate Then
    If myConvtName >= stDate Then
        DoCmd.TransferText acImportDelim, "EXTImportSpec", "tempEXT", myDName & LatestFile, True
    Else
    'Do nothing
    End If
Else
'Do nothing
End If

myFName = Dir
Loop

 

[varc1965]

Hi jfgambit,
I'm not a expert vba programmer and I'm trying to put your last code on my db in order to import in one table multiple files in the directory that coincide to those prompted dates. but code stop on red row (run time error 13 type mismatch).

Please could you help me
Thanking in advance
------------------------------------------------------
Private Sub Import_Click()

Dim stDate, edDate As Date
Dim myConvtName As Date

'Prompt for the START and END import dates
stDate = InputBox("What is the START DATE for EXT import?", "START DATE")
edDate = InputBox("What is the END DATE for EXT import?", "END DATE")

myDName = "C:\Users\ti20935\Desktop\MPS\MPS761\"

myFName = Dir(myDName, vbNormal)

'Loop through directory
Do While myFName <> ""

'Check current file - if it is between stDate and edDate import file
LatestFile7 = myFName7


'Check current file - if it is between stDate and edDate import file
myConvtName = Mid(LatestFile, 25, 2) & "/" & Mid(LatestFile, 27, 2) & "/" & Mid(LatestFile, 23, 2)
If myConvtName <= edDate Then
If myConvtName >= stDate Then
DoCmd.TransferText acImportDelim, "Import-MPS", "Dati_MPS_Foerster", myDName & LatestFile, True
Else
'Do nothing
End If
Else
'Do nothing
End If

myFName = Dir
Loop

MsgBox "Data has been imported into the Dati_MPS_Foerster"

End Sub
---------------------------------------------------------------------------