Importing a text with separators

Question

I am trying to automate the adding of new text files, which all have the same (known) layout.

The columns are separated using tabs (the TAB button). My question is, is it possible to do this in VBA? Like, in the access wizard for importing text files?

I am using the DoCmd.TransferText method in VBA

Answer 1

You'll need to go through the wizard once to make your specification file. TO do this import your text file like normal but before you get too deep into the wizard click on the bottom left, the "Advanced..." button. This is where you make your spec file.

Make ll these columns match your input file, data types and all. Be sure to select the {tab} field delimiter and the appropriate text qualifier if you are using one.

Save your spec (which can later be edited by coming back to this same screen and clicking Specs... then saving over your old one)

Now you can use in VBA like this

DoCmd.TransferText acImportDelim, "your spec name", "destination table name", sourceFilePath

There is a parameter HasFieldNames that you'll have to decide if it is true or false based on your file.