Removing Non printable characters (1 Viewer)

[GSTEEL320]

I've come across these for the first time recently. It would appear I import an excel sheet with several of these type of characters, which is causing issues when joining the data within that particular field.

I have seen a few vba solutions, which can be hit and miss if the characters are not always the same type.

So my question is this.... Will converting the file to a text file before import, remove these characters?

I'm considering writing vba to do this anyway, but I'm always open to a second opinion ......

 

[arnelgp]

you should try and test.
do you work on mixed english and non-english characters?

 

[June7]

I doubt characters will be removed. Or if characters can't be understood by text file will probably be substituted.

 

[sxschech]

Something to try, if the file is text format such as csv, you can import with UTF-8. Use the wizard to set this up. When the Import Text Wizard pops up, Click on Advanced, then Click on Code Page and choose UTF-8, then you can save the spec and use it to upload the file in the future.

 

[GSTEEL320]

That's great, thanks very much. I'll test this and let you know. TVM for all the advice.

 

[CJ_London]

I have had the situation where spreadsheets are completed around the world and sent in for import to the db. Among other things the db had to regularise the character set for upload to a corporate scale system which would not accept non printing characters or non 'latin' characters.

The way this was handled was to create a translation table to convert characters to something else - might be a ZLS, a space or a latin equivalent to the non latin character.

A vba function would accept a string and then parse through the translation table and using the replace function replace characters as required and then return the updated string. Ultimately it was extended to also try to regularise the language by replacing abbreviations.

The function was something like this - free typed so may be errors

function translateStr(byVal s as string) as string
static trst as dao.recordset

    'get translation records if not already loaded
    if trst is nothing then
        set trst=currentdb.openrecordset("SELECT * FROM tblTranslations")
    end if
   
    with trst
        .movefirst
        while not .eof
            s=replace(s,!phrase,!alternative)
            .movenext
        wend
    end with
   
    translateStr=S

end function

edit you can find the ascii codes for non printing characters using code like this

for i=1 to len s
debug.print asc(mid(s,i,1))
next i