Remove trailing invisible character(s) from text via VBScript

Question

I am scraping some web data and inserting into Access 2007. Using a .vbs script to fetch and insert data. Things were fine, until a recent upgrade of the scrapping program.

There is an extra space in each text record towards the end. Seems related to the encoding.

Trim - function does not work on this.
Tried Replace A (Circumflex) , and xA0 , no result.

Doing - Left(mystring,len(mystring)-1) right now.

Since I would be scraping millions of records, what would be the simplest and correct way to solve this?

Also, how to rectify this as the existing data in Access will now contain such 'contaminated' data? Use an UPDATE statement? How to check for last character as space?

Solution 1

Trim() will remove trailing spaces. So, if your string values end with something that looks like a space, but is not removed by Trim(), it may not actually be a space character. Another possibility would be a space followed by an invisible character.

Either way, it would be useful to know what the last character actually is.

SELECT Asc(Right(your_string, 1)) AS ascii_value
FROM YourTable;

Once you know what that character is, you can decide how best to get rid of it.

I don't know what the best way is for you. From the details which have emerged in the comments, it appears the troublesome character is a non-breaking space, ASCII 160. You want to discard that character when it occurs at the end of a string. Perhaps you also want to discard any regular space characters which precede the non-breaking space in that situation. But if non-breaking spaces are present elsewhere in the string, you want to keep those.

Additionally it seems you want to do this for existing data stored in the Access table and for new incoming data collected with your VBScript scraping procedure. From VBScript, a query can't use custom VBA functions or the Replace() function. So I don't know what to say about the new data without seeing your scraping code. For existing data, you could use an UPDATE statement, similar to the one you suggested in your question, which should work from either VBScript or from within an Access session.

UPDATE YourTable
SET your_string = Left(your_string, Len(your_string) -1)
WHERE Asc(Right(your_string, 1)) = 160;

If you also want to discard any spaces which are present ahead of the trailing non-breaking space, incorporate Trim().

UPDATE YourTable
SET your_string = Trim(Left(your_string, Len(your_string) -1))
WHERE Asc(Right(your_string, 1)) = 160;

Solution 2

The following Access VBA module code will give you a function named myRTrim() that you can use in an UPDATE query to fix the existing records, e.g.,

UPDATE [MyTable] SET [MyField]=myRTrim([MyField])

Option Compare Database
Option Explicit

Public Function myRTrim(source As Variant) As Variant
    Dim newLength As Long
    If IsNull(source) Then
        myRTrim = Null
    Else
        newLength = Len(source)
        Do While newLength > 0
            If Not IsCharToTrim(Mid(source, newLength, 1)) Then
                Exit Do
            End If
            newLength = newLength - 1
        Loop
        myRTrim = Left(source, newLength)
    End If
End Function

Private Function IsCharToTrim(testChar As String) As Boolean
    Select Case testChar
        '' characters to trim - add to this list if required
        Case " ", Chr(255), Chr(9), Chr(160)
            IsCharToTrim = True
        Case Else
            IsCharToTrim = False
    End Select
End Function

For new scrapes, you could add the same functions to your VBScript code. Or, you could bring your VBScript code into Access, tweak it for VBA (if necesary), and use it there.

Comments

[arcotenterprises]

thanks for the tip. it is 160 , a non breaking space. what is the best way to remove this ? I am inserting record by record for millions of rows , so individual character check can be done but may not be very efficient. Secondly , how to fix the existing table to check for all the records , every field and update.

[yoka]

Replace(yourtext, chr(160), " ") in VBS, similar command in SQL.

[arcotenterprises]

@arvo ,also need to make sure that the last character only is affected. Would it not be possible that there could be genuine char(160) in the middle of text.

[yoka]

I don't know your requirements; in my web related code I'm just blindly replacing nbsp's with ordinary spaces - no problems so far. BTW, while trimming you may need to remove trailing tab, cr, lf and their combinations either; it makes real trimming quite nasty operation.

[Tom Collins]

You can use a regular expression. Look here: stackoverflow.com/questions/7602301/…