Discussion Locked

How to clean up hidden characters

0 helpful
9 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

These days, I made many comma delimited text files exports from my ACCESS 97 tables. When one of my friends tried to import them in EXCEL, some records were damaged. When I checked the tables, I found that those records are containing hidden carriage return/line feed characters (CTRL+ENTER). Is there anybody who can help me with a solution for this problem? So far, I found out that I could use some functions to replace those characters with ? ? or ?_? (i.e., InStr()), but I don?t know the character codes for these key strokes (Chr$() ?).
Thank you very much for any idea regarding this issue.

Lucian
lucsel@home.com

9 replies

by mtnsqs

Look around for a very basic programming book - most of them had the ASCII character sets inthem - not a modern book - one from the 80's should do it

I don't have any ref books here with me at the moment to give you a more exact answer.

by ckeenan

Chr(13) and Chr(10) are carriage return and line feed repectively. using those same functions you should be able to eliminate unwanted characters.

by seagull data services

I use the following code to convert multi-line addresses to a single line. You could try and adapt it to your needs.

Function OneLine (Address As String, County As String) As String

Dim temp As String, TempChar As String, n As Integer For n = 1 To Len(Address)
TempChar = Mid$(Address, n, 1)
Select Case TempChar
Case Chr$(13)
temp = temp & ", "
n = n + 1
Case Else
temp = temp & TempChar
End Select
Next n

Select Case County
Case Is <> ""
OneLine = temp & ", " & County
Case ""
OneLine = temp
End Select

End Function

Hope this helps
John

by lucsel

Great! Your code is working fine. I used something close to yours, using VB for Access and the function InStr(). Thank you for your detailed answer!

by seagull data services

Quick Note: County is stored as a seperate field.
I use this in an Access query grid to produce an Address field:
Address:OneLine([Address],[County])

by lucsel

In my case, I didn?t check the text files that I used to load my database. They had those unwanted characters and when I made the export the errors were transferred to my friend. Thanks again.

This discussion is closed to new replies.