Importing a text with separators using VBA

Question

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

The columns are seperated using tabs (the TAB button). 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

With the import wizard the downside is that for even the slightest change in file format, you'll have to click through all those steps yet again to get the import working.

Check out @Remou's answer in ms Access import table from file in a query for a way to do it in straight SQL. I am actually using the same method in a project of mine. I use something like this (see my link for the details):

insert into MyTable (column-list...)
select (column-list...)
from [data-source-specifications].[file-name]
any-other-clauses...;

Just one caveat. If you put this SQL syntax into a normal Access query object, there's a good chance that Access will mangle it to the point where it won't even be able to open the query object. So compose and save the query in a text file while you try it out in Access. Once the query is tested and working, save it in a VBA subroutine so that Access will run it exactly as is, like so:

sub MyTableImport()
  sqlStr = "         insert into MyTable (column-list) " ' leave a space at the
  sqlStr = sqlStr & "select (column-list...) " ' end of each line of the string
  sqlStr = sqlStr & "from [data-source-specifications].[file-name] "
  sqlStr = sqlStr & "any-other-clauses... ;"

  DoCmd.RunSQL sqlStr
end sub

Answer 2

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.