哪种编码方式可以在Mac和Windows上用Excel正确打开CSV文件？

Question

我们有一个Web应用程序，可以导出包含UTF-8编码的外语字符的CSV文件，没有BOM。Windows和Mac用户在Excel中得到了垃圾字符。我尝试将其转换为带BOM的UTF-8；Excel/Win可以正常使用，但Excel/Mac则显示乱码。我正在使用Excel 2003/Win、Excel 2011/Mac。这是我尝试过的所有编码：

Encoding  BOM      Win                            Mac
--------  ---      ----------------------------   ------------
utf-8     --       scrambled                      scrambled
utf-8     BOM      WORKS                          scrambled
utf-16    --       file not recognized            file not recognized
utf-16    BOM      file not recognized            Chinese gibberish
utf-16LE  --       file not recognized            file not recognized
utf-16LE  BOM      characters OK,                 same as Win
                   row data all in first field

最好的编码方式是带BOM的UTF-16LE，但CSV不会识别它。字段分隔符是逗号，但分号也不影响。

是否有一种编码方式适用于这两个世界？

- Timm

---

如果您将UTF-16LE用于所有字段数据，但在逗号处使用8位/ASCII字符，会发生什么情况？根据这篇文章（http://creativyst.com/Doc/Articles/CSV/CSV01.htm#CSVAndEncodings），Excel可能会将Unicode逗号解释为字段数据的一部分，而不是分隔符。 - jveazey

有趣的提示@jveazey。我会在我的导出代码中尝试这个：mb_convert_encoding($str, "UTF-16LE");，并在这里发布结果。 - Timm

虽然这对你没有帮助，但我在Windows上测试了Excel 2007 SP2（使用Notepad2创建测试文件）。除了UTF-16LE BOM（与你在Windows上的结果相同）和UTF-16BE BOM（解析字段正确，但BOM作为A1中的前两个字符包含在内）之外，一切都正常运行。 - jveazey

@hveazey，引用的问题有一个答案，creechy推荐使用代码页Windows-1252。但对于我的情况（德语umlauts和sharp s），这并没有起作用。 - Timm

@notJim，stackoverflow.com/questions/4348802上有Jasdeep Gosal的答案，据说适用于Mac和PC；我还没有尝试过。他提供了PHPExcel（对我来说需要太多内存）和TSV解决方案，但我需要CSV。 - Timm

Answer 1

Excel编码

在处理Excel文件时，我发现WINDOWS-1252编码是最少出错的。由于它基本上是微软自己专有的字符集，因此可以假设它将适用于MS-Excel的Mac和Windows版本。这两个版本至少都包含相应的“文件来源”或“文件编码”选择器，可以正确读取数据。

根据您的系统和使用的工具，该编码也可以被命名为CP1252、ANSI、Windows (ANSI)、MS-ANSI或仅仅是Windows等其他变体。

该编码是ISO-8859-1（也称为LATIN1和其他名称）的超集，因此如果由于某些原因无法使用WINDOWS-1252，则可以回退到ISO-8859-1。请注意，ISO-8859-1缺少一些来自WINDOWS-1252的字符，如下所示：

