auto linking to files that change names

[Guest]

I am setting up an Access application to automatically read and parse .csv
files that are generated via script on a remote server.

The catch is that the .csv file name changes everyday according to the date.
For example, the files look like:

"QOR_DAILY_04152005.CSV"
"QOR_DAILY_04162005.CSV"
"QOR_DAILY_04172005.CSV"
....and so forth

The data format contained in the .csv file is the same everyday, and because
it is generated by automated script everyday, I felt that to fully automate
the entire process would save me a lot of pain down the road.

The 2 approaches that crossed my mind were:

1) Have the Access application copy the file to a local location, and
truncate the date, then link to the local file (i.e. file name would be:
'QOR_DAILY.CSV")

2) Set up VB Access code that would dynamically change the .csv link to look
for a file and date combo. The control would be to have a tbllog that would
store the file and historical dates already appended.

However, I don't know if either of these are possible with MS Access 2000?
So, are these ideas possible???? Or do I need to start looking for a
different application to complete this project?

Thanks for your suggestions.

 

[Nikos Yannacopoulos]

A2K is definitely capable of handling this. I have a third alternative
to offer though, which is basically to import the .csv file without
linking. You will need to do a manual import once so you create and save
an Import Sec, then you can use that in a TransferText method in VBA.
The "construction" of the .csv file name to import is very easy,
something like:

vImportFile = "QOR_DAILY_" & Format(Date, "mmddyyyy") & ".csv"


HTH,
Nikos

 

[John Nurick]

All this is quite possible using Access.

One thing you could do (if you just need to get at "today's" file) is to
write code that generates the file name and creates the SQL statement to
import it, e.g. this untested snippet:

Dim strFileName as String
Const F_FOLDER = "D:\Folder\Sub folder\"
Const F_EXT = "CSV"
Dim strSQL As String

strFileName = "QOR_DAILY_" & Format(Date(), "mmddyyyy")

strSQL = "INSERT INTO MyTable SELECT * FROM [Text;HDR=No;Database=" _
& F_FOLDER & ";].[" & strFileName & "]#[" & F_EXT & "];"

CurrentDB.Execute strSQL, dbFailOnError

Or if you just want the text file parsed and accessible to your code,
omit the INSERT INTO MyTable clause from strSQL and open a recordset

Dim rsR = DAO.Recordset
...
Set rsR = CurrentDB.OpenRecordset(strSQL, dbOpenSnapshot)
...

 

[Guest]

Very Clever, Nikos!
The only problem I see is either loading the same day twice (May not be a
problem) or skipping a day. If this could be a problem, then it might be
helpful to have a table of files downloaded. Have the procedure look for the
last day loaded, add a day to it, check to see if the new date is a date that
a load would be expected (like are weekends and holidays skipped), and do
this until you have a good date, then go import the file.
May be of no value, but I am just thinking out loud. well, no THIS WOULD BE
OUT LOUD

 

[Nikos Yannacopoulos]

Klatuu,

I was merely answering the particular question, but your point is valid
nonetheless. Actually I take a different approach: I have an extra field
for date in the table I import into, which I populate with the file date
(data date, not file system date) during import. I go through all files
in the particular folder, check to make sure that there are no records
in the table for a given file's date, if successful I import, I then
verify that the number of records imported equals the number of lines in
the file (minus header/footer lines), if matching I rename the file (so
it won't be imported again, but remains available just in case - clear
out those older than a week), or issue a warning message otherwise; then
move on to the next file. So, if the import process doesn't happen
everyday I don't miss anything, there are checks for successful import
etc. I suppose there can be more approaches proposed, each with its pros
and cons, this was just my $0.02's worth.

Nikos

 

[Guest]

Nikos,
Sorry if you thought I was being contray. I see your posts out here
regularly and you always have good ideas. I was not trying to contradict
what you had to say, only adding to it.
This is the interesting part of this group to me. I get to see a lot of
different ways to get to the same place. I learn from it daily.

 

[Nikos Yannacopoulos]

Klatuu,

Don't worry, I didn't get the wrong idea. I just saw you build on my
reply, and thought it was worth building on it a bit further.

Regards,
Nikos