Results 1 to 2 of 2
  1. #1
    hchui is offline Novice
    Windows Vista Access 2003
    Join Date
    Oct 2010
    Posts
    1

    Batch import .txt

    Hi, I'm looking for help here and this is the problem. I have thousands of .txt file. Each .txt contains data of one person and there're multiple records/rows of this person in the .txt. The .txt files are tab separate delimited. 1. How do I import these .txt files into tables in Access? I don't want to do > File > Import a thousand times. 2. My goal is to stack these .txt files into one huge file that contains everyone's data. This should be a simple query if I have the tables (imported from .txt) linked to the primary key. Again, I don't want to set the primary key in each table and I don't want to draw the relationship lines a thousand times. Is there a way to do it? Any thought would be appreciated. Many thanks, Helena

  2. #2
    ajetrumpet is offline VIP
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Location
    N/A
    Posts
    2,694
    are all of these files formatted the same way? if they are, you could run a function like this:
    Code:
    Function TxtAppend(wStr As String, _
                       wPath As String, _
                       Optional bNewLine As Boolean)
    
    '******************************************************************************
    '                                                                             *
    'Author: Adam Evanovich                                                       *
    'Date: 6/12/2006                                                              *
    'Purpose: Writes information (a string) to the end of a text file.            *
    '                                                                             *
    'Arguments:                                                                   *
    'wStr > String to write into the text file.                                   *
    'wPath > Full path of the text file being manipulated.                        *
    'bNewLine > Indicates whether or not to write the data to a new line.  If no, *
    '           data will appended immediately following the last character in    *
    '           the text file.  If omitted, false will be assumed.                *
    '                                                                             *
    '******************************************************************************
    
    On Error GoTo Cleanup
    
    Const wMode = 8& 
    
    Dim fso As Object
    Dim oFile As Object
    
       Set fso = CreateObject("Scripting.FileSystemObject")
       Set oFile = fso.opentextfile(wPath, wMode)
       
          If bNewLine Then
             oFile.writeline vbNewLine & wStr
          Else
             oFile.writeline wStr
          End If
             oFile.Close
    
    Cleanup:
        Set fso = Nothing
        Set oFile = Nothing
        
    End Function
    I got that from here: http://www.ajenterprisesonline.com/_...me=TxtAppend()

    take a look at the 'txt' functions on this page: http://www.ajenterprisesonline.com/_functions/

    you could easily loop them all and append them to a single file. at that point, you could use the wizard just once, since you want to get it into one table anyway, right?

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Shell with Batch Variables
    By robbyaube in forum Programming
    Replies: 2
    Last Post: 12-19-2019, 11:06 AM
  2. Batch change hyperlinks to images
    By amer in forum Access
    Replies: 2
    Last Post: 06-09-2010, 06:56 AM
  3. Running Batch File
    By NoiCe in forum Programming
    Replies: 2
    Last Post: 03-21-2010, 07:05 AM
  4. Open Accesss DB by batch script
    By micada in forum Access
    Replies: 0
    Last Post: 06-10-2008, 02:33 PM
  5. New Database - Batch processing
    By stevo2820 in forum Database Design
    Replies: 0
    Last Post: 04-30-2007, 02:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums