blocks|key|1257721|text|有一个简单的在线工具可以做到这一点，称为sqlizer.io。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1257722|​|1257723|📷|atomic|1257724|1257725|您上传一个XLSX文件到其中，输入一个工作表名称和单元格区域，它将生成一条CREATE+TABLE语句和一组INSERT语句，将所有数据导入到MySQL数据库中。|1257726|(免责声明:我帮助运行SQLizer)|1257727|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlizer.io/|1|IMAGE|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/8c0442966aa35d14404ab623edba2af7.png|imageAlt^0|K|A|0|0|0|0|1|1|0|0|0|0^^$0|@$1|2|3|4|5|6|7|11|8|@]|9|@$A|12|B|13|1|14]]|C|$]]|$1|D|3|E|5|6|7|15|8|@]|9|@]|C|$]]|$1|F|3|G|5|H|7|16|8|@]|9|@$A|17|B|18|1|19]]|C|$]]|$1|I|3|E|5|6|7|1A|8|@]|9|@]|C|$]]|$1|J|3|K|5|6|7|1B|8|@]|9|@]|C|$]]|$1|L|3|M|5|6|7|1C|8|@]|9|@]|C|$]]|$1|N|3|-4|5|6|7|1D|8|@]|9|@]|C|$]]]|O|$P|$5|Q|R|S|C|$T|U]]|V|$5|W|R|X|C|$Y|Z|10|-4]]]]

There's a simple online tool that can do this called <a href="http://sqlizer.io" rel="noreferrer">sqlizer.io</a>. 

<a href="http://sqlizer.io" rel="noreferrer"><img src="https://i.stack.imgur.com/IwyKA.png" alt="Screenshot from sqlizer.com"></a>

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.

(Disclaimer: I help run SQLizer)

blocks|key|1257812|text|不知道你是否有这些设置，但对我来说，我使用的是PHP和MYSQL。所以我使用一个PHP类PHPExcel。它接受几乎任何格式的文件，xls，xlsx，cvs，...然后让您读取和/或插入。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1257813|因此，我最终要做的就是将excel加载到phpexcel对象中，然后遍历所有行。根据我的需要，我编写了一个简单的SQL+insert命令，将excel文件中的数据插入到我的表中。|1257814|在前端，这是一个小工作，但这只是一个调整一些现有的代码示例的问题。但是，当您拨入它时，对导入进行更改是简单而快速的。|1257815|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

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.

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.

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.

blocks|key|1257980|text|最好也是最简单的方法是使用"MySQL+for+Excel“应用程序，这是一款来自oracle的免费应用程序。此应用程序在excel中添加了一个插件，用于将数据导出和导入到mysql。你可以从here下载|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1257981|entityMap|0|LINK|mutability|MUTABLE|url|http://www.mysql.com/why-mysql/windows/excel/^0|2O|4|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@$A|M|B|N|1|O]]|C|$]]|$1|D|3|-4|5|6|7|P|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]]]

the best and easiest way is to use "MySQL for Excel" app that is a free app from oracle. this app added a plugin to excel to export and import data to mysql. you can download that from <a href="http://www.mysql.com/why-mysql/windows/excel/" rel="nofollow">here</a>

blocks|key|1257943|text|您可以使用DocChow，这是一个非常直观的GIU，用于将Excel导入到MySQL中，并且在大多数常见平台(包括Linux)上都是免费的。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1257944|尤其是当您关心日期、日期时间数据类型时，DocChow可以轻松地处理数据类型。如果您正在处理多个想要导入到一个MySQL表中的Excel电子表格，DocChow会做这项繁琐的工作。|1257945|entityMap|0|LINK|mutability|MUTABLE|url|http://docchow.com/|1^0|5|7|0|0|K|7|1|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@$A|P|B|Q|1|R]]|C|$]]|$1|D|3|E|5|6|7|S|8|@]|9|@$A|T|B|U|1|V]]|C|$]]|$1|F|3|-4|5|6|7|W|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]|N|$5|I|J|K|C|$L|M]]]]

You could use <a href="http://docchow.com" rel="nofollow">DocChow</a>, a very intuitive GIU for importing Excel into MySQL, and it's free on most common platforms (including Linux). 

More especially if you are concerned about date, datetime datatypes, <a href="http://docchow.com" rel="nofollow">DocChow</a> easily handles datatypes. If you are working with multiple Excel spreadsheets that you want to import into one MySQL table DocChow does the dirty work.

blocks|key|1258035|text|步骤1创建CSV文件|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1258036|步骤2登录到您的mysql服务器|1258037|+++++mysql+-uroot+-pyourpassword+|code-block|syntax|javascript|1258038|步骤3加载您的csv文件|1258039|+++++load+data+local+infile+'//home/my-sys/my-excel.csv'+into+table+my_tables+fields+terminated+by+','+enclosed+by+'"'+(Country,+Amount,Qty);|1258040|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

Step 1 Create Your CSV file

Step 2 log in to your mysql server

<pre><code> mysql -uroot -pyourpassword 
</code></pre>

Step 3
load your csv file

