Access VBA import txt using DoCmd.TransferText Method

This Access VBA tutorial explains how to import txt using DoCmd.TransferText Method in one Table or separate Table.

Microsoft Access – import TXT using DoCmd.TransferText Method

In my previous post , I explained how to use DoCmd.TransferText Method to export queries to files such as txt, this tutorial explains how to import txt into Table.

First prepare a TXT file such as below

Then import the txt file manually. Before the final step, create a specification (which field is what data type, and separated by what delimiter). In the below example, I name it Employee spec. You don’t have to really import the file, just create the specification and then cancel the import. Creating a specification is not mandatory but it makes sure the data type is correct for each field and data are delimited correctly.

 

Press ALT+F11, insert the below Macro in a Module. It will import Query1 into a new Table called “import_table” using specification “Employee Spec”.

Public Sub import_query()
 DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Employee Spec", TableName:="import_table", FileName:="C:\test\Query1.txt", hasfieldnames:=True
End Sub

 

Run the Macro, now the table is created. Note that if the Table already exists, it will append data into the existing Table regardless whether the Table is empty.

Import all TXT files in a folder in a single Table

For example, we have two employee lists under C:\test\

We want to import these two employee lists into an Access

 

The below Macro imports all text files inside folder C:\test\. Access automatically creates a Table called Employee_tbl, the data structure will use specification Employee Spec.

Public Sub import()
    Dim FileNm, FilePathName, Path, FileNameList() As String
    Dim FileCount As Integer
    DoCmd.SetWarnings False
    Path = "C:\test\"
    FileNm = Dir(Path & "")

    While FileNm <> "" And Right(FileNm, 3) = "txt"
        FileCount = FileCount + 1
        ReDim Preserve FileNameList(1 To FileCount)
        FileNameList(FileCount) = FileNm
        FileNm = Dir()
    Wend

    If FileCount > 0 Then
        For FileCount = 1 To UBound(FileNameList)
            FilePathName = Path & FileNameList(FileCount)
            DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Employee Spec", TableName:="Employee_tbl", FileName:=FilePathName, hasfieldnames:=True
        Next
    End If
    DoCmd.SetWarnings True
End Sub

 

Result

 

Import all TXT files in a folder in separate Table

Modify the TrnsferText argument highlighted below to import txt file in separate Table.

DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="Employee Spec", TableName:=FileNameList(FileCount), FileName:=FilePathName, hasfieldnames:=True

Result