Export - Files

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello:

I am planning to import tab delimited files as they are created in the
specific folder every day by the external program.

I will use Task Scheduler to run Import Procedure. I am not sure thought
how to provide filename to import specification procedure as it will change
every day. The filenames will be as follow

C:\datafiles
200511150000.tab (this is for Nov 15, 05)
200511140000.tab (this is for Nov 14, 05)
200511130000.tab (this is for Nov 14, 05)
200511120000.tab, etc.

For example, on November 15 after midnight I will need to read a file from
the previous day, that is from November 14, 05 (I need full 24 hours data),
and so on.
How do I tell the program to read specific filename?

I would appreciate some ideas or suggestions.

Thanks
Danka
 
One approach would be to build the filename in code, as in this snippet

Dim strFolder As String
Dim dtDate As Date
Dim strFileName As String

strFolder = "C:\datafiles\"
dtDate = DateAdd("d", -1, Date)
strFileName = CStr(Year(dtDate)) & CStr(Month(dtDate)) _
& CStr(Day(dtDate)) & "0000.tab"

Then use Dir() to check that the file exists before importing it:

If Len(Dir(strFolder & strFilename)) = 0 Then
'File not found
'Log error, alert operator or whatever
...
Else
'Import file
DoCmd.TransferText blah blah
End If

An alternative approach would be to have the code import all files in
the folder whose names match the pattern, deleting, moving or renaming
each file after importing it. That way, if the procedure didn't run one
time, the missed file would be imported next time. Something like

strFileName = Dir(strFolder & "*.tab")
Do While Len(strFileName) > 0
If strFileName Like "############.tab" Then
'Import File
...
'If import successful, delete file
...
End If
Looop
 
Hello John:

1. Thanks for the solutions. First approach works perfectly and imports a
file dated one day bofore today's date in this statement:

DoCmd.TransferText acImportDelim, "zupaspec", "zupatable", strFolder &
strFileName

So this works fine.

2. I agree that I need to make sure that if the import fails for any reason
on any specific date it needs to be imported next time around and that I will
have no duplicate entries. I probably could build a table with filenames
that are imported and check the source directory names against my table names
and import only the one that are not there. I am not sure how to do it yet.

Possbly, your soluton is better but I don't quite understand the logic and
what these statements would do?

What do I match in this statement?

If strFileName Like "############.tab" Then
'Import File
...
Am I supposed to delete files from the folder I import from? I don't want
to make any changes in the source directory.
Or I need to delete access tables ?

Can you explain? Thank you very much.

Danka

End If
Loop
 
Hello John:

1. Thanks for the solutions. First approach works perfectly and imports a
file dated one day bofore today's date in this statement:

DoCmd.TransferText acImportDelim, "zupaspec", "zupatable", strFolder &
strFileName

So this works fine.

2. I agree that I need to make sure that if the import fails for any reason
on any specific date it needs to be imported next time around and that I will
have no duplicate entries. I probably could build a table with filenames
that are imported and check the source directory names against my table names
and import only the one that are not there. I am not sure how to do it yet.

Possbly, your soluton is better but I don't quite understand the logic and
what these statements would do?

What do I match in this statement?

If strFileName Like "############.tab" Then
Click to expand...

Check Help for the Like operator. Each # matches one digit.
'Import File
...
Am I supposed to delete files from the folder I import from? I don't want
to make any changes in the source directory.
Or I need to delete access tables ?
Click to expand...

The idea was to have the routine iterate through all the files in the
folder that that match the pattern (e.g. "200511170000.tab"). For each
file it would (1) attempt to import the data and (2) if the import was
successful either delete the file, rename it or move it to another
folder (e.g. an "imported files" folder) so it won't be imported a
second time.

If it's not acceptable to make any changes in the source folder, the
daily files will just accumulate there and the simplest way to keep
track of which files have been imported is as you suggest to set up a
table
tblFilesImported
FileName (text, primary key)
DateImported (date/time)

