I have a text file with more then one delimiters. They are tabs and spaces. In Excel one can apply more than one delimiter at import time and there is an option that the consective delimiters could be considered as one.(One can try it with File->Open-> .txt file) But unfortunatly I do not see this in ACCESS file import.

How can I import a .txt (multiple delimiter) file into ACCESS using VBA?

Any clue... any concrete help is most welcome.


[I have written about this question earlier as well but didnt get reply. Is it too challenging? Atleat it is for me :) ]

The only advice I can give you is to open the text file in NotePad, change the second delimiter to match the first, delete consecutive delimiters, save it, and THEN import it into Access. Make sure to save a back-up of your original text file intact BEFORE you do any of this.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

There are plenty of ways to accomplish this. But as you know Access doesn't allow it directly.

The thing is I don't know how much effort you're going through... Is this file import a one-time thing? Are there hundreds of files to import? Is the file generated over and over?

If the file is generated over and over, go to the people who generate the file and tell them to "do it right!" :D

If it's a one-time thing, use Excel to import it, save it as Excel and link to the XLS file from Access.

If it's hundreds of these, use the Excel object model to open the import the file with your import specifications, save it as Excel and then link to it. You can record a macro in Excel while you're doing the import and use the VBA code from that in your Access VBA.

Finally, you can use the FileSystemObject to open the file, loop through each record and use the Replace() command to replace one of the delimiters. This is similar to Greg's idea but can be automated if you have more than one file to process.

Randall J Weers
Membership Vice President
Pacific NorthWest Access Developers Group
http://www.pnwadg.org