是否有一种LOAD DATA INFILE（导入）xlsx文件到MySQL数据库表中的方法 [英] Is there a way of LOAD DATA INFILE (import) xlsx file into MySQL database table

问题描述

我知道这是讨论了很多，但是我没有找到解决方法。

I know that this is discussed a lot but I don't find solution of how to do that.

我需要的是导入一个excel文件（xls / xlsx）到我的数据库表。这是一个执行该操作的按钮，执行的命令就是这样：

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:

 string cmdText = "LOAD DATA INFILE 'importTest4MoreMore.csv' INTO TABLE management FIELDS TERMINATED BY ',';";

它的效果很好。但是我需要导入excel文件不是CSV。据我所知，LOAD DATA命令不支持xls的二进制文件。
那么解决方法是什么呢？请帮助

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

非常感谢

pepys

推荐答案

.xls永远不能直接导入到MySQL中。它是一个复杂的OLE文件，这意味着它的内部布局是不可理解的只是凡人（甚至比尔盖茨）。 .xlsx基本上只是一个包含多个.xml / xslt / etc的.zip文件。文件。您可以提取包含实际电子表格数据的相关的.xml，但是再次 - 它不可能是由MySQL的负载infile直接导入的格式。

.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 / xlsx导出到.csv。

The simplest solution is to export the .xls/xlsx to a .csv.

这篇关于是否有一种LOAD DATA INFILE（导入）xlsx文件到MySQL数据库表中的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！