Change Reports Save as CSV/Txt/Delimited to UTF8 on all platforms to pre-end a BOM to allow double-click to open properly in Excel

Stuart Beesley (Mr Toolbox)'s Avatar

Stuart Beesley (Mr Toolbox)

10 Nov, 2020 06:29 PM

A suggestion. I’ve just been diagnosing an oddity. When I create a pure UTF-8 CSV file in my Python scripts, double-click to open in excel doesn’t work well as Excel tries to open it as cp1252 format file. But when using Excel File>New Data>From Text and you choose UTF-8 it works. This is important particularly with currency symbols as anything other than $ gets garbled. It seems this is Excel’s fault (i.e. Mac Numbers is OK)…. BUT there is a solution. If you add BOM (Byte Order Mark) of (hexadecimal) byte sequence 0xEF,0xBB,0xBF to the beginning of the file before anything else, then Excel will actually read this and on double-click and read it as UTF-8 and symbols will all be OK. It seems this a valid thing to do, and it helps Excel.

So why am I telling you this? If you run a report in MD and click to save as CSV (etc) format file, then double-click to open on a Mac in Excel gives you the same garbled characters. It’s a real pain, and I’ve only just learned the possible solution. So….. if you pre-pended this same byte sequence and forced a UTF-8 format file when creating CSV, TXT files then this would be great. The other oddity is that on Windows, Moneydance seems to actually be creating a cp1252 format file (not a UTF-8 file). So whilst double click works for them, I feel that a UTF-8 file in all situations would be more compatible. I took a peek at the MD code and I can’t actually see why it creates a cp1252 format file so I suspect it’s using the computer’s default format for Windows… Thoughts?

  1. System closed this discussion on 09 Feb, 2021 06:30 PM.

Comments are currently closed for this discussion. You can start a new one.

Keyboard shortcuts


? Show this help
ESC Blurs the current field

Comment Form

r Focus the comment reply box
^ + ↩ Submit the comment

You can use Command ⌘ instead of Control ^ on Mac