Importing Multiple Text Files using File Name in Table

Question

Hi Everyone,

I currently receive several daily text files from one of our vendors that I import into a Access 2007 database table. All these text files have the same fields. I also have a separate table with the file names of these text files. Currently, I import these manually using the import button on the ribbon.

What I want to do is use a VB code that compares the name of the text files (all of which are located in the same directory) to the list of file names in my table. Then import any text files with file names not in my table. I have a file import specification saved that can be used in this process.

Can this be done? Please let me know if I can provide more information.

Many thanks,

Kevin

Answer 1

In VBA, loop over the files in the directory, with Dir "C:\path\*.txt"   as example (using a wildcard to identify all possible files). Inside the loop, check if the file is in the list of already imported data, or not, and if it is not, make a copy of that file  ( FileCopy )  into file name which is the one used by your import specs. Import the data. Append the orignal file name to the list of files already handled. Continue the loop.

Answer 2

Hi Vanderqhast,

Thanks for your reply. I'm still new at VB. Do you have an example code you can share with me?

Best Regards,

Kevin

Answer 3

Hi Kevin,

Welcome to the MSDN forum.

Please refer to the following code:

Sub DailyImport()
Dim rs As Recordset
Dim sql As String

Dim sPath As String
sPath = Dir$("D:\00-Access\TestDailyImport\" & "*.txt", vbNormal)

Do Until sPath = ""
    sql = "SELECT * From tbFiles Where FileName='" & sPath & "'"
    Set rs = CurrentDb.OpenRecordset(sql)
    
    'Check if the file name existing or not
    If rs.RecordCount = 0 Then
    
        'Import the data from sPath with the specification
        DoCmd.TransferText acImportDelim, "Daily Import Specification", "tbData", _
            "D:\00-Access\TestDailyImport\" & sPath, True
        
        'Insert the file name into the table
        sql = "Insert Into tbFiles (FileName) Values('" & sPath & "')"
        CurrentDb.Execute sql
        Set rs = Nothing
    End If
    sPath = Dir$
Loop

End Sub

tbData - The table store the data from the text file

tbFiles - The table store the name of the imported text file

Have a nice day.

---

Yoyo Jiang[MSFT]
MSDN Community Support | Feedback to us

Answer 4

Thanks. This works great! Kevin

Answer 5

Maybe something like this:

http://www.accessmvp.com/KDSnell/EXCEL_Import.htm#ImpBrsFldFiles

Change the following line:

strFile= Dir(strPath & "\*.xls")

strFile = Dir(strPath & "\*.txt")

---

Ryan Shuell