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

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
Are there files created within the date range in the folder?
Yes see attachments please. This is what I see on the DB when I press submit nothing happens
 

Attachments

  • db.PNG
    db.PNG
    20.6 KB · Views: 139
  • files1.PNG
    files1.PNG
    41 KB · Views: 138

Gasman

Enthusiastic Amateur
Local time
Today, 10:37
Joined
Sep 21, 2011
Messages
14,038
That is a very narrow range? Try something bigger as it definitely works.
Also how can you have a Date Created after a Date Modified?

1632510074162.png
 

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
That is a very narrow range? Try something bigger as it definitely works.
Also how can you have a Date Created after a Date Modified?

View attachment 94789
LoL you have noticed that as well :) I have never seen that before! that is why I said date created is the best way to go about it. I picked the range of 9/15-9/24 and see the attachment for the results. it only displays the 9/16/2021 date files and nothing else. I do have a lot of files created between these dates thou
 

Isaac

Lifelong Learner
Local time
Today, 03:37
Joined
Mar 14, 2017
Messages
8,738
I have never seen that before!

Definitely happens. I just downloaded a file from one of my client's FTP sites. Due to the way my FTP client happens to handle this task, the date created shows the moment I downloaded it. But the date modified shows 15 minutes earlier.

Can be common in file transfer situations, depending on how the transfer client interacts with Windows.
 

moke123

AWF VIP
Local time
Today, 06:37
Joined
Jan 11, 2013
Messages
3,849
try commenting out the "If" and the "end if" lines and see if it lists all the files.

Code:
Set fso = New FileSystemObject

    If Len(Dir(strFolder, vbDirectory)) = 0 Then Exit Function

    Set fol = fso.GetFolder(strFolder)

    For Each fil In fol.Files

        'If fil.DateCreated >= dteStart And fil.DateCreated <= dteEnd Then

            lbx.AddItem Format(fil.DateCreated, "Short Date") & ";" & fil.Path
          
        'End If

    Next
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Feb 19, 2002
Messages
42,970
@NahualXl
This is your application and you can do whatever you want but I'm telling you from experience, that at some point your users will either miss files or double import them if you persist in using this technique. It is an accident waiting to happen.
 

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
Definitely happens. I just downloaded a file from one of my client's FTP sites. Due to the way my FTP client happens to handle this task, the date created shows the moment I downloaded it. But the date modified shows 15 minutes earlier.

Can be common in file transfer situations, depending on how the transfer client interacts with Windows.
just looked at the original folder and it shows the same issue with the dates haha
 

Attachments

  • files1.PNG
    files1.PNG
    18 KB · Views: 132

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
try commenting out the "If" and the "end if" lines and see if it lists all the files.

Code:
Set fso = New FileSystemObject

    If Len(Dir(strFolder, vbDirectory)) = 0 Then Exit Function

    Set fol = fso.GetFolder(strFolder)

    For Each fil In fol.Files

        'If fil.DateCreated >= dteStart And fil.DateCreated <= dteEnd Then

            lbx.AddItem Format(fil.DateCreated, "Short Date") & ";" & fil.Path
         
        'End If

    Next
tried it & there are 53 files created on 9/24/2021 and it shows only 2. & 300+ on 9/16/2021.. it does not look like it is loading all the 9/16/2021 either
 

Attachments

  • files1.PNG
    files1.PNG
    115.5 KB · Views: 135

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
@NahualXl
This is your application and you can do whatever you want but I'm telling you from experience, that at some point your users will either miss files or double import them if you persist in using this technique. It is an accident waiting to happen.
I understand your point; however, I have not clue as how to code the approach you are suggesting
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 06:37
Joined
Feb 19, 2002
Messages
42,970
I explained it earlier but you weren't interested. we are now 30 posts into this thread so maybe you want to figure out what is wrong with what you have. Then you can decide if you want to dump all the current procedure and start again. Meanwhile, I'll see if I have an example.
 

moke123

AWF VIP
Local time
Today, 06:37
Joined
Jan 11, 2013
Messages
3,849
I understand your point; however, I have not clue as how to code the approach you are suggesting
Here's another example which has some of Pat's ideas in it, although a little less robust.
It adds a field to the record listing the file the data is from and the date and time of the import.
It moves the file to the archive folder after copying it. ( you can click the reset button to move them back just for the demo purposes)
I also defaulted the dates and folder path just to make it easy. Just click the submit button.
Also included a few example excel files.
 

Attachments

  • fsoCreatedDate.zip
    114.7 KB · Views: 159
Last edited:

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
Hello, Updated OP with updated code, just need some minor tweaks... appreciate everyone's time and assistance
 

moke123

AWF VIP
Local time
Today, 06:37
Joined
Jan 11, 2013
Messages
3,849
On my way out to work, I'll take a look when I get there.
Meanwhile here's another demo with a seperate log file which records the range of primary keys for each file imported.
I made it the other day but never got a chance to post it.
 

Attachments

  • fsoCreateDateV2.zip
    210.4 KB · Views: 124

moke123

AWF VIP
Local time
Today, 06:37
Joined
Jan 11, 2013
Messages
3,849
Code:
If strFile.DateCreated > (txt_Start_Date - 1) And strFile.DateCreated < (txt_Start_Date2 + 1) Then
What tweaks?

if dates are not working it could be you're comparing a date to a string.
You'll also need fso to get the date created property.
 
Last edited:

NahualXl

New member
Local time
Today, 06:37
Joined
Sep 23, 2021
Messages
19
Code:
If strFile.DateCreated > (txt_Start_Date - 1) And strFile.DateCreated < (txt_Start_Date2 + 1) Then
What tweaks?

if dates are not working it could be you're comparing a date to a string.
You'll also need fso to get the date created property.
"error" invalid qualifier here -> strFile.DateCreated
 

Users who are viewing this thread

Top Bottom