Solved How to open a set of CSV files created on a certain date span?

[NahualXl]

Hello,
I have tons of CSV logs created daily, need to be able to specify a date range i.e. "9/20/2021 - 9/23/2021" and open all the files created during this period.
Import them into an access 2016 table
This is the code I have pieced together from the web; however, I am unable to get it to work as it hangs up on the file open & import portion of it.
NOTE: I am looking for a method to accomplish this, so if you have a better way please do share
Thank you so much for your time

UPDATE: Was "given" this code and it imports all the CSV files into a single table; however, got hanged up on the datecatreated check to force the import based on the two dates only

Dim strPathFile As String, strFile As String, strPath As String
        Dim strTable As String, strBrowseMsg As String
        Dim blnHasFieldNames As Boolean

     
        blnHasFieldNames = True

        strBrowseMsg = "Select the folder that contains the CSV files:"

        strPath = "E:\OP60\"

        If strPath = "" Then
              MsgBox "No folder was selected.", vbOK, "No Selection"
              Exit Sub
        End If

        strTable = "tableData"
'*********************
Me.txt_Start_Date = "9/16/21"
Me.txt_Start_Date2 = "9/17/21"
'*********************
        strFile = Dir(strPath & "\*.CSV")
        Do While Len(strFile) > 0
              strPathFile = strPath & "\" & strFile

'************************ERRORS HERE DO NOT KNOW HOW TO CHECK FOR DATECREATED :(
  If strFile.DateCreated > (txt_Start_Date - 1) And strFile.DateCreated < (txt_Start_Date2 + 1) Then
 '***********************
        DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames

                  strFile = Dir()
        Loop

PS: apologies this is cross posted as well at vbaexpress.com

 

[theDBguy]

Hi. Welcome to AWF!

Are we talking about the csv files are named with the dates they were created, or are you talking about pulling the dates from the file properties?

 

[NahualXl]

Hi. Welcome to AWF!

Are we talking about the csv files are named with the dates they were created, or are you talking about pulling the dates from the file properties?

Hello much appreciated...
The files do not have a date on their name; what I need is to be able to open ALL the CSV files created between the 2 selected/specified dates. hope this explains things a bit sorry for the confusion

 

[Isaac]

I would declare fso and fsofile and fsofolder, all as objects. set fso to the scripting filesystemobject, fsofolder to fso.getfolder("path to folder"), and then loop through For Each fsofile in fsofolder.files.....do your import...next

inside the loop, have an if statement that tests the fsofile.datecreated

that's the approach i'd use, rather than Dir.

 

[Gasman]

Crossposted http://www.vbaexpress.com/forum/sho...t-of-CSV-files-created-on-a-certain-date-span

 

[NahualXl]

I would declare fso and fsofile and fsofolder, all as objects. set fso to the scripting filesystemobject, fsofolder to fso.getfolder("path to folder"), and then loop through For Each fsofile in fsofolder.files.....do your import...next

inside the loop, have an if statement that tests the fsofile.datecreated

that's the approach i'd use, rather than Dir.

I had updated the code on the OP, I get no errors, but it is not doing a thing, no imports etc.. thank you so much for your help

 

[moke123]

Here's an example showing how to loop through the folder and get the date range.
It includes recursion but you can comment that out if not needed.

There is also a procedure to modify (change the file path) of a saved import.
Use a saved import instead of transfertext and use the procedure to change the path of the file.

 

[arnelgp]

from your code:

Dim fsofile As Object
Dim fso As Object
Dim fsofolder As Object


Dim Directory As String
Dim FileName As String


Directory = "E:\Op60\"


Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsofolder = fso.GetFolder(Directory)


If Right(Directory, 1) <> "\" Then
   Directory = Directory & "\"
End If


FileName = Dir(Directory & "*.CSV")


Dim DateEarliest As Date
Dim EarliestDate As Date


EarliestDate = txt_Start_Date
DateEarliest = CDate(EarliestDate)


Dim DateLatest As Date
Dim LatestDate As Date


LatestDate = txt_Start_Date2
DateLatest = CDate(LatestDate)


For Each fsofile In fsofolder.files


If DateValue(fsofile.DateLastModified) >= DateEarliest And DateValue(fsofile.DateLastModified) <= DateLatest Then
    DoCmd.TransferText TransferType:=acLinkDelim, TableName:="CsvTbl", FileName:=Directory & FileName, HasFieldNames:=True
