import tab-delimited txt into Access table using VBA

Question

I am trying to import a tab-delimited txt file into an Access table using VBA. In my code, I want to insert it into a table that has not yet been created.

Here is what I tried doing. Note - I was able to make this work with a CSV, and without including this: DataType:=xlDelimited, Tab:=True

Sub InsertData()

    'import CSV into temp table
    DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tbl_TEMP", _
    FileName:=FileNameVariable, HasFieldNames:=True, DataType:=xlDelimited, Tab:=True

End Sub

When I run this block, I get the following error on DataType:=xlDelimited, Tab:=True

Compile error: Named argument not found

How should I change this in order to pull in the tab-delimited txt file so each column from the txt has its own column in Access?

Answer 1

As you have seen from the other articles on the topic, there really isn't a generic way to import tab-delimited text files. All of the other solutions I've seen say that you should import the tab-delimited text file once, save the import specification, and then use that import specification for all subsequent imports. The problem there is that if you want to import a different tab-delimited file the specification may not match.

The only way I've found to do it generically (short of "rolling your own" code using FileSystemObject, Split(s, vbTab), etc.) is to create a completely generic specification for all 255 possible fields and use that. It requires a one-time setup as follows:

Copy the CSV data from the Pastebin here, paste it into your favorite text editor, and save it as GenericTabSpecification.csv.

Open that file in Excel, select all 256 rows and 4 columns, then hit Ctrl+C to copy.

In Access, start the import wizard for text files and choose any tab-delimited file. (We won't actually be importing it.)

When you get to the first page in the wizard, click the "Advanced..." button.

In the Import Specification dialog, verify the settings (Field Delimiter, Text Qualifier, etc.) then click the top-left corner of the Field Information grid so all rows are selected:

Hit Ctrl+V to paste the data from Excel into the grid. The grid should now contain 255 rows.

Click the "Save As..." button and name the specification GenericTabSpecification. Once that is done, cancel out of the wizard.

Now we can do a generic import from VBA using a statement like this

DoCmd.TransferText _
        TransferType:=acImportDelim, _
        SpecificationName:="GenericTabSpecification", _
        TableName:="newTable", _
        FileName:="C:\Users\Gord\Desktop\foo.txt", _
        HasFieldNames:=False