I'm developing a part of an application that's responsible for exporting some data into CSV files. The application always uses UTF-8 because of its multilingual nature at all levels. But opening such CSV files (containing e.g. diacritics, cyrillic letters, Greek letters) in Excel does not achieve the expected results showing something like Г„/Г¤, Г–/Г¶. And I don't know how to force Excel understand that the open CSV file is encoded in UTF-8. I also tried specifying UTF-8 BOM EF BB BF, but Excel ignores that.

有什么解决办法吗?

附注:哪些工具可能像Excel一样?


更新

I have to say that I've confused the community with the formulation of the question. When I was asking this question, I asked for a way of opening a UTF-8 CSV file in Excel without any problems for a user, in a fluent and transparent way. However, I used a wrong formulation asking for doing it automatically. That is very confusing and it clashes with VBA macro automation. There are two answers for this questions that I appreciate the most: the very first answer by Alex https://stackoverflow.com/a/6002338/166589, and I've accepted this answer; and the second one by Mark https://stackoverflow.com/a/6488070/166589 that have appeared a little later. From the usability point of view, Excel seemed to have lack of a good user-friendly UTF-8 CSV support, so I consider both answers are correct, and I have accepted Alex's answer first because it really stated that Excel was not able to do that transparently. That is what I confused with automatically here. Mark's answer promotes a more complicated way for more advanced users to achieve the expected result. Both answers are great, but Alex's one fits my not clearly specified question a little better.


更新2

在最后一次编辑5个月后,我注意到Alex的答案不知为何消失了。我真的希望这不是一个技术问题,我希望现在不再有关于哪个答案更好的讨论。所以我认为马克的答案是最好的。


当前回答

只是为了帮助有兴趣在Excel上打开文件实现这个线程的用户。

我使用了下面的向导,它对我来说工作得很好,导入了一个UTF-8文件。 不是透明的,但如果您已经有了该文件,则非常有用。

Open Microsoft Excel 2007. Click on the Data menu bar option. Click on the From Text icon. Navigate to the location of the file that you want to import. Click on the filename and then click on the Import button. The Text Import Wizard - Step 1 or 3 window will now appear on the screen. Choose the file type that best describes your data - Delimited or Fixed Width. Choose 65001: Unicode (UTF-8) from the drop-down list that appears next to File origin. Click on the Next button to display the Text Import Wizard - Step 2 or 3 window. Place a checkmark next to the delimiter that was used in the file you wish to import into Microsoft Excel 2007. The Data preview window will show you how your data will appear based on the delimiter that you chose. Click on the Next button to display the Text Import Wizard - Step 3 of 3. Choose the appropriate data format for each column of data that you want to import. You also have the option to not import one or more columns of data if you want. Click on the Finish button to finish importing your data into Microsoft Excel 2007.

来源:https://www.itg.ias.edu/content/how-import-csv-file-uses-utf-8-character-encoding-0

其他回答

我尝试了我能在这个帖子上找到的一切,类似的,没有什么是完全有效的。然而,导入到谷歌表和简单地下载为csv工作就像一个魅力。如果你到了我的挫败点,可以试试。

是的,这是可能的。正如之前多个用户所指出的,当文件以UTF-8编码时,excel读取正确的字节顺序标记似乎存在问题。对于UTF-16,它似乎没有问题,所以它是UTF-8特有的。我为此使用的解决方案是添加BOM,两次。为此,我执行了两次下面的sed命令:

sed -I '1s/^/\xef\xbb\xbf/' *.csv

,其中通配符可以替换为任何文件名。然而,这会导致.csv文件开头的sep=发生突变。然后,.csv文件将在excel中正常打开,但在第一个单元格中有一个带有“sep=”的额外行。 "sep="也可以在源文件的.csv中删除,但是当用VBA打开文件时,应该指定分隔符:

Workbooks.Open(name, Format:=6, Delimiter:=";", Local:=True)

格式6是.csv格式。将Local设置为true,以防文件中有日期。如果Local未设置为true,日期将被美国化,这在某些情况下会破坏.csv格式。

我正在从一个简单的c#应用程序生成csv文件,也遇到了同样的问题。我的解决方案是确保文件是用UTF8编码编写的,如下所示:

// Use UTF8 encoding so that Excel is ok with accents and such.
using (StreamWriter writer = new StreamWriter(path, false, Encoding.UTF8))
{
    SaveCSV(writer);
}

我最初有以下代码,其中口音在notepad++中看起来很好,但在Excel中被破坏:

using (StreamWriter writer = new StreamWriter(path))
{
    SaveCSV(writer);
}

你的里程可能会有所不同——我使用的是。net 4和Office 365中的Excel。

我过去也遇到过同样的问题(如何生成Excel可以读取的文件,以及其他工具也可以读取的文件)。我使用的是TSV而不是CSV,但同样的编码问题出现了。

我没能找到任何方法让Excel自动识别UTF-8,我也不愿意/不能给文件的使用者复杂的如何打开它们的指令。所以我将它们编码为UTF-16le(带有BOM)而不是UTF-8。大小是原来的两倍,但Excel可以识别编码。而且它们的压缩性很好,所以尺寸很少(但遗憾的是并非永远)重要。

现在是2022年3月,似乎我们不能同时使用BOM和sep=…线。 添加sep=\t或类似的,使Excel忽略BOM。

使用分号似乎是Excel的默认理解,在这种情况下,我们可以跳过sep=…这样就行了。

这是微软365与Excel版本2110构建14527.20276。