In that case, after importing each file you'd append the corresponding
record to tblFilesImported, using something like this:

Dim strSQL As String
strSQL = "INSERT INTO tblFilesImported (FileName, DateImported) " _
& "VALUES ('" & strFileName & "', #" & Now() & "#);"
CurrentDB.Execute strSQL, dbFaileOnError

And while importing you'd need to test each filename retrieved by Dir()
against the file, like this:

If DCount("FileName", "tblFilesImported", _
"FileName = '" & strFileName & "'") > 0 Then
'File has already been imported
'Do nothing
Else
'import strFileName
....
End If
 
Hello John:

Thanks for your being patient with me. I will read more about Dir function.
In the meantime I tried this:

Private Sub Command13_Click()

Dim strFolder As String
Dim strFileName As String
Dim strSQL As String

strFolder = "C:\data\"
strFileName = Dir(strFolder & "*.tab")

Do While ??? ' not sure how to iterate and condition
strFileName = Dir ' seems to iterate to next filename

strSQL = "INSERT INTO tblFilesImported (FileName, DateImported) " _
& "VALUES ('" & strFileName & "', #" & Now() & "#);"
CurrentDb.Execute strSQL, dbFaileOnError ' should insert 2 fields

If DCount("FileName", "tblFilesImported", _
"FileName = '" & strFileName & "'") > 0 Then
'File has already been imported
'Do nothing - exit sub
Else
DoCmd.TransferText acImportDelim, "zupa", "zupa", strFolder &
strFileNameimport strFileName

End If
Loop
End Sub

I am not sure how to iterate and what is a condition for DoWhileI believe, I
should be checking a count of filenames against filename read from the folder
(<1) but I am not sure about iteration and condition. CAn you help?
 
Dim strFolder As String
Dim strFileName As String
Dim strSQL As String

strFolder = "C:\data\"
strFileName = Dir(strFolder & "*.tab")

Do While Len(strFileName) > 0

If DCount("FileName", "tblFilesImported", _
"FileName = '" & strFileName & "'") > 0 Then
'File has already been imported
'Do nothing - exit sub
Else
DoCmd.TransferText acImportDelim, "zupa", "zupa", _
strFolder & strFileName
' Don't update the table until you've imported the file...
strSQL = "INSERT INTO tblFilesImported (FileName, DateImported) " _
& "VALUES ('" & strFileName & "', #" & Now() & "#);"
CurrentDb.Execute strSQL, dbFaileOnError ' should insert 2 fields
End If
strFileName = Dir
Loop

End Sub
 
Hello John:

Thanks for all your help - your solution worked perfectly. If any file
import is skipped, then the next time around an entry is inserted into
control table (tblFileImported) and records are appended to import file. So,
I am home now and can process records further....

Thanks again.
 
All works well - thanks.
--
danka


Douglas J. Steele said:
Dim strFolder As String
Dim strFileName As String
Dim strSQL As String

strFolder = "C:\data\"
strFileName = Dir(strFolder & "*.tab")

Do While Len(strFileName) > 0

If DCount("FileName", "tblFilesImported", _
"FileName = '" & strFileName & "'") > 0 Then
'File has already been imported
'Do nothing - exit sub
Else
DoCmd.TransferText acImportDelim, "zupa", "zupa", _
strFolder & strFileName
' Don't update the table until you've imported the file...
strSQL = "INSERT INTO tblFilesImported (FileName, DateImported) " _
& "VALUES ('" & strFileName & "', #" & Now() & "#);"
CurrentDb.Execute strSQL, dbFaileOnError ' should insert 2 fields
End If
strFileName = Dir
Loop

End Sub
Click to expand...
 
I'm glad it's working.

Hello John:

Thanks for all your help - your solution worked perfectly. If any file
import is skipped, then the next time around an entry is inserted into
control table (tblFileImported) and records are appended to import file. So,
I am home now and can process records further....

Thanks again.
Click to expand...
 
Back
Top