General discussion

Locked

How to clean up hidden characters

By lucsel ·
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

This conversation is currently closed to new comments.

9 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

How to clean up hidden characters

by MtnSqs In reply to How to clean up hidden ch ...

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.

Collapse -

How to clean up hidden characters

by lucsel In reply to How to clean up hidden ch ...

Poster rated this answer

Collapse -

How to clean up hidden characters

by ckeenan In reply to How to clean up hidden ch ...

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

Collapse -

How to clean up hidden characters

by lucsel In reply to How to clean up hidden ch ...

Bingo! These are the key codes that I looking for! Thanks.

Collapse -

How to clean up hidden characters

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

Collapse -

How to clean up hidden characters

by lucsel In reply to How to clean up hidden ch ...

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!

Collapse -

How to clean up hidden characters

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

Collapse -

How to clean up hidden characters

by lucsel In reply to How to clean up hidden ch ...

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.

Collapse -

How to clean up hidden characters

by lucsel In reply to How to clean up hidden ch ...

This question was closed by the author

Back to Web Development Forum
9 total posts (Page 1 of 1)  

Related Discussions

Related Forums