将MySQL用于OUTFILE以生成Excel可读的UTF8数据

I have a problem similar to this question. That is - I need to export some UTF8 data within a MySQL database to MS Excel.

我有类似这个问题的问题。那就是 - 我需要将MySQL数据库中的一些UTF8数据导出到MS Excel。

The gotchas kindly Excel provides:

好极了Excel提供：

Excel opens UTF8 formatted CSV files as ANSCI, thus breaking
Excel将UTF8格式的CSV文件作为ANSCI打开，从而破坏
Excel will open tab-seperated UTF8 files correctly, but there is no support for linebreaks (my data has linebreaks, though in a worst-case scenario I might be able to loose these)
Excel将正确打开制表符分隔的UTF8文件，但不支持换行符（我的数据有换行符，但在最坏的情况下我可能会松开这些行）
Excel will, apparently, open UTF-16LE (little endian) encoded CSVs OK. However, so far as I know, MySQL INTO OUTFILE does not accept content encoding argument, and just defaults to the database encoding (UTF8).
显然，Excel将打开UTF-16LE（小端）编码的CSV。但是，据我所知，MySQL INTO OUTFILE不接受内容编码参数，只是默认为数据库编码（UTF8）。

My web-app is PHP driven, but unfortunately I cannot use a PHP Excel-file-making library since the database is pretty large. All my exports must be done through MySQL.

我的网络应用程序是PHP驱动的，但不幸的是我不能使用PHP Excel文件制作库，因为数据库非常大。我的所有出口都必须通过MySQL完成。

If anybody knows how to make MySQL jump through Excel's hoops on this one, that would be great.

如果有人知道如何让MySQL在这个问题上跳过Excel的箍，那就太好了。

Many thanks,

非常感谢，

Jack

插口

Edit: This answer describes a solution that works for Excel 2007. Adding a 'BOM' to the file, which I may be able to do by providing the outputted file to the client via a PHP script that appends the BOM. Ideally I would like to find a solution that works in 2003 also.

编辑：此答案描述了适用于Excel 2007的解决方案。向文件添加“BOM”，我可以通过附加BOM的PHP脚本将输出的文件提供给客户端来执行此操作。理想情况下，我想找到一个在2003年也有效的解决方案。

2 个解决方案

#1

I recall running into this issue with Excel. The BOM fix does work for Excel 2007 and 2010. We also wanted to support 2003, however our solution was to just write XLS files instead of CSV files (using Java). That doesn't sound like an option for you since you're exporting from MySQL. One idea that comes to mind is to convert your UTF8 output to UTF-16LE after your export. This page explains how to do it with Perl.

我记得用Excel运行这个问题。 BOM修复程序适用于Excel 2007和2010.我们还希望支持2003，但我们的解决方案是只编写XLS文件而不是CSV文件（使用Java）。这对你来说听起来不是一个选择，因为你是从MySQL导出的。想到的一个想法是在导出后将UTF8输出转换为UTF-16LE。本页介绍了如何使用Perl。

2 个解决方案

#1

更多相关文章

随机推荐