Loop through file path directory and write records to table

Posted on 2011-10-12
Last Modified: 2012-08-14
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?

Question by:creativefusion
    LVL 77

    Accepted Solution

    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.

    Author Comment

    Hi Peter,

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

    LVL 77

    Assisted Solution

    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.

    Author Closing Comment

    Excellent help. Cheers,

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now