General discussion

  • Creator
    Topic
  • #2077932

    IMPORT MULTIPLE TEXT FILES TO ACCESS

    Locked

    by jregan@johncranesealol ·

    I have a need to import many fixed length record files to the same database. The file names change each day. I want to automate the import. I am new to access and can use help with this. I can use the wizard to import one file but thats not a good solution for me. I tried a macro but couldn’t get it to work correctly. Any ideas?

All Comments

  • Author
    Replies
    • #3786694

      IMPORT MULTIPLE TEXT FILES TO ACCESS

      by asidonline ·

      In reply to IMPORT MULTIPLE TEXT FILES TO ACCESS

      If you are comfortable with code this will be fairly straightforward.

      The first thing to do is create an import specification. To do this, do a manual import and before you finish, click on the advanced button and save the specification.

      Thenin a new module do something like this:

      Docmd.TransferText acImportFixed, “Daily Import Specification”,”tbl_ImportTo”,”c:\source.txt”

      Look up the access help for details on the elements.

      As for the file name, i’m assuming it’s name is something like ddmmyy.txt (290800.txt). If this is the case, you can programatically work out which file to import:

      Public sub sub_Import
      Dim vls_file as string

      vls_file = “c:\” & Format(Date,”ddmmyy”) & “.txt”
      ‘This creates a value like c:\290800.txt

      Docmd.TransferText acImportFixed, “Daily Import Specification”,”tbl_ImportTo”,”c:\290800.txt”

      End Sub

      Then, you can call this procedure when the first form in the database opens up.

      This should be a good starting point for you.

      • #3786614

        IMPORT MULTIPLE TEXT FILES TO ACCESS

        by jregan@johncranesealol ·

        In reply to IMPORT MULTIPLE TEXT FILES TO ACCESS

        I have already created an import specification file to use for the import. The problem I have is the number of files to import. An example of the file name is 01-00245.pik or 02-00245.pik or 01.00233.rec…. file extensions may be rec,pik,mrc,hpk,cyc,etc….up to 200 files per day.

    • #3779358

      IMPORT MULTIPLE TEXT FILES TO ACCESS

      by jregan@johncranesealol ·

      In reply to IMPORT MULTIPLE TEXT FILES TO ACCESS

      This question was closed by the author

Viewing 1 reply thread