How to deal with unicode / special / invisible characters?

Hi there,

once a while I happen to encounter a very nasty data issue, caused by copy & paste i.e. form Excel (iso-1252?) to a web editor (utf-8). Issues can manifest in corrupt data being saved (broken CSV) to processing in Knime coming to a complete halt.

In this particular case it’s an Zero Width Space Unicode Character

<0x200b>

which only becomes visible when copy & pasting the cell at fault into an editor like sublime. Though, you must be very lucky picking the exact cell or tediously reduce the data set to gradually narrow it down.

The question is, how would you deal with that issue? I even struggle to properly phrase it. Is it an encoding discrepancy or something entirely else? Thanks for your feedback!

Best

Mike