Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2006
    Posts
    1

    Unanswered: How do I import many txt file datebases?

    I was given a folder with 17 thousand text files. Each file has a main number, and then a verticale list with 3 pieces of information on each line divided by a comma. I need to get all the information in those 17 thousand files, into 1 database. How?

  2. #2
    Join Date
    Aug 2005
    Location
    Louisville, KY
    Posts
    28
    Hopefully these files have names such as file00001, file00002, file00003, etc. If so, it should not be too tough. You can make the table you want to import them into, do the first one, as your using the import wizard, click on advanced, and save the method you are using as an import protocol. This will make sure that it imports each file the same, if you are not so comfertable with this part, you will see, its pretty simple. After saving your import protocol you will need to do the vba. You will need to set up a loop to go through all the files, and import each one into your table using the protocol, and it should look something like this:

    Import Sub()
    x = 00001
    Do until x=17000
    DoCmd.TransferText acImportDelim, "Import Protocol Name Here", _
    "Your Table Name Here", "C:\...\file"& x &".txt"
    x = x + 1
    End Loop
    End Sub

    This should be about what you want.

  3. #3
    Join Date
    Sep 2004
    Location
    Tampa, FL
    Posts
    520
    as an FYI you can easily merge all the txt files into 1 file in DOS with ease. If all the files are in one location you can type:

    Code:
    copy *.txt newfile.txt
    Darasen

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You don't necessarily have to use the import function to process the file. What I do is I create a File Specification (what rickinin called a protocol) then I write code to link to the file (use the same method but use acLink as the first parameter). When you link to the file, give the new table name the same name for each file. Then base queries (Append, Select and Update queries) on the table.

    Start by creating the table that you need all of the data to be in (this could be one or more tables). Link to one file and call the resulting table tblIncomingData. Then create a few queries to transform the linked data to the table(s) in your database. Then write some code that will loop through the files in your directory (File001.txt, File002.txt, File003.txt) and link to each file one at a time and call the table tblIncomingData. Then create more code to run the queries that need to be executed on each file.

    You will want to look at the following functions/methods:

    Dir()
    DoCmd.TransferText
    DoCmd.OpenQuery
    SetOption "Confirm Action Queries",False/True

Posting Permissions

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