Weird White Space Character From Excel File (1 Viewer)

[sneuberg]

We get data from a linked Excel Spreadsheet and I found what appeared to be a space at the end of a text field in one of the records. Since that wouldn't match the existing data in our database I applied the Trim function to the field to remove the space. That didn't work. So to see what I was dealing with I put

RM: Asc(Right([Tracker].[PromoDesc],1))

in the query. The result for the this for the record in question was 160. That's outside of the range of ASCII characters. Does anyone know what this is and have suggestions on how get rid of it?

Note the character appears as white space in the spreadsheet too.

 

[ByteMyzer]

Instead of:

Trim([Tracker].[PromoDesc])

...you might try:

Trim(Replace([Tracker].[PromoDesc], Chr(160), " ", 1, -1, 1))

 

[spudchick]

It's a nonbreaking space. If it's just an import problem and they aren't used in the body of descriptions instead of regular chr(32) spaces, you could just use the replace function, but it might be easier and safer to replace just that character.

I'd probably do this if it were me:

update tracker set promodesc=rtrim(replace(promodesc,chr(160)," "))

That way the string in question only has normal spaces in your data.

 

[pbaldy]

Looks like 160 is a non-breaking space:

http://www.danshort.com/ASCIImap/

https://en.wikipedia.org/wiki/Non-breaking_space

No idea how it gets in there offhand. If you're importing it, you could run some function to trim it off. Left(Len()-1) if they all have it, or a custom function.

 

[sxschech]

Instead of trim, what if you try the replace function now that you have identified the character. Since I'm importing data that often has non standard characters, ended up building a function and doing replaces with it. Each time a new character was identified, it was added to the code. It would be great to have it automated, but haven't gone to that level since some non standard chars are acceptable and others like the one you encountered aren't. Here is a list of ascii codes. Looks like 160 is a acute. However other sites say non breaking space as pbaldy pointed out.


http://www.theasciicode.com.ar/exte...ase-letter-a-acute-accent-ascii-code-160.html

 

[sneuberg]

Thank you all for your replies. I think I will try

Trim(Replace([Tracker].[PromoDesc], Chr(160), " ", 1, -1, 1))

I'd rather do

update tracker set promodesc=rtrim(replace(promodesc,chr(160)," "))

But up to this point we haven't been modifying the customers spreadsheets, just using them as input. I feel we'd need their permission and getting any kind of response from them is difficult.

 

[gemma-the-husky]

non-breaking space. lots of google items about removing them.
They can be keyed in directly as Alt 0160

maybe they get into the spreadsheet from some other process.