| Char | ANSI | Unicode | ANSI Hex | Unicode Hex | HTML entity | Unicode Name                               | Unicode Range            |
| €    | 128  | 8364    | 0x80     | U+20AC      | €      | euro sign                                  | Currency Symbols         |
| ‚    | 130  | 8218    | 0x82     | U+201A      | ‚     | single low-9 quotation mark                | General Punctuation      |
| ƒ    | 131  | 402     | 0x83     | U+0192      | ƒ      | Latin small letter f with hook             | Latin Extended-B         |
| „    | 132  | 8222    | 0x84     | U+201E      | „     | double low-9 quotation mark                | General Punctuation      |
| …    | 133  | 8230    | 0x85     | U+2026      | …    | horizontal ellipsis                        | General Punctuation      |
| †    | 134  | 8224    | 0x86     | U+2020      | †    | dagger                                     | General Punctuation      |
| ‡    | 135  | 8225    | 0x87     | U+2021      | ‡    | double dagger                              | General Punctuation      |
| ˆ    | 136  | 710     | 0x88     | U+02C6      | ˆ      | modifier letter circumflex accent          | Spacing Modifier Letters |
| ‰    | 137  | 8240    | 0x89     | U+2030      | ‰    | per mille sign                             | General Punctuation      |
| Š    | 138  | 352     | 0x8A     | U+0160      | Š    | Latin capital letter S with caron          | Latin Extended-A         |
| ‹    | 139  | 8249    | 0x8B     | U+2039      | ‹    | single left-pointing angle quotation mark  | General Punctuation      |
| Π   | 140  | 338     | 0x8C     | U+0152      | Π    | Latin capital ligature OE                  | Latin Extended-A         |
| Ž    | 142  | 381     | 0x8E     | U+017D      |             | Latin capital letter Z with caron          | Latin Extended-A         |
| ‘    | 145  | 8216    | 0x91     | U+2018      | ‘     | left single quotation mark                 | General Punctuation      |
| ’    | 146  | 8217    | 0x92     | U+2019      | ’     | right single quotation mark                | General Punctuation      |
| “    | 147  | 8220    | 0x93     | U+201C      | “     | left double quotation mark                 | General Punctuation      |
| ”    | 148  | 8221    | 0x94     | U+201D      | ”     | right double quotation mark                | General Punctuation      |
| •    | 149  | 8226    | 0x95     | U+2022      | •      | bullet                                     | General Punctuation      |
| –    | 150  | 8211    | 0x96     | U+2013      | –     | en dash                                    | General Punctuation      |
| —    | 151  | 8212    | 0x97     | U+2014      | —     | em dash                                    | General Punctuation      |
| ˜    | 152  | 732     | 0x98     | U+02DC      | ˜     | small tilde                                | Spacing Modifier Letters |
| ™    | 153  | 8482    | 0x99     | U+2122      | ™     | trade mark sign                            | Letterlike Symbols       |
| š    | 154  | 353     | 0x9A     | U+0161      | š    | Latin small letter s with caron            | Latin Extended-A         |
| ›    | 155  | 8250    | 0x9B     | U+203A      | ›    | single right-pointing angle quotation mark | General Punctuation      |
| œ    | 156  | 339     | 0x9C     | U+0153      | œ     | Latin small ligature oe                    | Latin Extended-A         |
| ž    | 158  | 382     | 0x9E     | U+017E      |             | Latin small letter z with caron            | Latin Extended-A         |
| Ÿ    | 159  | 376     | 0x9F     | U+0178      | Ÿ      | Latin capital letter Y with diaeresis      | Latin Extended-A         |

请注意，欧元符号丢失了。 此表格可在Alan Wood找到。

转换

每种工具和语言的转换方式都不同。然而，假设您有一个名为query_result.csv的文件，您知道它是以UTF-8编码的。使用iconv将其转换为WINDOWS-1252：

iconv -f UTF-8 -t WINDOWS-1252 query_result.csv > query_result-win.csv

- mikezter

---

虽然有点麻烦，但这似乎是将带有欧洲字符的 .csv 文件导入 Mac OSX 上的 Excel 的答案。 - Fergie

True。它回答了OP的问题。在您的情况下，您首先必须知道（或猜测）在您的“带有欧洲字符的.csv文件”中使用的编码。然后，您可以将其转换为WINDOS-1252，这很可能会被Mac和Windows Excel正确解释。 - mikezter

这不是真正的解决方案，迟早会遇到无法转换为WINDOWS-1252的字符。 - Walter Tross

如果出现中文字符，WINDOWS-1252将无法正常工作。因此，似乎UTF-16LE带BOM是唯一的选择。 - XWang

这对于带有变音符号的SQL数据导出非常有效。 - motorbaby

Answer 2

如果你在UTF-16LE文件头部加上BOM，并且使用tab分隔符而不是逗号分隔符，Excel将能够识别字段。原因是Excel实际上使用了Unicode *.txt解析器。

