CSV importing little squares

[Seb]

Hi

I have a CSV file that is imported that is some info from a scanning application. All is working ok, but it is imported as showing a few little "squares" or blocks at the end of the fields....
I've search all over and its seems to be the TAB ASCII field being imported but how do I remove this?

I've included a Jpeg for you to see the problem....And I've tried RTRIM & TRIM

Thanks guys

Seb

 

[ejstefl]

Since you are importing a CSV file, you can open it in Excel first and use the CLEAN function, which will remove all non-printable codes.

FYI, Trim will only remove leading and trailing spaces.

 

[Seb]

Exactly what I need to do....but how would you do that from within Access?
I'm trying to get this as automated as possible....

Thank by the way.

 

[ejstefl]

I don't know of a built-in Access command, but you could write a custom function to do it for you. Asc() will give you the ASCII value of a character, which you can then determine if it is a "good" value or not and delete it if not.

 

[RuralGuy]

Here's two functions for a basFunctions standard module that can be modified to suit your needs. It would currently strip all control codes from the string.

Public Function CleanString(strIn As String) As String
'-- Strip unwanted characters from the incoming string
Dim OffSet As Long
For OffSet = 1 To Len(strIn)
   CleanString = CleanString & FilterIt(Mid(strIn, OffSet, 1))
Next OffSet

End Function

Public Function FilterIt(strIn As String) As String

Select Case strIn

'   Case vbCr
'      FilterIt = strIn     '-- Allow a Carriage Return
'   Case vbLf
'      FilterIt = strIn     '-- Allow a Line Feed
''   Case "A"
''      FilterIt = vbCrLf & strIn        '-- Remove "A" as well as a test
   Case Is < " "
      FilterIt = ""        '-- Turn all other Control Codes to Zero Length String
'   Case Chr(127)
'      FilterIt = ""        '-- Remove DEL as well
   Case Else
      FilterIt = strIn     '-- Pass back everything else
   
End Select

End Function

 

[Rickster57]

squares

In a query you could try this:

expr1:Replace([thefieldwithsquares],Chr(12),"")