gerrymcd
asked on
Invalid Characters-How to remove them?
im converting an XML File to an Access Database. the problem i keep having is the XML file is full of invalida characters like;
and so on. How can i clean up the data being saved to the xml file, i.e remove the invalid chars. so far my sub looks like this but it just keeps getting longer;
Private Sub Clean_XML_Export2(myExport struct As datastruct)
Dim s As String, tmp() As String, i As Integer
s = "&|" & Chr(34) & "|" & Chr$(0) & "|<|>|=|+|-|!|?|" & vbNullChar & "|" & vbNull & "|" & vbCr & "|" & vbLf & "|" & vbCrLf & "|¤|ö|Ö|í|é|´||||||‘|’|¥||||||| |||||"
tmp = Split(s, "|")
'MsgBox Chr(34)
For i = 0 To UBound(tmp)
With myExportstruct
.album = Trim(Replace(.album, tmp(i), ""))
.artist = Trim(Replace(.artist, tmp(i), ""))
.file = Trim(Replace(.file, tmp(i), ""))
.genre = Trim(Replace(.genre, tmp(i), ""))
.track = Trim(Replace(.track, tmp(i), ""))
.year = Trim(Replace(.year, tmp(i), ""))
.Comment = Trim(Replace(.Comment, tmp(i), ""))
.Bitrate = Trim(Replace(.Bitrate, tmp(i), ""))
End With
'Debug.Print tmp(i) & " " & Asc(tmp(i))
Next
End Sub
and so on. How can i clean up the data being saved to the xml file, i.e remove the invalid chars. so far my sub looks like this but it just keeps getting longer;
Private Sub Clean_XML_Export2(myExport
Dim s As String, tmp() As String, i As Integer
s = "&|" & Chr(34) & "|" & Chr$(0) & "|<|>|=|+|-|!|?|" & vbNullChar & "|" & vbNull & "|" & vbCr & "|" & vbLf & "|" & vbCrLf & "|¤|ö|Ö|í|é|´||||||‘|’|¥|||||||
tmp = Split(s, "|")
'MsgBox Chr(34)
For i = 0 To UBound(tmp)
With myExportstruct
.album = Trim(Replace(.album, tmp(i), ""))
.artist = Trim(Replace(.artist, tmp(i), ""))
.file = Trim(Replace(.file, tmp(i), ""))
.genre = Trim(Replace(.genre, tmp(i), ""))
.track = Trim(Replace(.track, tmp(i), ""))
.year = Trim(Replace(.year, tmp(i), ""))
.Comment = Trim(Replace(.Comment, tmp(i), ""))
.Bitrate = Trim(Replace(.Bitrate, tmp(i), ""))
End With
'Debug.Print tmp(i) & " " & Asc(tmp(i))
Next
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You may find
tmp = Array(vbCrLf, Chr(27), Chr(0))
is a quicker way to write your array (so long as tmp() is a variant, not a string).
As for the actual question, perhaps you should create an array of the characters that are allowed as I am sure there will be far more that are not (thanks to unicode, etc).
Alternatively, how about checking the strings with a LIKE operator: http://www.devx.com/vb2themax/Tip/18578 has more details...
HTH
J.
tmp = Array(vbCrLf, Chr(27), Chr(0))
is a quicker way to write your array (so long as tmp() is a variant, not a string).
As for the actual question, perhaps you should create an array of the characters that are allowed as I am sure there will be far more that are not (thanks to unicode, etc).
Alternatively, how about checking the strings with a LIKE operator: http://www.devx.com/vb2themax/Tip/18578 has more details...
HTH
J.
0x20 – 0x7E