[Enter] Character Creating New Record During Import

[StephenB]

Hello all. I've tried searching the forums as well Google for an answer for my problem, but perhaps I'm not searching on the correct key words. Would someone please point me to a post that addresses the below issue.
My company has a customer service (CS) application where our CS agents document CS requests. For reporting purposes, they drop a text file containing open service requests that I then import in Access 2003 so I can analyze the data. I've had the vendor use È (ALT+0200) as the delimiter and double-quotes to enclose text.
This has been working well until the application vendor upgraded the app last week. Now, whenever a user hits the [Enter] key to create a new line in the comments section of the application, a new line is created during the import process into Access.
When I view the text file I can see the Enter Character because it is displayed as  (except it looks more like a rectangle standing in its short side.)
I thought if creating a macro to run on the text file to delete the enter characters, but I can't find a way to systemically find the character. So far, the only solution on my end is to manually delete the characters, but that's unrealistic because there are thousands of records.
Thank you in advance for pointing me to a helpful post or commenting here.
Stephen

 

[WayneRyan]

Stephen,

In existing data, you can filter out the carriage-return quite easily.

In an update query:

Update YourTable
Set    [YourField] = Replace([YourField], Chr(13), "")

If you process the text file with VBA, you can still use the Replace function.

I wouldn't worry about the thousands of records.

hth,
Wayne

 

[StephenB]

Thank you Wayne. No go on updating the table because the data is already imported and all the Chr(13)s have been converted into new recors (so they don't appear in the comments.)
I had tried searching for chr(13) in the text file, but the find fearure wasn't identifying them ("Not Found".) However, you bring up a good point, maybe I can code search and replace that bypasses MS's search and replace feature. I'll try that.
Thanks again.

 

[WayneRyan]

Stephen,

No, they are there.

Use a query and look for "*" & chr(13) & "*" and they'll be there.

Wayne

 

[StephenB]

Wayne, I tried your second suggestion, but again no results.
Just so I'm clear, you're saying that if I identify and eliminate the carriage controls in access, the records will straighten themsleves out, right?

I've been focusing on finding them in the text file. I was able to find a site site that spoke on this issue. It suggested typing the following in a Word document and then copy/paste into search in the text file....

"
"

That worked, so I performed a find and replace (with no character in the replace). That worked also, but there are other characters as well. While Chr(13) is carraige return, I believe these other characters are new line feeds which, if I recall correctly are Chr(10). That's where I'm at now.

 

[WayneRyan]

Stephen,

If you are using VBA to input the data, then the replace function is really
what you need.

The carriage returns are one of:

Replace(SomeVBString, Chr(13), "")

Replace(SomeVBString, chr(13) & Chr(10), "")

Replace(SomeVBString, vbCrLf, "")

It really should be one of those.

If that doesn't work, in the VBA Immediate Window, try ASC(Mid(SomeVBString, n, 1))

If the ASC value is something like a 8 (TAB), 12, (FormFeed), etc. you'll know
it soon.

Or, just look at the Textfile with Ultra-Edit or something that will show you the
unprintables.

hth,
Wayne


Wayne