23

I have been given an export from a MySQL database that seems to have had it's encoding muddled somewhat over time and contains a mix of HTML char codes such as & uuml; and more problematic characters representing the same letters such as ü and Ã. It is my task to to bring some consistency back to the file and get everything into the correct Latin characters, e.g. ú and ó.

An example of the sort of string I am dealing with is

Desinfektionslösungstücher für Flächen

Which should equate to

50 Tattoo Desinfektionsl ö    sungst ü    cher f ü    r Fl ä    chen 
50 Tattoo Desinfektionsl ö sungst ü cher f ü r Fl ä chen

Is there a method available in C#/.Net 4.5 that would successfully re-encode the likes of ü and à to UTF-8?

Else what approach would be advisable?

Also is the paragraph character in the above example string an actual paragraph character or part of some other character combination?

I have created a lookup table in the case of needing to do find and replace which is below, however I am unsure as to how complete it is.

É -> É
“ -> "
†-> "
Ç -> Ç
à -> Ã
é, 'é
à -> ú -> ú
• -> -
Ø -> Ø
õ -> õ
í -> í
â -> â
ã -> ã
ê -> ê
á -> á
é -> é
ó -> ó
– -> –
ç -> ç
ª -> ª
º -> º
à  -> à
Boann
  • 48,794
  • 16
  • 117
  • 146
Gga
  • 4,311
  • 14
  • 39
  • 74
  • 12
    Point of pedantry: `ü` and `Ã` are not "special characters" exactly, but **[Mojibake](https://en.wikipedia.org/wiki/Mojibake)**. – Boann Feb 20 '13 at 14:11
  • @Boann ped away... interesting – Gga Feb 20 '13 at 15:03
  • Btw your post is somewhat misleading, after repairing the data I got `Desinfektionslösungstücher für Flächen`, which seems to be correct but in your expected result you have spaces. – Esailija Feb 20 '13 at 16:59
  • @Esailija Yes put in the spaces, there just to illustrate what maps to what... – Gga Feb 21 '13 at 10:33
  • Useful info: In order to quickly debug this kind of issues you can use this website: https://2cyr.com/decode/?lang=en For this particular example, copy/paste the string in the question, then select UTF-8 as source and WINDOWS-1252 as displayed. Then click OK. Copy/paste resulting text to upper text box again and re-run with the same settings. You will see the original string. – Ahmet Jan 18 '21 at 10:47
  • Anyone knows if the table is complete? I think some characters are missing – George Chalhoub Dec 26 '21 at 21:34

5 Answers5

30

Well, first of all, as the data has been decoded using the wrong encoding, it's likely that some of the characters are impossible to recover. It looks like it's UTF-8 data that incorrectly decoded using an 8-bit encoding.

There is no built in method to recover data like this, because it's not something that you normally do. There is no reliable way to decode the data, because it's already broken.

What you can try, is to encode the data, and decode it using the wrong encoding again, just the other way around:

byte[] data = Encoding.Default.GetBytes(input);
string output = Encoding.UTF8.GetString(data);

The Encoding.Default uses the current ANSI encoding for your system. You can try some different encodings there and see which one gives the best result.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Thanks, I think your theory that the data may be irrecoverable could well be correct. I have broken the string down like so... 50 Tattoo Desinfektionsl ö sungst ü cher f ü r Fl ä chen --- and --- 50 Tattoo Desinfektionsl ö sungst ü cher f ü r Fl ä chen. so I know what should be appearing where but still cannot convert – Gga Feb 20 '13 at 13:10
  • 1
    Your code combined with the findings of @pawlakppp solved the issue so thanks to both of you. – Gga Feb 20 '13 at 14:05
  • 1
    Possibly the python 3 equivalent: `s.encode('raw_unicode_escape').decode('utf8')` – matanster Dec 14 '18 at 17:08
17

The data is only partly unrecoverable due to Windows-1252 encoding having 5 unassigned slots. Some modifications of Windows-1252 fill these with control characters but those don't make it to posts in Stackoverflow. If modified Windows-1252 has been used you can fully recover as long as you don't lose the hidden control characters in copy pastes.

There is also the non-breaking space character that is ignored or turned into a space usually with copypastes, but that's not an issue when you deal with bytes directly.

The misencoding abuse this string has gone through is:

UTF-8 -> Windows-1252 -> UTF-8 -> Windows-1252

To recover, here is an example:

String a = "Desinfektionslösungstücher für Flächen";
Encoding utf8 = Encoding.GetEncoding(65001);
Encoding win1252 = Encoding.GetEncoding(1252);

string result = utf8.GetString(win1252.GetBytes(utf8.GetString(win1252.GetBytes(a))));

Console.WriteLine(result);
//Desinfektionslösungstücher für Flächen
Esailija
  • 138,174
  • 23
  • 272
  • 326
5

It's probably windows-1252 encoded string which you read as UTF-8.

As Guffa mentioned data has been corrupted.

Lets take a look on bytes:
ö -> C3B6 in UTF8

in windows-1252 C3 ->Ã B6 ->¶

so ö ->ö

what about all these "ƒÂ":

ƒ ->83 Â ->C2

Honesty i don't know why they appear, but you can try erase them and do some conversions as Guffa mentioned. Good luck

el vis
  • 1,302
  • 2
  • 16
  • 32
  • Thanks, I am following the same lines of investigation myself and have removed "ƒÂ". A reexport of the data has removed them and turned the A-hats to A-tildes which is good, then there seems to be a clear conversion as laid out here: http://www.i18nqa.com/debug/utf8-debug.html – Gga Feb 20 '13 at 14:02
2

Here you can find a completer list:

http://bueltge.de/wp-content/download/wk/utf-8_kodierungen.pdf

Jorden van Foreest
  • 645
  • 5
  • 18
1

I've been troubled by this char problem before. Solution:

My .(cs)html file was UTF-8; I converted to UTF-8Y (UTF-8 with a BOM).

codeMonkey
  • 4,134
  • 2
  • 31
  • 50
Alhan Ozdemir
  • 21
  • 1