MySQL 如何将excel文件导入MySQL数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1310166/
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
How to import an excel file in to a MySQL database
提问by Fero
Can any one explain how to import a Microsoft Excel file in to a MySQL database?
任何人都可以解释如何将 Microsoft Excel 文件导入 MySQL 数据库吗？
For example, my Excel table looks like this:
例如，我的 Excel 表格如下所示：
Country | Amount | Qty
----------------------------------
America | 93 | 0.60
Greece | 9377 | 0.80
Australia | 9375 | 0.80
采纳答案by ndp
Export it into some text format. The easiest will probably be a tab-delimited version, but CSV can work as well.
Use the load data capability. See http://dev.mysql.com/doc/refman/5.1/en/load-data.html
Look half way down the page, as it will gives a good example for tab separated data:
FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\'
Check your data. Sometimes quoting or escaping has problems, and you need to adjust your source, import command-- or it may just be easier to post-process via SQL.
将其导出为某种文本格式。最简单的可能是制表符分隔的版本，但 CSV 也可以工作。
使用加载数据功能。见http://dev.mysql.com/doc/refman/5.1/en/load-data.html
查看页面的一半，因为它将为制表符分隔数据提供一个很好的示例：
由 '\t' 终止的字段由 '' 包围，由 '\' 转义
检查您的数据。有时引用或转义会出现问题，您需要调整源代码、导入命令——或者通过 SQL 进行后处理可能更容易。
回答by d4nt
There's a simple online tool that can do this called sqlizer.io.
有一个简单的在线工具可以执行此操作，称为sqlizer.io。
You upload an XLSX file to it, enter a sheet name and cell range, and it will generate a CREATE TABLE statement and a bunch of INSERT statements to import all your data into a MySQL database.
你上传一个 XLSX 文件到它，输入一个工作表名称和单元格范围，它会生成一个 CREATE TABLE 语句和一堆 INSERT 语句来将你的所有数据导入到 MySQL 数据库中。
(Disclaimer: I help run SQLizer)
（免责声明：我帮助运行 SQLizer）
回答by thdoan
Below is another method to import spreadsheet data into a MySQL database that doesn't rely on any extra software. Let's assume you want to import your Excel table into the salestable of a MySQL database named mydatabase.
下面是另一种将电子表格数据导入 MySQL 数据库的方法，该方法不依赖于任何额外的软件。假设您要将 Excel 表导入到sales名为mydatabase.
Select the relevant cells:
Paste into Mr. Data Converterand select the output as MySQL:
Change the table name and column definitions to fit your requirements in the generated output:
选择相关单元格：
粘贴到Mr. Data Converter并选择输出为 MySQL：
在生成的输出中更改表名和列定义以满足您的要求：
CREATE TABLE sales (
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Country VARCHAR(255),
Amount INT,
Qty FLOAT
);
INSERT INTO sales
(Country,Amount,Qty)
VALUES
('America',93,0.60),
('Greece',9377,0.80),
('Australia',9375,0.80);
If you're using MySQL Workbenchor already logged into
mysqlfrom the command line, then you can execute the generated SQL statements from step 3 directly. Otherwise, paste the code into a text file (e.g.,import.sql) and execute this command from a Unix shell:mysql mydatabase < import.sqlOther ways to import from a SQL file can be found in this Stack Overflow answer.
如果您正在使用MySQL Workbench或已经
mysql从命令行登录，那么您可以直接执行步骤 3 中生成的 SQL 语句。否则，将代码粘贴到文本文件（例如，import.sql）并从 Unix shell 执行此命令：mysql mydatabase < import.sql其他从 SQL 文件导入的方法可以在这个 Stack Overflow answer 中找到。
回答by panofish
There are actually several ways to import an excel file in to a MySQL database with varying degrees of complexity and success.
实际上有多种方法可以将 excel 文件导入 MySQL 数据库，其复杂程度和成功程度各不相同。
Excel2MySQL. Hands down, the easiest and fastest way to import Excel data into MySQL. It supports all verions of Excel and doesn't require Office install.
LOAD DATA INFILE: This popular option is perhaps the most technical and requires some understanding of MySQL command execution. You must manually create your table before loading and use appropriately sized VARCHAR field types. Therefore, your field data types are not optimized. LOAD DATA INFILE has trouble importing large files that exceed 'max_allowed_packet' size. Special attention is required to avoid problems importing special characters and foreign unicode characters. Here is a recent example I used to import a csv file named test.csv.
phpMyAdmin: Select your database first, then select the Import tab. phpMyAdmin will automatically create your table and size your VARCHAR fields, but it won't optimize the field types. phpMyAdmin has trouble importing large files that exceed 'max_allowed_packet' size.
MySQL for Excel: This is a free Excel Add-in from Oracle. This option is a bit tedious because it uses a wizard and the import is slow and buggy with large files, but this may be a good option for small files with VARCHAR data. Fields are not optimized.
Excel2MySQL。毫无疑问，这是将 Excel 数据导入 MySQL 的最简单快捷的方法。它支持所有版本的 Excel，不需要安装 Office。
LOAD DATA INFILE：这个流行的选项可能是最具技术性的，需要对 MySQL 命令执行有一定的了解。您必须在加载之前手动创建表并使用适当大小的 VARCHAR 字段类型。因此，您的字段数据类型未优化。LOAD DATA INFILE 无法导入超过“max_allowed_packet”大小的大文件。需要特别注意避免导入特殊字符和外来 unicode 字符时出现问题。这是我最近用来导入名为 test.csv 的 csv 文件的示例。
phpMyAdmin：首先选择您的数据库，然后选择导入选项卡。phpMyAdmin 将自动创建您的表并调整您的 VARCHAR 字段的大小，但它不会优化字段类型。phpMyAdmin 无法导入超过“max_allowed_packet”大小的大文件。
MySQL for Excel：这是来自 Oracle 的免费 Excel 插件。此选项有点乏味，因为它使用向导，并且导入速度慢且大文件有问题，但对于包含 VARCHAR 数据的小文件，这可能是一个不错的选择。字段未优化。
回答by user1441213
Not sure if you have all this setup, but for me I am using PHP and MYSQL. So I use a PHP class PHPExcel. This takes a file in nearly any format, xls, xlsx, cvs,... and then lets you read and / or insert.
不确定您是否拥有所有这些设置，但对我而言，我使用的是 PHP 和 MYSQL。所以我使用了一个 PHP 类 PHPExcel。这需要一个几乎任何格式的文件，xls、xlsx、cvs 等，然后让您读取和/或插入。
So what I wind up doing is loading the excel in to a phpexcel object and then loop through all the rows. Based on what I want, I write a simple SQL insert command to insert the data in the excel file into my table.
所以我最终要做的是将 excel 加载到 phpexcel 对象中，然后循环遍历所有行。根据我的需要，我编写了一个简单的 SQL 插入命令，将 excel 文件中的数据插入到我的表中。
On the front end it is a little work, but its just a matter of tweaking some of the existing code examples. But when you have it dialed in making changes to the import is simple and fast.
在前端，它需要做一些工作，但这只是调整一些现有代码示例的问题。但是，当您拨打电话时，对导入进行更改既简单又快速。
回答by Christophe Weis
When using text files to import data, I had problems with quotes and how Excel was formatting numbers. For example, my Excel configuration used the comma as decimal separator instead of the dot.
使用文本文件导入数据时，我遇到了引号问题以及 Excel 如何格式化数字。例如，我的 Excel 配置使用逗号作为小数点分隔符而不是点。
Now I use Microsoft Access2010 to open my MySql table as linked table. There I can simply copy and paste cells from Excel to Access.
现在我使用 Microsoft Access2010 将 MySql 表作为链接表打开。在那里，我可以简单地将单元格从 Excel 复制并粘贴到 Access。
To do this, first install the MySql ODBC driverand create an ODBC connection. Then in access, in the "External Data" tab, open "ODBC Database" dialog and link to any table using the ODBC connection.
为此，首先安装MySql ODBC 驱动程序并创建ODBC 连接。然后在访问中，在“外部数据”选项卡中，打开“ODBC 数据库”对话框并使用 ODBC 连接链接到任何表。
Using MySql Workbench, you can also copy and paste your Excel data into the result grid of MySql Workbench. I gave detailed instructions in this answer.
使用MySql Workbench，您还可以将 Excel 数据复制并粘贴到 MySql Workbench 的结果网格中。我在这个答案中给出了详细说明。
回答by Sadeq Shajary
回答by Raúl Moreno
For a step by step example for importing Excel 2007 into MySQL with correct encoding(UTF-8) search for this comment:
有关使用正确编码(UTF-8)将 Excel 2007 导入 MySQL的分步示例，请搜索此评论：
"Posted by Mike Laird on October 13 2010 12:50am"
“由 Mike Laird 于 2010 年 10 月 13 日上午 12:50 发布”
in the next URL:
在下一个网址中：
回答by Seanj1000
You could use DocChow, a very intuitive GIU for importing Excel into MySQL, and it's free on most common platforms (including Linux).
您可以使用DocChow，这是一个非常直观的 GIU，用于将 Excel 导入 MySQL，并且它在大多数常见平台（包括 Linux）上都是免费的。
More especially if you are concerned about date, datetime datatypes, DocChoweasily handles datatypes. If you are working with multiple Excel spreadsheets that you want to import into one MySQL table DocChow does the dirty work.
更特别的是，如果您关心日期、日期时间数据类型， DocChow可以轻松处理数据类型。如果您正在处理要导入到一个 MySQL 表中的多个 Excel 电子表格，DocChow 会做一些肮脏的工作。
回答by Syed Shibli
Step 1 Create Your CSV file
步骤 1 创建您的 CSV 文件
Step 2 log in to your mysql server
第2步登录你的mysql服务器
mysql -uroot -pyourpassword
Step 3 load your csv file
第 3 步加载您的 csv 文件
load data local infile '//home/my-sys/my-excel.csv' into table my_tables fields terminated by ',' enclosed by '"' (Country, Amount,Qty);
