php 如何将excel文件转换成mysql数据库？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3621798/
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 convert excel file into mysql database?
提问by FlyingCat
my boss wants me to create a mysql database for an existing excel file. I was wondering if there are any ways to do convert or import excel file? I have searched google but didn't find anything useful. I appreciate for any reply....Thanks.
我的老板要我为现有的 excel 文件创建一个 mysql 数据库。我想知道是否有任何方法可以转换或导入 excel 文件？我已经搜索过谷歌，但没有找到任何有用的东西。我感谢任何答复....谢谢。
采纳答案by Icode4food
If you have a web server up and running with PHP support, I highly recommend phpMyAdmin.
如果您有一个支持 PHP 的网络服务器，我强烈推荐phpMyAdmin。
- Configure it to connect to MySQL
- Create the Database and table
- Click the import tab and you can import a CSV.
- 配置它以连接到 MySQL
- 创建数据库和表
- 单击导入选项卡，您可以导入 CSV。
If this is a simple one-time import this probably isn't worth the effort. If, on the other hand, you will ever have to work with MySQL again, you will never regret the time to install and configure phpMyAdmin.
如果这是一个简单的一次性导入，这可能不值得付出努力。另一方面，如果您将不得不再次使用 MySQL，您将永远不会后悔安装和配置 phpMyAdmin 的时间。
回答by Chris J
- Save as CSV
- Use a COPY sql statement
- Profit
- 另存为 CSV
- 使用 COPY sql 语句
- 利润
回答by Yanick Rochon
PHPMyAdmincan import CSV files, as well as Excel files (though I've never tried it)
PHPMyAdmin可以导入 CSV 文件，也可以导入 Excel 文件（虽然我从未尝试过）
First you need to create your datebase, and add a table. There must be as many fields in that table as there are columns in your Excel document (yes, I know you know)
首先，您需要创建您的数据库，并添加一个表。该表中的字段数必须与 Excel 文档中的列数一样多（是的，我知道你知道）
Then select that database and table in phpmyadmin and use the "Import" tab.
然后在 phpmyadmin 中选择该数据库和表并使用“导入”选项卡。
回答by jason m
easiest way to do it would be this:
最简单的方法是这样的：
insert into Table (col 1,col 2,col 3...col n) values (val1,...valn);
插入表 (col 1,col 2,col 3...col n) 值 (val1,...valn);
basically:
基本上：
do 2 for loops in your excel:
在你的 excel 中做 2 个 for 循环：
dim i,j
dim sqlString,sqlBase
sqlString=""
sqlBase="insert into yourTable (col1....coln) values ("
for i=1 to myRowVariable
sqlString=""
for j=1 to myColVariable
if j=myColVariable then
sqlString=sqlString & Cells(i,j).value & ");"
else if j=1 then
sqlString=sqlBase & sqlString & ","
else
sqlString=sqlString & Cells(i,j).value & ","
end if
Next j
Next i
'write sqlString into a txt or something
this will do what you need in a bootstrap but fast and very intuitive way.
这将以引导程序但快速且非常直观的方式完成您需要的操作。
回答by pascal
From Excel, export the sheet as a text file. In MySQL, use LOAD DATA INFILEto import the text file.
从 Excel 中，将工作表导出为文本文件。在 MySQL 中，用于LOAD DATA INFILE导入文本文件。
回答by Byron Whitlock
I wrote a tool that will let you do sql queries against a csv file. The output is saved as a csv as well. Maybe you will find it useful.
我编写了一个工具，可以让您对 csv 文件进行 sql 查询。输出也保存为 csv。也许你会发现它很有用。
回答by polemon
You can use an ODBC driver to "mount" an Excel file as database and then make SQL queries to it. All you need then, is a simple migration tool, to copy the tables to another databases system.
您可以使用 ODBC 驱动程序将 Excel 文件“挂载”为数据库，然后对其进行 SQL 查询。然后，您只需要一个简单的迁移工具，即可将表复制到另一个数据库系统。
I believe there's even an mysqldump-like tool for ODBC driven databases.
我相信甚至有一个mysqldump类似 ODBC 驱动数据库的工具。
回答by s1mm0t
A low tech solution would be to use the concatenation functionin excel to convert the data into a series of insert statements and then copy and paste them into mysql query analyzer or whatever client you are using.
一个低技术的解决方案是使用excel 中的连接函数将数据转换为一系列插入语句，然后将它们复制并粘贴到 mysql 查询分析器或您正在使用的任何客户端中。
