Loop through file path directory and write records to table

Hi All,

I have a static folder located in a directory that contains a dynamic number of csv files each of which contain exactly the same data. These files are updated daily automatically by an external system.

What I am wanting to do using access VBA is loop through the files and write the records contained in the files to a master table called DT_SKU_BY_LOC in MS Access 2002.

Has anyone got any ideas on how I could do this as quickly and efficiently as possible?

Who is Participating?
Untested code...It assumes you have already created an import spec with a known name.

Sub DirLoop()

      Dim MyFile As String
      Dim mypath as string
      mypath = "c:\mydata\"      ' or wherever your files are
      MyFile = Dir(MyPath & "*.csv")

      ' Start the loop, and continue until there are no more files found.

      Do While MyFile <> ""
              docmd.TransferText acImportDelim,"myspecname","tablename", Mypath & Myfile, True  
               MyFile = Dir()

   End Sub

In my opinion imports should always be done to an intermediate or holding table , and never to the final target table.  Then, if there are any errors there is no need to unpick anything.  Once you are satisfied that all the data is OK then use an append query to do the fnal posting to the target table.
creativefusionAuthor Commented:
Hi Peter,

Could you please point me in the right direction for an import spec?

You have to go through the import process on one of your files manually, and on the second(?) step you click 'Advanced' which allows you to save the definition as a named specification.
creativefusionAuthor Commented:
Excellent help. Cheers,
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.