Import procedure

[gripper111]

Hello Members,

I am seeking advice for the following issues. I have a table in Access 2007 named "tblLogFiles" It has 5 text fields.

I have a directory on my local machine C drive named "log Files".

I have a script that runs throughout the day at different times which creates an output file to an excel .csv formatted file. Each one has a different name but is deposited into the same folder directory.

What I would love to be able to do is to have a cmd button on a form and when click it will go into that file and upload each .csv and append to this table and put a date this was done and the original file name. (example c:\log files\system1\12345.csv. The output in access table should be reduced to just "12345.csv" I do not need the entire path)

So for instance let say I have 10 files in that directory. Each file has log files that can range from 10 entries to 100. When clicking the button it will go to the first file in this directory import and append to the "tblLogFile" then date stamp and place the file name for each entry from that .csv file, then move on to the next file and so on until done. There may be several dozen files in this directory at any given time because I do not import everyday. Usually once a week or when I have time.

I would love some advice and perhaps some ideals on vba for an onclick command to do this procedures.

Thanks for you help

 

[alvechurchdata]

The File System Object has all the features you need for this task. Examples at

http://msdn.microsoft.com/en-us/library/z9ty6h50(v=vs.85).aspx.

The only possible snag is that some administrators disable the FSO because it's a security risk.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[BobStubbs]

You might find it useful to make this a two-step process. Create a table in your database that will hold the results of importing one log file - with no field validation etc. Structure your process so that you:

-- Delete all records in the temp table
-- Import one log file to the temp table
-- Run any checking processes that you might want on this data
-- Post the records from this table to your main log table. This allows you to check for any data errors, and add your date, and time and source file name information
-- Repeat this "loop" for each imported file.

I hope this is of some help.

Bob Stubbs (London, UK)