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!

Please show some code esp. regarding the encoding since the accented 'A' hints that there might somewhere Unicode (where this is used in an escape-like manner for certain cases like characters > 128)... — Yahia
– Yahia, Commented Aug 22, 2011 at 17:31

Andy · Accepted Answer · 2011-08-22 17:34:14Z

14

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));

answered Aug 22, 2011 at 17:34

Andy

5,28810 gold badges45 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

shf301 Over a year ago

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 Over a year ago

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

amelian Over a year ago

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

Fredy Over a year ago

Thanks a lot, in my case I had to change File.WriteAllLines(path, lines) to File.WriteAllLines(path, lines, System.Text.Encoding.UTF8) to get rid of these nasty chars.

Andrew Kennan · Accepted Answer · 2011-08-23 00:29:21Z

4

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.

answered Aug 23, 2011 at 0:29

Andrew Kennan

14.2k3 gold badges27 silver badges33 bronze badges

Comments

razon · Accepted Answer · 2017-09-06 13:25:12Z

1

You need to add Preamble to file:

        var data = Encoding.UTF8.GetBytes(csv);
        var result = Encoding.UTF8.GetPreamble().Concat(data).ToArray();
        return File(new MemoryStream(result), "application/octet-stream", "file.csv");

answered Sep 6, 2017 at 13:25

razon

4,1103 gold badges36 silver badges48 bronze badges

Collectives™ on Stack Overflow

CSV encoding issues (Microsoft Excel)

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related