Solved How to open a set of CSV files created on a certain date span? (1 Viewer)

NahualXl

New member
Local time
Today, 01:47
Joined
Sep 23, 2021
Messages
19
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


Code:
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
 
Last edited:

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 22:47
Joined
Oct 29, 2018
Messages
17,177
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

New member
Local time
Today, 01:47
Joined
Sep 23, 2021
Messages
19
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

Lifelong Learner
Local time
Yesterday, 22:47
Joined
Mar 14, 2017
Messages
5,924
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.
 

NahualXl

New member
Local time
Today, 01:47
Joined
Sep 23, 2021
Messages
19
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

AWF VIP
Local time
Today, 01:47
Joined
Jan 11, 2013
Messages
2,603
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.
 

Attachments

  • fsoCreatedDate.accdb
    636 KB · Views: 38

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:47
Joined
May 7, 2009
Messages
14,648
from your code:
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
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:47
Joined
Feb 19, 2002
Messages
33,777
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

New member
Local time
Today, 01:47
Joined
Sep 23, 2021
Messages
19
from your code:
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
 

Attachments

  • DateTimePicker.accdb
    1.3 MB · Views: 99
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 01:47
Joined
Feb 19, 2002
Messages
33,777
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.
 
Last edited:

NahualXl

New member
Local time
Today, 01:47
Joined
Sep 23, 2021
Messages
19
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

AWF VIP
Local time
Today, 01:47
Joined
Jan 11, 2013
Messages
2,603
Are there files created within the date range in the folder?
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:47
Joined
Sep 21, 2011
Messages
9,319
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

Super Moderator
Staff member
Local time
Today, 01:47
Joined
Feb 19, 2002
Messages
33,777
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

AWF VIP
Local time
Today, 01:47
Joined
Jan 11, 2013
Messages
2,603
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

Enthusiastic Amateur
Local time
Today, 06:47
Joined
Sep 21, 2011
Messages
9,319
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

AWF VIP
Local time
Today, 01:47
Joined
Jan 11, 2013
Messages
2,603
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

Enthusiastic Amateur
Local time
Today, 06:47
Joined
Sep 21, 2011
Messages
9,319
Well if you are curious, I will log the issues and report back? The calendar form showed all 42 buttons as 1 to 42?
 

Users who are viewing this thread

Top Bottom