'Debug.Print DateValue(fsofile.DateLastModified)
End If
Next

 

[Pat Hartman]

I'm sure that one of the suggested solutions will work but what I do to simplify the import task is to move the files to an archive folder after I import them. That way, the folder only ever contains the files I want ti import and you don't run the risk of missing one because you messed up your date range or of importing the same file multiple times.

The second thing I do is to create a log of all the files that were imported. As I pick a file, I save it's name in a log record and capture the generated autonumber. There is a unique index on the file name to prevent saving the same name multiple times. Then as part of the append, I also include the LogID in the data that gets appended. That way every row is tied to the file name.

Should something still go wrong, the errant file's data can be identified and the row deleted from the log table. Cascade delete handles deleting the related details.

 

[NahualXl]

from your code:

Dim fsofile As Object
Dim fso As Object
Dim fsofolder As Object


Dim Directory As String
Dim FileName As String


Directory = "E:\Op60\"


Set fso = CreateObject("Scripting.FileSystemObject")
    Set fsofolder = fso.GetFolder(Directory)


If Right(Directory, 1) <> "\" Then
   Directory = Directory & "\"
End If


FileName = Dir(Directory & "*.CSV")


Dim DateEarliest As Date
Dim EarliestDate As Date


EarliestDate = txt_Start_Date
DateEarliest = CDate(EarliestDate)


Dim DateLatest As Date
Dim LatestDate As Date


LatestDate = txt_Start_Date2
DateLatest = CDate(LatestDate)


For Each fsofile In fsofolder.files


If DateValue(fsofile.DateLastModified) >= DateEarliest And DateValue(fsofile.DateLastModified) <= DateLatest Then
    DoCmd.TransferText TransferType:=acLinkDelim, TableName:="CsvTbl", FileName:=Directory & FileName, HasFieldNames:=True
'Debug.Print DateValue(fsofile.DateLastModified)
End If
Next

GREAT! Thank you so much... question is there a way to "merge" all the tables" in to a single table?
**Also it opens only for me all files created on 9/7/2021 (it does not obey the time pickers that is).
**Tried changing fsofile.DateLastModified but the code did not take, I think it will be more optimal to get the files by creation date
so I am attaching my test DB to ensure I am not doing something wrong elsewhere

 

[Pat Hartman]

I guess I should have examined the suggested code in more detail. You link each new table and then append it to the permanent table. You always link as the same table name so that the append query doesn't change and you don't end up with a separate linked tble for each one imported.

The reason for the link/append is so that you can clean up data as you import it and so you can include the LogID field so you can identify the source of the data.

 

[NahualXl]

Here's an example showing how to loop through the folder and get the date range.
It includes recursion but you can comment that out if not needed.

There is also a procedure to modify (change the file path) of a saved import.
Use a saved import instead of transfertext and use the procedure to change the path of the file.

appreciate the assistance, when I run this DB nothing happens... select folder, date range, hit submit and don't see anything... and I missing something?

 

[moke123]

Are there files created within the date range in the folder?

 

[Gasman]

It worked for me, but with errors, probably due to my old version?
Select date range of this year, and see what you get?

 

[Pat Hartman]

As I mentioned already, doing this by date range exposes you to the problem of missing a file if you mess up the date range.

 

[moke123]

It worked for me, but with errors, probably due to my old version?
Select date range of this year, and see what you get?

what error did you get?
wonder if its a us/uk date kind of thing.

 

[Gasman]

Errors on the calendar form, and something about object not having a property?
Do not worry about it, as I only have 2007 and later features always raise errors. I entered the dates manually and files were retrieved.

 

[moke123]

Errors on the calendar form, and something about object not having a property?
Do not worry about it, as I only have 2007 and later features always raise errors. I entered the dates manually and files were retrieved.

Weird, I use 2010 so versions shouldn't be a problem.
The date picker is a custom class I use all the time without ever throwing an error.

 

[Gasman]

Well if you are curious, I will log the issues and report back? The calendar form showed all 42 buttons as 1 to 42?

 

[moke123]

Well if you are curious, I will log the issues and report back? The calendar form showed all 42 buttons as 1 to 42?

Sent you a pm so we dont hijack OP's thread.