Get CSV Data from Clipboard (pasted from Excel) that contains accented characters

Question

SCENARIO

• My users will copy cells from Excel (thus placing it into the clipboard)
• And my application will retrieve those cells from the clipboard

THE PROBLEM

• My code retrieves the CSV format from the clipboard
• However, the if the original Excel content contains characters like ä (a with umlaut) then retrieved CSV string doesn't have the correct characters (ä ends up showing as a "square" for me)
• In comparison, if my code retrieves the Unicode text format from the clipboard everything works fine: the ä is preserved in the string retrieved from the clipboard

SOURCE CODE - ORIGINAL - WITH THE PROBLEM

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    // read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = new System.Text.UTF8Encoding();
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv = reader.ReadToEnd();

    // read the unicode string
    string data_string = System.Windows.Forms.Clipboard.GetText();



}

THE RESULTS WHEN RUNNING THE SAMPLE CODE

• Repro steps: Enter some text in Excel (I used the word "doppelgänger" plus some numbers) and simply hit Ctrl-C to copy it to the clipboard and then run the code above.
• data_csv is set to "doppelg�nger,1\r\n2,3\r\n\0"
• data_string is set to "doppelgänger\t1\r\n2\t3\r\n"

QUESTION

• What can I do to get the correct characters?

COMMENTS

• Yes, I know I could workaround this problem by using the Unicode text. But I actually want to understand what is going on with the CSV
• using or not using the UTF-8 encoding when retrieving the stream makes no difference in the results

THE ANSWER

After looking at the comments, and paying close attention to what Excel was putting on the clipboard for CSV, it seemed reasonable that Excel might be placing the contents using an "legacy" encoding instead of UTF-8. So I tried the using the Windows 1252 codepage as the encoding and it worked. See the code below

SOURCE CODE - WITH THE ANSWER

[STAThread]
static void Main(string[] args)
{
    var fmt_csv = System.Windows.Forms.DataFormats.CommaSeparatedValue;

    //read the CSV
    var dataobject = System.Windows.Forms.Clipboard.GetDataObject();
    var stream = (System.IO.Stream)dataobject.GetData(fmt_csv);
    var enc = System.Text.Encoding.GetEncoding(1252);
    var reader = new System.IO.StreamReader(stream,enc);
    string data_csv= reader.ReadToEnd();

    //read the Unicode String
    string data_string = System.Windows.Forms.Clipboard.GetText();
}

Answer 1

Your encoding of the stream as UTF8 is not working. The bytes for the umlaut are being converted into the "replacement character" unicode character.

Instead, just look at the stream's data without any extra encoding instructions. The data will be in some set format used by Excel. You should be able to tell by looking at the byte(s) where the unlaut is. You should then be able to convert it to UTF-8.

Worst case is if the CSV Formatter throws out everything that is not Ascii. In that case, you might be able to write your own Data formatter.

In some cases, the Excel folks have figured that CSV means Ascii only. See http://www.tech-archive.net/Archive/Excel/microsoft.public.excel.misc/2008-07/msg02270.html

Answer 2

Excel stores the string on the clipboard using the Unicode character encoding. The reason you get a square when you try to read the string in ANSI is that there is no representation for that character in your system's ANSI codepage. You should just use Unicode. If you're going to be dealing with localization issues, then ANSI is just more trouble than it's worth.

Edit: Joel Spolsky wrote an excellent introduction to character encodings, which is definately worth checking out: The Absolute Minimum Every Software Developer Absolutely, Positively Must Know About Unicode and Character Sets (No Excuses!)