Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old June 8th, 2005, 02:59 AM
Authorized User
Join Date: Mar 2005
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via MSN to Suomi
Default Multiple delimiter Text file import into Access

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 :) ]

Old June 9th, 2005, 06:39 AM
Friend of Wrox
Join Date: Jun 2003
Posts: 1,151
Thanks: 2
Thanked 14 Times in 14 Posts
Send a message via ICQ to SerranoG Send a message via AIM to SerranoG

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
Old June 12th, 2005, 10:26 AM
Friend of Wrox
Join Date: Nov 2004
Posts: 248
Thanks: 0
Thanked 1 Time in 1 Post

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
Import text file to MS Access, ASP.net rykie Access ASP 0 August 31st, 2007 03:07 AM
How can i import a local text file into a jsp file ravibabu Java GUI 1 April 25th, 2007 04:05 AM
Comma Delimiter / Text Qualifier pearce64 Excel VBA 0 October 25th, 2005 04:57 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.