Dirty Data Problem

[aaalex]

I'm building an access process to import, reformat and export data originally extracted to excel from a project management system.
One of the fields is filled with carriage returns, tabs, etc. It imports fine but when I export as tab delimited it hosed everything up.

I can identify the bad records but haven't been able to find an easy, reliable way to clean them up.

Hope someone has run in to this and figured a way to automate cleanup.

 

[RuralGuy]

Are you just trying to get rid of those CR's and TAB's? How about creating a function that does that and use it on those fields?

 

[datAdrenaline]

There are a couple of options ...

1) While the file is in Excel, you can use Excels function named CLEAN().
2) You can reference the Excel Object Library in the Access file and use CLEAN() again. In the VBA editor, use Tools > References, then click on the Excel Object Library, then create a function that calls Excels CLEAN() function ...

Public Function fClean(strString As String) As String
    fClean = Excel.WorksheetFunction.Clean(strString)
End Function

3) You can create you own User Defined Function to strip the Ascii codes you don't want, just as RG was saying.

 

[aaalex]

Perfect! Thanks for the help. Also going to tell the person at the source of the data about the clean function--maybe I won't have to worry about it (I haven't found the forum to eliminate user errors yet).