How to import data delimited by Enter key

[Guest]

I have a file exported from a DOS program and the 10 "fields" in each record
are "delimited" by the Enter key. In other words, the file contains 10
separate lines for the first record, 10 lines for the 2nd record, etc. I can
define a database with 10 fields but how do I tell the Import program that
the delimiter is the Enter Key?

 

[Ken Snell \(MVP\)]

You can't tell ACCESS to use "Enter" as the delimiter key for an import.
When that key is pressed in a text file, a line feed character and
end-of-line character are inserted and they don't serve as delimiters; they
serve as "end of the line" markers.

Best way to do what you want would be to write a VBA routine to read the
text file one line at a time and write data to a recordset that is based on
the table. Read ten lines into ten variables, then create a new record in
the recordset; repeat until you reach the end of the text file.

 

[John Nurick]

This VBScript
http://www.j.nurick.dial.pipex.com/Code/VBS/talltowide.vbs
converts a file like your description into an ordinary tab-delimited
file that Access can import.

If you download it, you can either run it as is, or use it as the basis
for a VBA procedure.

 

[Guest]

John,

Thanks for the VBS script, it worked great. Conversions from "records" to
"tab delimited" worked with no problems at all. Only problem was when I
imported the table into the DB the first time it assumed the Zip field was a
long integer and gave me errors on a few records. I just set that field back
to "text", reran the import and all 400 records (in the first of about a
dozen files) came through perfectly. I never expected a response so fast and
with the right answers.

 

[John Nurick]

Thanks for the feedback. I wrote the script a while ago when I had the
same sort of task, so it only took a moment to upload it to the web.