是否可以强制Excel自动识别UTF-8 CSV文件？ [英] Is it possible to force Excel recognize UTF-8 CSV files automatically?

问题描述

我正在开发负责将某些数据导出为CSV文件的应用程序的一部分。应用程序总是使用UTF-8，因为它在各个层面都具有多种语言的性质。但是，在Excel中打开这样的CSV文件（包含例如变音符号，西里尔字母，希腊字母）不能达到预期的结果，如Г/Г¤，Г-/¶¶/ / code >。而且我不知道如何强制Excel了解开放的CSV文件是以UTF-8编码的。我也尝试指定UTF-8 BOM EF BB BF ，但Excel忽略它。

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.

有没有解决方法？

PS哪些工具可能会像Excel这样做？

P.S. Which tools may potentially behave like Excel does?

更新

我不得不说，我混淆了社区的问题。当我提出这个问题时，我要求以一种流利和透明的方式在Excel中打开UTF-8 CSV文件，而不会对用户造成任何问题。但是，我使用错误的表述要求自动执行。这是非常混乱，它与VBA宏自动化冲突。我最感激的问题有两个答案：Alex https://stackoverflow.com/a/6002338的第一个答案/ 166589 ，我已经接受了这个答案;第二个由稍后出现的Mark https://stackoverflow.com/a/6488070/166589 发布。从可用性的角度来看，Excel似乎缺乏良好的用户友好的UTF-8 CSV支持，所以我认为 答案是正确的，我已经接受了Alex的回答，因为它真的说Excel不能透明地做。这就是我在这里自动混淆的。马克的答案促使更复杂的方式让更多的高级用户实现预期的效果。两个答案都很棒，但是Alex的一个更适合我没有明确指定的问题。

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

最后一次编辑五个月后，我注意到Alex的回答由于某种原因而消失了。我真的希望这不是一个技术问题，我希望现在再没有更多的讨论。所以我接受马克的答案是最好的答案。

Five months later after the last edit, I've noticed that Alex's answer has disappeared for some reason. I really hope it wasn't a technical issue and I hope there is no more discussion on which answer is greater now. So I'm accepting Mark's answer as the best one.

推荐答案

Alex是正确的，但是你必须导出到csv，您可以在打开csv文件时向用户提供此建议：

Alex is correct, but as you have to export to csv, you can give the users this advice when opening the csv files:

1. 将导出的文件另存为csv


2. 打开Excel


3. 使用数据导入数据 - >导入外部数据 - >导入数据


4. 选择文件类型csv 并浏览到您的文件


5. 在导入向导中将File_Origin更改为65001 UTF（或选择正确的语言字符标识符）


6. 更改分隔符到逗号


7. 选择要导入的位置并完成

1. Save the exported file as a csv
2. Open Excel
3. Import the data using Data-->Import External Data --> Import Data
4. Select the file type of "csv" and browse to your file
5. In the import wizard change the File_Origin to "65001 UTF" (or choose correct language character identifier)
6. Change the Delimiter to comma
7. Select where to import to and Finish

正确显示

这篇关于是否可以强制Excel自动识别UTF-8 CSV文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！