I think I have a character encoding problem in a csv download that will be opened in Excel. Looks fine on screen, download looks fine in Notepad++, but in Excel, some data looks like the attached screenshot. The mistaken character is some sort of hyphen. The download doesn't encode or transform the data at all, at least not intentionally. It's done like this:

<cfheader name="content-disposition" value="attachment; filename="...

Default charset for cfheader is UTF-8, which I thought should be fine, apparently not. I also tried explicitly setting it to UTF-8, and also to iso-8859-1 and windows-1252, same result. Thoughts?

Any Chance it is MSs Funky SMART quotations? Where instead of using STRAIGHT Quotes , ' " '.... you're getting the curly /fancy ones? Another thought springs to mind... have you checked the EOL that is used in the file?

It probably is an MS funky character, some sort of overeducated hyphen in this case, entered into the app by a user. Question is, if it renders OK on the app page without any preprocessing, and the csv file itself seems ok in Notepad++, why is it wrong in Excel? And more to the point, how can I keep that from happening?

Perhaps notepad is lying to you7? Can you open it using linux, just to rule out an MS-ism?

It's ok on a cf web page too, rendered with the same code used for the download version. Only difference is the <cfcontent> stuff used for the download. Notepad++ shows it correctly with its default encoding of UTF8, but if I choose Encoding > ANSI, it shows the same incorrect version of that hyphen. Wordpad shows the same incorrect version as that and Excel. I can get Excel to read it correctly if instead of double-clicking the file or using the normal Open cmd, I choose Data > From Text and scroll way down in the list of encodings to UTF8. So it appears that the problem is actually how Excel and Wordpad read the file, not its actual contents. Thing is, my users won't know any of that, they'll just double-click and see spooge. BUT, it turns out that if I write a BOM into the file before writing the rest of it, Excel opens it correctly. That's chr(65279) FYI. I'm more than a little tempted to unfancy those hyphens in the db, just to simplify life. Turns out they're not in user-entered data, but in lookup data entered or imported by our devs, back in 2014.

I am as stumped as you as to a why it is different, it should just work. in a test DB of course 😉 can you change of the entries in the db - by overwriting the hyphen with a new hyphen. and see if you just have peculiar data IN the dtatbase:? Also can you check the character encoding for the t DB and the table, too.Lastly I would check the region/character set etc of the DB server and the CFML server too. Just to rulw them out. In one of our databases (In PROD) we somehow have Swedish collation! we started out as just english amd now have oter lanuagess we support - but swedish is not one of them!

Hah, everything you don't look at is weird 🙂 Table collation is SQL_Latin1_General_CP1_CI_AS, same as the db. I don't think that's the problem, since cf can display it just fine. Even somewhat backwards Excel can too if there's a BOM in the file, which is easy enough to do. IOW, the data itself is ok.

how are you generating the csv?

CF code that just uses writeOutput(). I have a generic query rendering CFC that's used here, for both the screen and download versions. Its download() method now takes a writeBOM argument, which defaults to true.