CSV encoding issues (Microsoft Excel)

Question

I am dynamically creating CSV files using C#, and I am encountering some strange encoding issues. I currently use the ASCII encoding, which works fine in Excel 2010, which I use at home and on my work machine. However, the customer uses Excel 2007, and for them there are some strange formatting issues, namely that the '£' sign (UK pound sign) is preceded with an accented 'A' character.

What encoding should I use? The annoying thing is that I can hardly test these fixes as I don't have access to Excel 2007!

Solution 1

I'm using Windows ANSI codepage 1252 without any problems on Excel 2003. I explicitly changed to this because of the same issue you are seeing.

private const int WIN_1252_CP = 1252; // Windows ANSI codepage 1252

this._writer = new StreamWriter(fileName, false, Encoding.GetEncoding(WIN_1252_CP));

Solution 2

I've successfully used UTF8 encoding when writing CSV files intended to work with Excel.

The only problem I had was making sure to use the overload of the StreamWriter constructor that takes an encoding as a parameter. The default encoding of StreamWriter says it is UTF8 but it's really UTF8-Without-A-Byte-Order-Mark and without a BOM Excel will mess up characters using multiple bytes.

Comments

[shf301]

I'd recommend using Encoding.Default instead. That will always get the systems default ANSI code page rather than hard coding it to 1252.

[Chris]

This seems to have worked - I have forwarded the CSV to the customer and they can confirm it works. Thanks!

[amelian]

Very very useful, this has solved my Encoding problems in a CVS file that I was generating. Regards

[Andy]

@AMGHoshi Glad it helped.