有没有办法将 LOAD DATA INFILE (import) xlsx 文件导入 MySQL 数据库表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7982868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way of LOAD DATA INFILE (import) xlsx file into MySQL database table
提问by Pepys
I know that this is discussed a lot but I don't find solution of how to do that.
我知道这被讨论了很多，但我没有找到如何做到这一点的解决方案。
What I need is to import an excel file (xls/xlsx) to my database table. It is a button which does that and the command which is executed is like that:
我需要的是将一个 excel 文件 (xls/xlsx) 导入到我的数据库表中。这是一个执行此操作的按钮，执行的命令如下所示：
string cmdText = "LOAD DATA INFILE 'importTest4MoreMore.csv' INTO TABLE management FIELDS TERMINATED BY ',';";
It works great. But I need to import excel file not CSV. As far as I know LOAD DATA command does not support binary files which xls is. So what's the solution to that? Please help
它工作得很好。但我需要导入 excel 文件而不是 CSV。据我所知 LOAD DATA 命令不支持 xls 的二进制文件。那么有什么解决办法呢？请帮忙
Thanks a lot
非常感谢
pepys
佩皮斯
回答by Marc B
.xls will never be importable directly into MySQL. it's a compound OLE file, which means its internal layout is not understandable by mere mortals (or even Bill Gates). .xlsx is basically just a .zip file which contains multiple .xml/xslt/etc. files. You can probably extract the relevant .xml that contains the actual spreadsheet data, but again - it's not likely to be in a format that's directly importable by MySQL's load infile.
.xls 永远不会直接导入 MySQL。它是一个复合 OLE 文件，这意味着它的内部布局是普通人（甚至比尔盖茨）无法理解的。.xlsx 基本上只是一个 .zip 文件，其中包含多个 .xml/xslt/etc。文件。您可能可以提取包含实际电子表格数据的相关 .xml，但同样 - 它不太可能采用可由 MySQL 的加载文件直接导入的格式。
The simplest solution is to export the .xls/xlsx to a .csv.
最简单的解决方案是将 .xls/xlsx 导出为 .csv。
回答by Mohsen Abasi
How to import 'xlsx' file into MySQL:
如何将 'xlsx' 文件导入 MySQL：
1/ Open your '.xlsx' file Office Excel and click on 'Save As' button from menu and select
1/打开你的“.xlsx”文件Office Excel，然后从菜单中点击“另存为”按钮并选择
'CSV (MS-DOS) (*.csv)'
from 'Save as type' list. Finally click 'Save' button.
来自“另存为类型”列表。最后点击“保存”按钮。
2/ Copy or upload the .csv file into your installed MySQL server (a directory path like: '/root/someDirectory/' in Linux servers)
2/ 将 .csv 文件复制或上传到您安装的 MySQL 服务器（Linux 服务器中的目录路径如：'/root/someDirectory/'）
3/ Login to your database:
3/ 登录到您的数据库：
mysql -u root -pSomePassword
4/ Create and use destination database:
4/ 创建和使用目标数据库：
use db1
5/ Create a MySQL table in your destination database (e.g. 'db1') with columns like the ones of '.csv' file above.
5/ 在目标数据库（例如“db1”）中创建一个 MySQL 表，其中的列类似于上面的“.csv”文件中的列。
6/ Execute the following command:
6/执行以下命令：
LOAD DATA INFILE '/root/someDirectory/file1.csv' INTO TABLE `Table1` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;
Please note that the option 'IGNORE 1 LINES' says MySQL to ignore the first line of '.csv' file. So, it is just for '.xlsx' files with 1 header column. You can remove this option.
请注意，选项“IGNORE 1 LINES”表示 MySQL 忽略“.csv”文件的第一行。因此，它仅适用于具有 1 个标题列的“.xlsx”文件。您可以删除此选项。
回答by Devart
You can load xls or xlsx files with Data Importtool (MS Excel or MS Excel 2007 format) in dbForge Studio for MySQL. This tool opens Excel files directly, COM interface is not used; and command line is supported.
您可以在dbForge Studio for MySQL 中使用数据导入工具（MS Excel 或 MS Excel 2007 格式）加载 xls 或 xlsx 文件。本工具直接打开Excel文件，不使用COM接口；并且支持命令行。
