Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2004
    Posts
    214

    Unanswered: multiple imports

    All, i am using access 2003
    Does anyone have code to import multiple text files into multiple access tables. I need to design a form with buttons so that users are able to select and import the text files they want into a table. Then I have to create code to clean up the breaks and extra headers. Please help

  2. #2
    Join Date
    Jul 2004
    Posts
    214
    Ok I have found a way to import multiple text files in a spaghetti code fashion. See code:

    Code:
    Private Sub Command2_Enter()
    On Error GoTo Err_Command2_Enter
    
    'Delete Temp Table
                DoCmd.DeleteObject acTable, "ACHClosedAccts"
                DoCmd.DeleteObject acTable, "ACHClosedBucketAccts"
                DoCmd.DeleteObject acTable, "CMSCardNumber"
                DoCmd.DeleteObject acTable, "CompareAcctNumber"
                DoCmd.DeleteObject acTable, "HELOCS"
                DoCmd.DeleteObject acTable, "LOC"
                DoCmd.DeleteObjectcTable, "MembersFlagandReasonCd"
                DoCmd.DeleteObject acTable, "S40CreditCardNotLinked"
                DoCmd.DeleteObject acTable, "S61withoutPeriodicPymts"
                DoCmd.DeleteObject acTable, "TransfersClosedAccts"
                DoCmd.DeleteObject acTable, "VISACardNumber"
    
    'Import Text files into corresponding tables
    
    DoCmd.TransferText acImportFixed, "ACHClosedAccts Import Specification", _
    "ACHClosedAccts", "D:\0017_Errors\TextFiles\ACHClosedAccts.txt", True
    
    DoCmd.TransferText acImportFixed, "ACHClosedBucketAccts Import Specification", _
    "ACHClosedBucketAccts", "D:\0017_Errors\TextFiles\ACHClosedBucketAccts.txt", True
    
    DoCmd.TransferText acImportFixed, "CMSCardNumber Import Specification", _
    "CMSCardNumber", "D:\0017_Errors\TextFiles\CMSCardNumber.txt", True
    
    DoCmd.TransferText acImportFixed, "CompareAcctNumber Import Specification", _
    "CompareAcctNumber", "D:\0017_Errors\TextFiles\CompareAcctNumber.txt", True
    
    DoCmd.TransferText acImportFixed, "HELOCS Import Specification", _
    "HELOCS", "D:\0017_Errors\TextFiles\HELOC.txt", True
    
    DoCmd.TransferText acImportFixed, "Loc Import Specification", _
    "LOC", "D:\0017_Errors\TextFiles\LOC.txt", True
    
    DoCmd.TransferText acImportFixed, "MembersFlagandReasonCd Import Specification", _
    "MembersFlagandReasonCd", "D:\0017_Errors\TextFiles\MembersFlagandReasonCd.txt", True
    
    DoCmd.TransferText acImportFixed, "S40CreditCardNotLinked Import Specification", _
    "S40CreditCardNotLinked", "D:\0017_Errors\TextFiles\LOC.txt", True
    
    DoCmd.TransferText acImportFixed, "S61withoutPeriodicPymts Import Specification", _
    "S61withoutPeriodicPymts", "D:\0017_Errors\TextFiles\S61withoutPeriodicPymts.txt", True
    
    DoCmd.TransferText acImportFixed, "TransfersClosedAccts Import Specification", _
    "TransfersClosedAccts", "D:\0017_Errors\TextFiles\TransfersClosedAccts.txt", True
    
    DoCmd.TransferText acImportFixed, "VISACardNumber Import Specification", _
    "VISACardNumber", "D:\0017_Errors\TextFiles\VISACardNumber.txt", True
    
    Exit_Command2_Enter:
        Exit Sub
    
    Err_Command2_Enter:
        MsgBox Err.Description
        Resume Exit_Command2_Enter
    
    End Sub
    I know I can make this code more efficient but I don't know how. Shoud this be a function, is so how do I change it to a function to be called. 1st, I want a message box telling the user that the import is running and a message box to tell them it had successfully completed the Import.

    2nd, I need now is a way to clean up the breaks and the multiple headers in the tables using code or is there a way to do this before the import. As you see there are about eleven different text files going into eleven different tables. There are more to be added. Please help. Thanks

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No, it doesn't have to be a function. I can't see too many ways to make that code more efficient... aside from correcting the obvious error in the delete commands. There's nothing "spaghetti-like" in that code. Well... the command button could at least have a name I suppose

    For message boxes, use the MsgBox command or have a look at the SysCmd functionality to produce status bar progress indication.

    As for the cleanup procedure... Breaks and multiple headers could mean many things, but if you are talking about blank lines and lines that contain field headings, just run a delete query or two to clean it up.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jul 2004
    Posts
    214
    I would use your code if I could figure how to make it work for multiple text files. As you can see in my code, I have to import muiltiple files at one time. I would like to find a better way because I will be adding more files

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    That code doesn't work for multiple files. In fact, that code doesn't do much at all.... it needs a lot of work.

    Simplifying the code (removing unnecessary variables and steps), it's this:

    Code:
    Open "C:\textfile.txt" For Input As #1
    While Not EOF(1)
        Line Input #1, txtme
    Wend
    Which ultimately does nothing but put the last line of input from one file into an object called txtme. Sorry bren0098 :/
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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