<pre><code> load data local infile '//home/my-sys/my-excel.csv' into table my_tables fields terminated by ',' enclosed by '"' (Country, Amount,Qty);
</code></pre>

blocks|key|1258085|text|另一个有用的工具是Toad+for+MySQL，它是MySQL的前端替代品。遗憾的是，Quest不再支持它，而是一个出色的MySQL集成开发环境，具有导入和导出向导，可满足大多数文件类型的需求。|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|1258086|entityMap|0|LINK|mutability|MUTABLE|url|https://download.cnet.com/Toad-for-MySQL/3000-10254_4-10567178.html|1|https://www.quest.com/^0|9|E|0|17|5|1|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]|$A|R|B|S|1|T]]|C|$]]|$1|D|3|-4|5|6|7|U|8|@]|9|@]|C|$]]]|E|$F|$5|G|H|I|C|$J|K]]|L|$5|G|H|I|C|$J|M]]]]

Another useful tool, and as a MySQL front-end replacement, is <a href="https://download.cnet.com/Toad-for-MySQL/3000-10254_4-10567178.html" rel="nofollow noreferrer">Toad for MySQL</a>. Sadly, no longer supported by <a href="https://www.quest.com" rel="nofollow noreferrer">Quest</a>, but a brilliant IDE for MySQL, with IMPORT and EXPORT wizards, catering for most file types.

blocks|key|1472802|text|最快最简单的方法是将XLS保存为ODS+(打开文档预览表)，然后从PhpMyAdmin导入它|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1472803|📷|atomic|offset|length|1472804|​|1472805|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/e3e66b315f1ecf82d7182ee58bcfb20e.png|imageAlt^0|0|0|1|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@$E|T|F|U|1|V]]|A|$]]|$1|G|3|H|5|6|7|W|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|X|8|@]|9|@]|A|$]]]|J|$K|$5|L|M|N|A|$O|P|Q|-4]]]]

Fastest and simpliest way is to save XLS as ODS (open document spreasheet) and import it from PhpMyAdmin
<a href="https://i.stack.imgur.com/OMIvb.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/OMIvb.png" alt="enter image description here" /></a>

blocks|key|1472907|text|如果你不喜欢插件，VBA和外部工具，我有一个excel文件，使用公式只允许你创建插入/更新。你只需要把数据放在单元格上：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1472908|​|1472909|📷|atomic|offset|length|1472910|1472911|作为额外功能，文件中还有另一个用于创建表的选项卡：|1472912|1472913|1472914|1472915|该文件可在以下链接中找到：|1472916|EXCEL+FILE|1472917|entityMap|0|IMAGE|mutability|IMMUTABLE|imageUrl|https://ask.qcloudimg.com/http-save/yehe-900000/207eb6b8071509fdaff7de555a59c33f.png|imageAlt|1|https://ask.qcloudimg.com/http-save/yehe-900000/da3a990674865815af19eaf2f58e48a5.png|2|LINK|MUTABLE|url|https://drive.google.com/file/d/1_xymS3BZmxpj7UiXpkIrnyAb-IpDhl9R/view?usp=sharing^0|0|0|0|1|0|0|0|0|0|0|1|1|0|0|0|0|A|2|0^^$0|@$1|2|3|4|5|6|7|18|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|19|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|1A|8|@]|9|@$G|1B|H|1C|1|1D]]|A|$]]|$1|I|3|C|5|6|7|1E|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1F|8|@]|9|@]|A|$]]|$1|L|3|C|5|6|7|1G|8|@]|9|@]|A|$]]|$1|M|3|E|5|F|7|1H|8|@]|9|@$G|1I|H|1J|1|1K]]|A|$]]|$1|N|3|C|5|6|7|1L|8|@]|9|@]|A|$]]|$1|O|3|P|5|6|7|1M|8|@]|9|@]|A|$]]|$1|Q|3|R|5|6|7|1N|8|@]|9|@$G|1O|H|1P|1|1Q]]|A|$]]|$1|S|3|-4|5|6|7|1R|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z|10|-4]]|11|$5|V|W|X|A|$Y|12|10|-4]]|13|$5|14|W|15|A|$16|17]]]]

If you don't like plugins, VBA and external tools, I have an excel file that using formulas only allows you to create INSERT/UPDATES. You only have to put the data on the cells:
<a href="https://i.stack.imgur.com/mCjB9.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/mCjB9.png" alt="enter image description here" /></a>
As an extra, there's another tab in the file to CREATE TABLES:
<a href="https://i.stack.imgur.com/oqc38.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/oqc38.png" alt="enter image description here" /></a>
The file can be found on the following link:
<a href="https://drive.google.com/file/d/1_xymS3BZmxpj7UiXpkIrnyAb-IpDhl9R/view?usp=sharing" rel="nofollow noreferrer">EXCEL FILE</a>

How to import an excel file in to a MySQL database

集成开发环境

SQL数据库

数据库

服务器

MySQL

Linux

问如何将excel文件导入到MySQL数据库
EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将excel文件导入到MySQL数据库EN

回答 8

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何将excel文件导入到MySQL数据库
EN