将大的xls文件导入mysql数据库

Question

我想导入大的xlsx（电子表格文件）到mysql数据库。 我之前是通过phpmyadmin完成的，但是这个文件太大（205000行）。

所以当我想要通过phpmyadmin来做，它花了很长时间，没有完成。

什么是最好的和快速的方式将其导入到mysql数据库？

Answer 1

你可以在MySQL中使用LOAD DATA命令做到这一点： http://blog.tjitjing.com/index.php/2008/02/import-excel-data-into-mysql-in-5-easy.html

Save your Excel data as a csv file (In Excel 2007 using Save As) 
Check the saved file using a text editor such as Notepad to see what it actually looks like, i.e. what delimiter was used etc. 
Start the MySQL Command Prompt (I usually do this from the MySQL Query Browser – Tools – MySQL Command Line Client to avoid having to enter username and password etc.) 
Enter this command: 
LOAD DATA LOCAL INFILE ‘C:\\temp\\yourfile.csv’ INTO TABLE database.table FIELDS TERMINATED BY ‘;’ ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘\r\n’ (field1, field2); 
[Edit: Make sure to check your single quotes (') and double quotes (") if you copy and paste this code] 
Done! 

Answer 2

您可以尝试使用的Navicat MySQL的。我已经用250MB + xlsx文件完成了这个工作，Navicat完美无缺地处理它，而不会冒出汗来。

只需确保您的MySQL配置为能够通过将my.ini中的max_allowed_packet选项更改为更大数量（例如128M）来接收大量数据。

Answer 3

Toad for MySQL（Freeware）将是另一种选择。