Import txt files based on date

[umair434]

Hi,

Here is the situation:


I want to import 3 recent txt files (based on date and time) to an access table. Each txt file is to be imported to it's table.

These txt files are located in a directory which is populated everyday with new txt files, hence the txt files keep changing.

A file is named as "Locations20120315.TXT" where 20120315 represents March 15, 2012.


How can I achieve this by click on a button?


Really appreciated any help I can get. Thanks!

 

[jdraw]

You said 3 files. What are the names of the other 2?
How will you know the files to move tomorrow?
Do you determine the filename based on a function using Date?

 

[umair434]

Thanks for the reply jdraw!

the thing is - these files are thrown in this directory by a host system. The name of the files are similar but with the date changed.


so today's file name is Locations20120315.TXT
yesterdays = Locations20120314.TXT

and so on.


Now on Mondays, I want to be able to import the files for friday, saturday and sunday! This is to be done every monday. Since the files names will be changing (their date portion), I was hoping if this can be automated.


Does it make some sense?

thanks again!

 

[jdraw]

I think t could be done, but what exactly does this really mean
Each txt file is to be imported to it's table.

You bring the text file into separate tables...? It isn't clear what the overall process and intent are (to me anyway).

 

[pi6]

Here is what I have for simular

Private Sub Import_Click()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim FileDate As Date
Dim ConvDate As Date
Dim MyPass As String
Dim FileName As String
Dim Found As Integer
Const NAME_NOT_IN_COLLECTION = 3265
Found = True

On Error Resume Next
Set db = CurrentDb
MyPass = "D:\AAA\BBB\Folder\"
FileName = Dir(MyPass & "*.csv", vbNormal)
While Len(FileName) > 0
FileDate = FileDateTime(MyPass & FileName)
ConvDate = Format(FileDate, "mm/dd/yyyy")
If ConvDate = Me.txtDate.Value Then
DoCmd.TransferText acImportDelim, "ImportFile", "tablename", MyPass & FileName
End If
FileName = Dir
Wend
Dim tb As String
tb = db.TableDefs("tablename").Name
If Err = NAME_NOT_IN_COLLECTION Then
Found = False
MsgBox "File wasn't imported. Please verify if file exists."

' Reset the error variable.
Err = 0
Exit Sub

txtDate is a test box on the form. You also need save Import specification first.

 

[umair434]

Jdraw: Yes sir! the txt files are to be imported into their seperate table - which gets overwrriten every week.

pi6 - thanks for the code. i'll play with it and keep you posted.


Thanks guys!

 

[umair434]

just a question:

filedatetime shows the time a file was created or last modified. Since a file might be modified after it's creation date, is there a way to compare txtDate with just the creation date?

 

[pi6]

You may rename table in the database and reimport next file. Just remember if there are two files per day, it will combine them together.

 

[umair434]

There is 1 file per day. but is there a way to find the creation date only?

I have been googling this since last night - i know access must have another way to solve this :S

any clues?

Thanks guys!