Import new .txt file only.

[turks67 via AccessMonster.com]

I have to import text files everyday from a folder that has multiple text
files from previous days of the year into a table. How can i only import the
new files without importing all the text files everytime. I using the

DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile


Thanks

 

[Dirk Goldgar]

I have to import text files everyday from a folder that has multiple text
files from previous days of the year into a table. How can i only import
the
new files without importing all the text files everytime. I using the

DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile

How do you know which files are new? Maybe the file names are dated, such
that you can tell which are today's files, but what if you miss a day?

It seems to me that you can either (a) record in a database table which
files you have imported, or (b) after successfully importing a file, move it
to an archive folder so it isn't present in the original folder to be
imported again.

 

[turks67 via AccessMonster.com]

I have a table with files I have already imported. The problem is that the
same file get imported with the new files. how can I move the old files only?

 

[Piet Linden]

I have a table with files I have already imported. The problem is that the
same file get imported with the new files. how can I move the old files only?

how about moving the file immediately after you import it? Check out
the Name function in VBA.

 

[Dirk Goldgar]

I have a table with files I have already imported. The problem is that the
same file get imported with the new files. how can I move the old files
only?

As you go through the list of files in the folder, look each one up in the
table to see if it has already been imported, and only import it if it
hasn't. Here's a very rough example of the sort of logic I mean:

'------ start of example code ------
Dim strFolderPath As String
Dim strFileName As String
Dim db As DAO.Database

Set db = CurrentDb

strFolder = "C:\Your Path\To\Your Folder\"

' Get first .txt file in folder.
strFileName = Dir(strFolder & "*.txt")

' Loop to process all .txt files.
Do Until Len(strFileName) = 0

' Have we already imported this one?
If IsNull(DLookup("FileName", "ImportedFiles", _
"FileName=" & Chr(34) & strFileName & Chr(34))) _
Then
' We haven't already imported it, so import this file.

strPathFile = strFolderPath & strFIleName

DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile

' Record this import in the ImportedFiles table.
db.Execute _
"INSERT INTO ImportedFiles (FileName, ImportDate) " & _
"VALUES(" & Chr(34) & strFileName & Chr(34) & _
", " & Format(Date(), "\#mm\/dd\/yyyy\#") & ")",
_
dbFailOnError

End If

' Get next .txt file.
strFileName = Dir()

Loop

Set db = Nothing
'------ end of example code ------

 

[turks67 via AccessMonster.com]

Thank you. Will give it a try.

As you go through the list of files in the folder, look each one up in the
table to see if it has already been imported, and only import it if it
hasn't. Here's a very rough example of the sort of logic I mean:

'------ start of example code ------
Dim strFolderPath As String
Dim strFileName As String
Dim db As DAO.Database

Set db = CurrentDb

strFolder = "C:\Your Path\To\Your Folder\"

' Get first .txt file in folder.
strFileName = Dir(strFolder & "*.txt")

' Loop to process all .txt files.
Do Until Len(strFileName) = 0

' Have we already imported this one?
If IsNull(DLookup("FileName", "ImportedFiles", _
"FileName=" & Chr(34) & strFileName & Chr(34))) _
Then
' We haven't already imported it, so import this file.

strPathFile = strFolderPath & strFIleName

DoCmd.TransferText acImportDelim, strSpec, strTable, strPathFile

' Record this import in the ImportedFiles table.
db.Execute _
"INSERT INTO ImportedFiles (FileName, ImportDate) " & _
"VALUES(" & Chr(34) & strFileName & Chr(34) & _
", " & Format(Date(), "\#mm\/dd\/yyyy\#") & ")",
_
dbFailOnError

End If

' Get next .txt file.
strFileName = Dir()

Loop

Set db = Nothing
'------ end of example code ------