Characters as numbers and special characters

Question

here is the description: I am making a .csv file from the .xls file by copy/paste. Than .csv file is loaded in the table in the database.

Problem: When I copy the data from the file some "special" characters are also copied. However these "special" characters are not noticed in the .xls file because there they are show as blanks.

Question: Which tool (freeware) or which method can I use to identify this "special" character so I can remove them? I supose it should be some tool that identifies a particular character by some of its numeric equivalent.

Notepad find / replace?? - Or maybe just load the Excel file into the DB directly.... — John Bustos, Mar 21 '13 at 14:39
http://notepad-plus-plus.org/ - NotePad ++ also has `Regex Matching` for removing / replacing lots of specific characters at once — John Bustos, Mar 21 '13 at 14:42
it is not problem to replace them. It is problem to identify them. When I open the file in Notepad++ (which I am using) instead of special characters I see only blanks — Adam, Mar 21 '13 at 14:46
If you open it in Notepad++ and save it and it keeps the special chars, then they are there, you just can't see them (they're seen as spaces to you) - Maybe a regex replace along the lines of `Posix [:print:]` - http://www.regular-expressions.info/posixbrackets.html — John Bustos, Mar 21 '13 at 14:58
You can read this CSV file binary into a string variable, and then do a `Replace` of the special characters that are causing problems, and then `Write` the edited string back to the file and save it. — David Zemens, Mar 21 '13 at 15:09
http://stackoverflow.com/questions/10434335/text-file-in-vba-open-find-replace-saveas-close-file — David Zemens, Mar 21 '13 at 15:14
@Adam have you had a chance to try out the suggestion I posted? — David Zemens, Mar 21 '13 at 17:07
@David Zemens, thanks for your suggestion. However I was looking a different solution - one that does not include going down to coding level. There is a possibility to load the file into unix envirnment and than open it with vi/vim editor. This editor automatically transforms special characters masked as blanks. Since I am working in Win XP Environment, this method is not very handy. So, if you could recommend some tool that does the same, that would helo me a lot — Adam, Mar 22 '13 at 12:55
@Adam can't help you there... I guess I was answering the second part, "*which method can I use to identify this 'special' character...*" I personally wouldn't use a 3p tool when there is such a simple VBA-based solution to this problem. Once you identify the special characters' `Chr()` values, it's probably no more than ~30 lines of VBA. — David Zemens, Mar 22 '13 at 13:51

score 1 · Answer 1 · edited May 23 '17 at 11:49

From a previous question:

Text file in VBA: Open/Find Replace/SaveAs/Close File

Sub ReplaceStringInFile()

Dim sBuf As String
Dim sTemp As String
Dim iFileNum As Integer
Dim sFileName As String

sFileName = "C:\Temp\test.txt" '<-- Edit as needed

iFileNum = FreeFile
Open sFileName For Input As iFileNum
Do Until EOF(iFileNum)
    Line Input #iFileNum, sBuf
    sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum
sTemp = Replace(sTemp, "THIS", "THAT")  '<--- edit as needed

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

End Sub

Probably what you will need to do is change this line:

sTemp = Replace(sTemp, "THIS", "THAT")

To something like this:

sTemp = Replace(sTemp, Chr(10), vbNullString)

Which would replace all carriage returns with a null. Ovbiously, you will need to somehow identify what is the CHR() value(s) that you want to remove.

Here is a list of CHR() values that might be helpful.

http://www.techonthenet.com/ascii/chart.php

Characters as numbers and special characters

1 Answers1