Remove non-printable characters in a string

[lazaridisaris]

I am importing data from a text file (unicode) with "|" delimiter. The problem is that this file has some non-printable characters in some fields and when I try to parse the text it hangs with unspesified errors.

I wonder if there is a way to clean-up all those characters before inserting the data to my DB.

I am using Access 2000 2003

Thanks in advance

Aris

 

[CautionMP]

Yes, there are a couple of ways depending on how you import the data now.

A question for you, how are you importing the data currently?

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lazaridisaris]

Thanks for your time
I am using
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.getfile(TmpFile)
Set ts = f.OpenAsTextStream(ForReading, TristateTrue)


k = Rs.Fields.count - 1

Do While Not ts.atendofstream

s = ts.ReadLine
' Do something with the line
Loop


ts.Close

Regards
Aris

 

[CautionMP]

First thought:

Dim intCharToReplace As Integer
For intCharToReplace = 1 to 31
  s = Replace(s,Chr(intCharToReplace),"")
Next intCharToReplace
s = Replace(s,Chr(127),"")

This handles the entire string, it could also be used on individual fields once the [tt]s[/tt] has been parsed into records.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lazaridisaris]

Thanks I will try that.
But I am wondering if it will work for Unicode Characters

The character that exists in the string is when I use the ascW() function something like " 0"

which means that it is not in the range 1-31 or 127

when I open the file in Word it shows like a square end when I display all no printable characters it is like ÿ

Thanks

I will reply to you about the results

 

[lazaridisaris]

I tried your suggestion by entering 0-31 and the result was to remove 160 characters after the first occurance of the non-printable character.

R32|0026|0001|5001868439|1997-12-03|4550390251872009|EUR|50|N/A|IC|N/A|2005-06-13|Bucket 13|0034796245| |2000-1-1|2002-12-31|1800.0000|1800.0000|0.0000|0.0000|0.0000|||204.6700|Bucket 13|1421.9400| | | || |120|0001|2006-04-30|2003-07-08|Bucket 0||881.4400| |00000|997| |.0000| |||2003-07-08|W|0.0000||2003-07-09|1|| |2|0|Bucket 13||Bucket 13|1421.9400|2005-06-13|2025-06-13|Legal|0|110|00000548| |LG-CALL

this is the first line of the text and the mentioned character is after the |0034796245|

I dont know if you will receive the text as it is.

but at least I tried.

Regards

Aris

 

[CautionMP]

For grins and giggles, try this.

Since it looks like the first non-printing character is in the 15[sup]th[/sup] field (xero based array), let's see what this kicks out:

Dim r() As String
  r = Split(s, "|")
  For intChar = 1 To LenB(r(14)) Step 2
    Debug.Print AscW(MidB(r(14), intChar, 2)), _
    MidB(r(14), intChar, 2)
  Next intChar

CMP
P.S. I missed Unicode in your original post which means I'm poking around in the dark here. Sorry about that, I will still do my best to help out.


(GMT-07:00) Mountain Time (US & Canada)

 

[lazaridisaris]

I changed the debug to
Debug.Print "Start :"; AscW(MidB(r(14), intChar, 2)), MidB(r(14), intChar, 2) & ":End"

and I got
Start : 0 :End

I am confused

What is that character ?

Thanks

 

[CautionMP]

0 or 0000 is a unicode null (Unicode.org), maybe try the original [tt]Replace()[/tt] function, but go from 0 to 31?

Stepping back twords simple, have you played with the [tt]StrConv()[/tt] function using the conversion constant [tt]vbFromUnicode[/tt]? It might do the job without any fancy code.

CMP

(GMT-07:00) Mountain Time (US & Canada)

 

[lazaridisaris]

I have tried everything even I wrote a project in VB 2005 to convert the strings and only that worked.

by replacing the String with " " where ascW() was 0.

and worked fine

by the way you have helped me a lot with the split function
I did'nt new about that.

It helped me to parse data about 20 times as faster than I was parsing before.


Thanks a lot

Regards

Aris

 

[JerryKlmns]

APH

???????? ??? ????? ?????? ?? ?????? ?? ?????? ??? Altamira ?? Access. ?? ???????? ??? ?? ????? ??????. ??? ?????? ?? ???? ???!

????????? ?????

 

[lazaridisaris]

Den diavazontai ta ellinika
poios eisai ?

 

[JerryKlmns]

THN PATHSA

57598 TO ESOT

GERASSIMOS KLMNS

 

[lazaridisaris]

den vgazo tipota
2109409569
aris