Importing Data - arbitrary end of record delimiter

[Guest]

I have a text file that uses the '^' character for field delimiters, and the '~' character for end of record
I know access can change the field delimiter, but can the end of record be specified anywhere
The reason the end of record is '~' is because there are embedded newline characters in some of the fields
Thanks.

 

[Ronald W. Roberts]

I have a text file that uses the '^' character for field delimiters, and the '~' character for end of record.
I know access can change the field delimiter, but can the end of record be specified anywhere?
The reason the end of record is '~' is because there are embedded newline characters in some of the fields.
Thanks.

I'm not sure this is a problem. I don't know how or what created the
text file, but you can't just change the EOF mark.
If you open the file in Notepad or Write, you should see the ~ character
in the data as the last data character in the file,
not as the EOF. I don't know of any editing program that shows the EOF
unless it is a hex dump of the disk.

Also you can use the input statement to read in the data until you find
the ~ character.

Ron

 

[John Nurick]

Hi Alex,

Access's text file import system assumes CR+LF - Chr(13) & Chr(10) - as
a record separator and there's no way of changing this.

The only way Access will reliably import text data containing newlines
is from a CSV file in which the text fields are delimited with quote
marks. (Also, Access textboxes require CR+LF for newlines.)

So there are three ways to go.

1) Use Perl or a text editor to (a) change your field separator from ^
to , and enclose every field that might contain a newline in " "; (b)
if necessary change the newlines to CR+LF; (c) replace ~ with CR+LF.

2) Use Perl or a text editor to (a) replace newlines with an otherwise
unused character such as |; (b) replace ~ with CR+LF. Then import the
file. Finally run an update query to replace all | with CR+LF.

3) Write VBA code to read and parse the file character by character.