Batch Import text files to Access

[gdaniels]

Hi, I need some help. The routine below runs when a command button is clicked. A prompt appears to enter the directory path to the folder containing delimited text files to be imported into an Access 97 table. The test folder contains 9 files. The problem is that the 1st file is imported 9 times into the table. How do I correct the routine so it loops properly and imports each file? Thanks in advance.


Gary


Private Sub Command0_Click()
Dim InputDir, ImportFile As String, tblName As String
Dim InputMsg, Name As String

InputMsg = "Type the pathname of the folder that contains "
InputMsg = InputMsg & "the files you want to import."
InputDir = InputBox(InputMsg)
' Change the file extension on the next line for the
' type of file you want to import.
ImportFile = Dir(InputDir & "\*.txt")
Name = ((InputDir) & ("\") & (ImportFile))
Do While Len(ImportFile) > 0
' Use the import file name without its extension as the table
' name.
tblName = "Expenses"

DoCmd.TransferText [acImportDelim], "Batch", "ExpBatch", Name
ImportFile = Dir
Loop
End Sub

[PEB]

Hi,
When your file is inserted in your database it'll be good to change its extension from txt to tx1 for exemple! So this file won't apear in your file list to insert in the database

To do this you have to use:

Name OldName As NewName :)

[ryjfgjl]

Try this tool:
ExcelToDatabase: batch import excel into database automaticly

[cactusdata]

This works for me:

Private Sub ImmportFiles_Click()
 
    Dim InputDir        As String
    Dim ImportFile      As String
    Dim InputMsg        As String
    Dim Name            As String
 
    InputMsg = _
        "Type the pathname of the folder that contains " & _
        "the files you want to import."
    InputDir = InputBox(InputMsg, "Import text files")
 
    ' Change the file extension on the next line for the
    ' type of file you want to import.
    ImportFile = Dir(InputDir & "\*.txt", vbNormal)
    Do While Len(ImportFile) > 0
        Name = InputDir & "\" & ImportFile
        ' Use the import file name without its extension as the table name.
        DoCmd.TransferText acImportDelim, "Batch", "ExpBatch", Name
        ImportFile = Dir
    Loop
 
End Sub