注意：如果在Excel中编辑并保存该文件，则会以制表符分隔的ASCII文件格式进行保存。现在的问题是，当你重新打开该文件时，Excel认为它是真正的CSV文件（使用逗号分隔符），看到它不是Unicode编码，因此将按逗号分隔解析 - 这将导致错误。

更新：至少在Windows Excel 2010中，今天我没有遇到上述警告，尽管保存行为存在差异：

• 你编辑并退出Excel（尝试保存为'Unicode*.txt'）

相比之下:

• 只编辑并关闭文件（如预期工作）。

- Duncan Smart

---

不错，但是这个警告对我来说破坏了解决方案；最终用户不会满意出现损坏的Excel表格。 - Timm

如果您将初始文件扩展名更改为*.txt，则可能会起作用，但是这样一来，文件类型与Excel之间的关联就会丢失：即他们无法双击文件并自动在Excel中打开它。 - Duncan Smart

这对我行不通。作为不精通计算机的最终用户，需要在Excel中打开它而没有任何障碍。 - Timm

我的“.csv” Excel表格看起来很好，包含特殊字符和分隔字段。我使用字节顺序标记（BOM）"\ufeff"开始输出字符串，然后使用"\t"制表符代替逗号进行字段分隔，并使用“utf-16LE”编码文件。多亏了这个页面，一切都运作得很好！ - Geek Stocks

"sep=," 和 UTF16LE 编码对我来说有效，并且不需要不同的分隔符 (逗号仍然有效)。 双击打开文件可正确加载文件，单元格内的特殊字符和换行符也保留完整。 不足之处：据我所见，除了 Excel 之外，没有任何程序可以识别 "sep=," 标头。但是， OpenOffice / LibreOffice 无论如何都不需要这个 hack（单元格内容中的换行符可以正常工作），而从文本文件加载 / 在 Excel 中使用文本到列助手时，不能正确处理单元格内的换行符。" - CodeManX

Answer 3

简而言之：没有解决方案。无论您使用什么编码或方法，Excel 2011/Mac 都无法正确解释包含变音符号和重音符号的 CSV 文件。我很乐意听到有人告诉我不同的看法！

- Timm

---

我发现WIN-1252或ISO-8859-1编码可行。请查看我的回答。 - mikezter

解决方案是使用UTF-16LE，并确保您使用制表符分隔列而不是逗号。 - Tim Groeneveld

你真的在Win和Mac上尝试过这个吗，Tim？正如我所提到的，TSV在我的情况下不是一个选项。 - Timm

对我来说，如果我使用WIN-1252编码，在Mac和Windows版本的Excel上导出工作正常。@Timm，你应该考虑更改接受的答案。 - Pierre Arnaud

对于那些发现这个工作的人，你们的数据集中是否实际包含扩展（如中文）字符？WIN-1252编码会因为超出范围而无法处理它们。 - Bill Leeper

WIN-1252在Excel 2010中无法导出“Curaçao”。 - QuestionC

Answer 4

您只尝试了逗号分隔和分号分隔的CSV。 如果您尝试了制表符分隔的CSV（也称为TSV），您会找到答案：使用带BOM（字节顺序标记）的UTF-16LE，制表符分隔。

但是: 在您的评论中提到TSV不适用于您（尽管我在您的问题中没有找到此要求）。 这很遗憾。这通常意味着您允许手动编辑TSV文件，这可能不是一个好主意。 TSV文件的可视检查不是问题。此外，编辑器可以设置显示特殊字符以标记制表符。

是的，我在Windows和Mac上尝试过这个方法。

- Walter Tross

Answer 5

关于在Mac的Excel 2011中导入UTF8编码CSV的关键点是：微软表示：“Excel for Mac目前不支持UTF-8编码。”Excel for Mac 2011 and UTF-8

太好了，干得漂亮，微软！

- Timm

Answer 6

在Mac上读取UTF-8格式的CSV文件的最佳解决方案是将它们转换为XLSX格式。我发现了Konrad Foerstner制作的脚本，稍微改进了一下，增加了对不同分隔符字符的支持。

从Github下载该脚本：https://github.com/brablc/clit/blob/master/csv2xlsx.py。为了运行它，你需要安装一个用于Excel文件操作的python模块openpyxl：sudo easy_install openpyxl。

