MS Excel: save spreadsheet to tab-separated with UNIX end of lines and UTF-8?

Question

I have a tab-separated text file with a table that has UNIX end-of-line characters (i.e. \n) and UTF-8 encoding. I can open it in MS Excel 2010 without major problems using Data → Import and selecting related options. However, if I save it back into a text file using File → Save as → Tab-separated, I end up with a file with DOS end-of-lines (i.e. \r\n) and one-byte national Windows encoding (i.e. "ANSI").

Of course, I can restore proper end-of-lines and encoding with an external tool, but that looks like a lot of hassle in the long term.

Is there a quick way to save tab-separated file with UNIX EOLs and in UTF8 (i.e. using all the settings that were used during the import) in MS Excel?

Answer 1

Short answer: no. Excel can't do that without using VBA.

About different encodings
Check this Stackoverflow question about the different encodings (UTF-8, UTF-16, Unicode, etc.)

What Excel can do
Microsoft Excel can export text files using ANSI (Windows-1252 or ISO-8859-1, which are quite similar) or using Unicode (which is the same as UTF-16). Both have a DOS-style end-of-line ([CR]+[LF] or '\r\n' or in ASCII HEX code: 0x0D0A).

What Excel can't do
Microsoft Excel can't export UTF-8 and can't use UNIX style newlines ([LF] or '\n' or in ASCII HEX code: 0x0D).

See this Microsoft Knowledge base article where this is confirmed. They revert to another tool (such as Notepad) to save the ANSI file to UTF-8.

For a VBA answer, see this solution.