[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 213
  • Last Modified:

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?

CF
0
creativefusion
Asked:
creativefusion
  • 2
  • 2
2 Solutions
 
peter57rCommented:
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()
      Loop

   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.
0
 
creativefusionAuthor Commented:
Hi Peter,

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

CF
0
 
peter57rCommented:
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.
0
 
creativefusionAuthor Commented:
Excellent help. Cheers,
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now