- brablc

Answer 7

在我的情况下，这个方法可行（Mac，Excel 2011，包括捷克变音符的西里尔和拉丁字符）：

• 字符集为UTF-16LE（仅使用UTF-16不够）
• BOM为"\xFF\xFE"
• 使用制表符作为分隔符
• 不要忘记对分隔符和CRLFs进行编码 :-)
• 使用iconv而不是mb_convert_encoding

- Marek Demčák

Answer 8

根据我的案例，Excel 2011 for Mac OS似乎没有像我想象的那样使用Encoding.GetEncoding("10000")，我浪费了两天时间，但与Microsoft OS上相同的iso一样。最好的证明是在Excel 2011 for MAC上制作一个带有特殊字符的文件，将其保存为CSV，然后在MAC文本编辑器中打开它，字符会变成乱码。

对于我来说，这种方法有效-这意味着在MAC OS上的Excel 2011的csv导出中有特殊的西欧字符。

Encoding isoMacOS = Encoding.GetEncoding("iso-8859-1");
Encoding defaultEncoding = Encoding.Default; 

// Convert the string into a byte array.
byte[] defaultEncodingBytes = defaultEncoding.GetBytes(exportText);

// Perform the conversion from one encoding to the other.
byte[] ansiBytes = Encoding.Convert(defaultEncoding, isoMacOS, defaultEncodingBytes);

decodedString = isoMacOS.GetString(ansiBytes);

- user525081

---

你在使用什么编程语言@user525081？ 能将它翻译成PHP吗？ - Timm

@Timm 看起来这是一个Java示例，但在PHP中你可以使用iconv来进行转换 - http://de3.php.net/manual/en/function.iconv.php - Ashish Datta

好的@user525081，和其他答案一样。这适用于Mac用户，让Windows用户感到困扰；而且它并没有回答原来的问题 - 一个在两个平台上都能工作的编码方式。谢谢。 - Timm

Answer 9

在我的Mac OS上，Text Wrangler将使用Excel创建的CSV文件识别为“西方”编码。

经过一些搜索，我写了这个小脚本（我不确定Windows是否可用，也许需要使用Cygwin？）：

$ cat /usr/local/bin/utf8.sh

#!/bin/bash

INPUTFILE="$1"

iconv -f macroman -c -t UTF-8 $INPUTFILE |tr '\r' '\n' >/tmp/file.$$.csv

mv $INPUTFILE ms_trash
mv /tmp/file.$$.csv $INPUTFILE

- Nuno

---

这是我在OS X 10.14.2（和Excel 2011）上唯一有效的方法。 - Donald

Answer 10

以下方法适用于我在Excel for Mac 2011和Windows Excel 2002上的操作：

1. 在Mac上使用iconv，将文件转换为UTF-16 Little-Endian格式并将其命名为*.txt（.txt扩展名强制Excel运行文本导入向导）：

   iconv -f UTF-8 -t UTF-16LE filename.csv >filename_UTF-16LE.csv.txt

2. 在Excel中打开文件，在文本导入向导中选择：

   • 第1步：文件来源：忽略它，选择任意一个都可以
   • 第2步：选择合适的分隔符和文本限定符
   • 第3步：如果需要，选择列格式

PS iconv创建的UTF-16LE带有BOM字节FF FE。

PPS 我的原始csv文件是在Windows 7计算机上以UTF-8格式（带有BOM字节EF BB BF）创建的，并使用CRLF换行符。逗号被用作字段分隔符，单引号被用作文本限定符。其中包含ASCII字母以及不同的带有波浪线、umlaut等的拉丁字母，还有一些西里尔字母。在Excel for Win和Mac中都正常显示。

PPPS 精确的软件版本：
* Mac OS X 10.6.8
* Excel for Mac 2011 v.14.1.3
* Windows Server 2003 SP2
* Windows Excel 2002 v.10.2701.2625

- Koit Saarevet

---

如果您有一个没有BOM的UTF-8文件，iconv将把它转换为没有BOM的UTF-16LE（不幸的是，无法告诉iconv添加BOM）。 - Walter Tross