List out all different character types found in a table

[kjv1611]

In some projects where I work, folks copy/paste values into database text fields, and Access doesn't seem to have a problem with even odd characters that apparently come form copy/pasting from scanned documents. Well, SQL Server does take issue with the characters, but I have no way of guessing where those characters might be.

I'd like to do one or a combination of the following:
1. List out any fields and values (and maybe the UniqueID field to help locate it) for anything that is not an ASCII character.
2. List all character types found in the table, so at least I can get a starting place as to what to look for
3. Maybe something else I'm not thinking about??



"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Andrzejek]

First I would make sure the Fields in the table(s) are set the way they should, ie. Dates are Dates, not text. The same goes for Numbers and other data types.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[kjv1611]

Thanks - they are set that way. The problem is the text fields. Apparently, normal text fields will allow non-ascii characters, at least it seems that way from the import/export errors.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I found this thread from 2001 on another site that shows a way to possible get what I need via VB, but I need it in VBA.

http://www.vbforums.com/showthread.php?100382-How-to-check-if-a-string-has-all-ASCII-characters

(Post # 19)

The code is:

dim ba() as byte, s as string, i & 
s = "blah " 
ba = s 
for i = 0 to ubound(ba) step 2 
if ba(i)>127 then 'WARNING NOT ASCII 
next i

Any thoughts on converting that?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

the method they talk of using is a byte array, by the way.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

Alright, found this discussion:

http://stackoverflow.com/questions/998746/how-to-convert-a-simple-string-to-byte-array-in-vba

And this particular bit of an example, so maybe I can go that route... will see..

Public Sub Main()
   Dim b() As Byte
   Dim s As String
   s = "Whatever"
   b = s  'Assign Unicode string to bytes.'
   s = b  'Works in reverse, too!'
   Debug.Print s
End Sub

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[strongm]

>The problem is the text fields

In SQL Server nchar, nvarchar, and ntext datat types are the same as char, varchar, and text - but handle Unicode fine

 

[kjv1611]

Well, do they handle line breaks fine? Maybe I just need to search for Chr(10) and Chr(13)? I've done that in the past, but I didn't think that was the issue this time. Hopefully I'll be able to check that today.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[randy700]

Something like this might be what you want.

Public Function Validate()
    Dim s As String
    s = "whatever"
    For i = 1 To Len(s)
        If Mid(s, i, 1) Like "[a-z]" Or IsNumeric(Mid(s, i, 1)) Then
            Debug.Print (Mid(s, i, 1))
        Else
            Mid(s, i, 1) = " "
            Debug.Print (Mid(s, i, 1))
        End If
    Next
    Debug.Print s
End Function

Randy

 

[Andrzejek]

Randy,
Wouldn't that detect just a-z and 0-9?
What about A-Z, Space, Tab, [tt]~!@#$%^&*()_+:";'<>?,./|\,[/tt] and other 'valid' chracters?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[PeteJohnston]

I had exactly your problem. There were a few problems with character sets but the eventual approach was to create a table tblCharSet with all of the valid characters. I then created a work table with three fields

LineID      Autonumber
Original    Memo
Changed     Memo

This concatenates the fields from the table being checked. ColumnNames needs to be the string that concatenates the field names from your table.

db.Execute "Delete From tblImport"
db.Execute "ALTER TABLE tblImport ALTER COLUMN LineID COUNTER(1,1)"
sSQL = "Insert Into tblImport (Original) Select [i]ColumnNames[/i] From tblImport2"
db.Execute sSQL
db.Execute "Update tblImport Set Changed = Original"
db.Execute "Drop Table tblImport2"

This then replaces each valid character with a zero length string.

sSQL = "Select Actual From tblCharSet Where Status = 1"
Set rst = CurrentDb.OpenRecordset(sSQL)
With rst
    .MoveFirst
    Do While Not .EOF
        sActual = Replace(.Fields("Actual"), "'", "''")
        CurrentDb.Execute "Update tblImport " & _
                           "Set Changed = Replace(Changed, '" & sActual & "', '') " & _
                           "Where InStr(1, Original, '" & sActual & "') > 0"
        .MoveNext
        Loop
    .Close
End With
        
' Get rid of any blanks
CurrentDb.Execute "Update tblImport Set Changed = Replace(Changed, ' ', '')"
    
Set rst = Nothing

Any record where the Changed field has anything left shows you the characters it didn't like and the LineID field gives you the number or the record in error.

Simples

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[kjv1611]

PeteJohnston,

Thanks ton for sharing. I'll try to take a look at that if I can get another item completed at work, unrelated. Hopefully I WILL get back to this very soon for sure.

I'll be sure to post back with results once I can look at it.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[PeteJohnston]

Woops - typo time!

I just noticed that my reference to tblImport2 deletes it after it has finished. That is because I was importing the data from Excel and only needed the table until I had populated tblImport. You probably don't want to delete your table

Also the last sentence should have "number of the record in error"

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[kjv1611]

Thanks for pointing that out. Still haven't had time to get back to that particular item anyway. And also, I was initially testing on a copy of the data anyway. I'm being even more copy happy about things after I accidentally somehow deleted a whole huge table of data a month or two ago! Scary moment that was!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[kjv1611]

I'm not saying I won't need to check for characters, b/c I KNOW I have before on a similar project, but so far I found a data entry error (few of them actually) in a date format field. So even though it's a date formatted field, Access allowed them to enter invalid dates, and SQL didn't like the invalid dates. So got those fixed, now I cross my fingers and see if the import/export to SQL finally works!

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57