Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    2

    Unanswered: Create MACRO for Import FixedLength Text File to Access Table

    Hello
    I'm a new user and I and I take this opportunity to greet all member of forum.

    I have a problem.
    For work, I have to convert very often in file ACCESS .mdb, many TXT files that I download via batch terminal, each with its own file .MAS that contains the maximum allocation of fixed fields.


    I want to make a macro from the file specification .MAS (I might as well convert it to .ini file or something that suggested to you) that create directly .mdb file without my having to manually specify the length of the fixed fields.

    I read the TransferText method. Could do for me?

    I accept tips

    Thanks to all

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    no but if you make an effort and it doens't quiote work we may try.

    another approach is to write a code module that handles this, builds in suitable error trapping / handling and so on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    Without doing your work for you, may I suggest that vba code in a module will work for you as you can set up a loop, and using the DIR command you can search the folder where your text files are stored and import every file found. Inside the loop each text file can be imported into a prepared table using TransferText and the saved fixed-length import specifications that you create for your text files.

    For your fixed-length import specs to work for every file, all text files must have the same format, that is, the starting columns of each field must be the same in every text file. Good luck.

  4. #4
    Join Date
    Oct 2011
    Posts
    2
    i have no idea my friends...

    the document on internet are poor.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    actually Ive found the documentation and on line help pretty good....
    and some of the online resources are also excellent
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Jan 2002
    Location
    Bay Area
    Posts
    511
    This is what you should do:
    1) make a table to receive the imported fixed-length text we'll call Table1
    2) create import specifications by importing one of the text
    files and saving the specifications. Make a note of what
    name you give the specs. I would import all fields as text,
    but someone else might have a different opinion. Do the import
    into the empty Table1, making sure the specs work.
    3) Add a module and write a function we'll call Import_TextFiles
    4) In the function, declare the variables, such as:
    Code:
    Dim tblName As String
    Dim fileToImport As String
    Dim textFilePath As String
    Dim counter as Integer
    5) Assign values to the 1st three variables.
    TextFile_Path and fileToImport will be assigned something like this.
    You will write in your own path.
    Code:
    TextFile_Path = "C:\Import Files\"
    fileToImport = Dir(TextFile_Path & "\*.txt")
    6) Here is some code and/or descriptions for this process:

    Code:
    fileToImport = Dir(TextFile_Path & "\*.txt")
        
      If fileToImport = "" Then
            MsgBox "No files were found in the folder" & vbCrLf & TextFile_Path
        Else
            <if you want to start with an empty Table1, you can empty it here>
            <continue with the import process using a loop>
    Do While fileToImport <> ""
            < use TransferText to import (TextFile_Path & fileToImport)>
             fileToImport = Dir      'if there are more text files, this will assign
                                          'the next text file name to the variable
            <add 1 to counter>
    Loop
            MsgBox "All done importing " & CStr(counter) & " files."
    End If
    The counter is there to give you a sense of what you just imported, but
    you should always test when developing code to be sure that you are getting
    all the data from the text files, and that every text file in the folder is imported.
    If the import is done periodically, be aware of the number of text files you have
    and how much import data to expect. Your new code will give you all the time
    you need to do a check for accuracy.

    If you want to empty the table before importing, this code will do it:
    Code:
    DoCmd.SetWarnings False
    sqlText = "DELETE * From [" & tblName & "]"
    DoCmd****nSQL sqlText
    DoCmd.SetWarnings True
    To run the code in the module, you can create a macro, under Action, add a RunCode, and for Function Name, type in Import_TextFiles()
    In the module, remove the word "Private" from the first line, so that the macro can find the function.


    I hope this will give you enough clues to start coding.
    Last edited by JerryDal; 11-08-11 at 15:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •