使用 phpMyAdmin 将 CSV 文件导入 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23815741/
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
Import CSV file into MySQL using phpMyAdmin
提问by CharlesEF
I have searched and read many posts/articles regarding importing a CSV file into a MySQL database using phpMyAdmin 2.8.0.1and they make it sound so simple, in actually it is not. Nothing I do works correctly.
我搜索并阅读了许多关于使用 CSV 文件导入 MySQL 数据库的帖子/文章phpMyAdmin 2.8.0.1，它们听起来很简单，但实际上并非如此。我所做的一切都不能正常工作。
I have a table with 2 columns, both defined as NOT NULL. The primary index is configured to use both columns. I have many CSV files to import but I'm starting with the small ones first. Here is a sample of my CSV data file:
我有一个包含 2 列的表，均定义为NOT NULL. 主索引配置为使用两列。我有很多 CSV 文件要导入，但我首先从小的文件开始。这是我的 CSV 数据文件的示例：
type description
T Antarctic Territory
T Dependency
T Independent State
T Proto Dependency
T Proto Independent State
There are only 17 rows to import but usually I get 0 rows inserted and sometimes I get 1 row inserted but it is in the wrong format. What I mean is that column 1 is blank and column 2 contains the data of both columns, in the wrong order. This is the SQL generated by my import attempt:
只有 17 行要导入，但通常我会插入 0 行，有时我会插入 1 行，但格式错误。我的意思是第 1 列是空白的，第 2 列包含两列的数据，顺序错误。这是我的导入尝试生成的 SQL：
LOAD DATA LOCAL INFILE '/var/php_sessions/uploads/phpiptDPV' REPLACE INTO TABLE `country_types`
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\r\n'
IGNORE 1
LINES (
`type` ,
`description`
)# MySQL returned an empty result set (i.e. zero rows).
Can anyone see where I'm going wrong? I've spent a few days researching and trying different things but I'm ready to throw out phpMyAdmin.
谁能看到我哪里出错了？我花了几天时间研究和尝试不同的东西，但我准备放弃 phpMyAdmin。
回答by Bill Karwin
The problem is with LOCAL. There are two concepts of "local" in this case. You probably mean that the CSV file is on the workstation where you are running your browser accessing phpMyAdmin.
问题在于LOCAL. 在这种情况下，“本地”有两个概念。您可能是说 CSV 文件位于您运行浏览器访问 phpMyAdmin 的工作站上。
But the LOAD DATA LOCAL INFILEstatement is running on the web server where phpMyAdmin is running. So it's looking for the file on the web server. When I tried this I got this error output by phpMyAdmin:
但是该LOAD DATA LOCAL INFILE语句正在运行 phpMyAdmin 的 Web 服务器上运行。所以它正在寻找网络服务器上的文件。当我尝试这个时，我得到了 phpMyAdmin 的这个错误输出：
#7890 - Can't find file '/Users/billkarwin/t.csv'.
You can try using phpMyAdmin's Import feature.
您可以尝试使用 phpMyAdmin 的导入功能。
- Select your table.
- Click the Importtab.
- Click the Choose filebutton to browse to your local csv file.
- Select the 'CSV using LOAD DATA' for Format.
- Choose other Format-Specific Options.
- Click Go.
- 选择你的桌子。
- 单击导入选项卡。
- 单击“选择文件”按钮以浏览到您的本地 csv 文件。
- 为Format选择“CSV using LOAD DATA” 。
- 选择其他特定于格式的选项。
- 点击前往。
回答by Hanumant Tuntoni
The following steps worked for me.
以下步骤对我有用。
Import the file to a dummy database (i.e. don't select any database).
Hit import and select the appropriate CSV file that you want to load the data with.
phpMyadmin will create a database and an appropriate table to hold the data.
Hit export and select the SQL format
You will get to download an SQL file.
The file will have the appropriate query to insert the data into your table.
将文件导入虚拟数据库（即不选择任何数据库）。
点击导入并选择要加载数据的相应 CSV 文件。
phpMyadmin 将创建一个数据库和一个适当的表来保存数据。
点击导出并选择 SQL 格式
您将可以下载 SQL 文件。
该文件将具有适当的查询以将数据插入到您的表中。
Make sure you change the default table name and column name to match your table name and column names.
确保更改默认表名和列名以匹配您的表名和列名。
回答by Steve Rapaport
I have finally found an easy and reliable way to import from Excel to phpMyAdmin. Save in Excel in OpenDocument Spreadsheet format. Import into phpMyAdmin in that same format. Poof, it's all there. It sets up column names as A, B, C,... etcetera, but other than that it's flawless.
我终于找到了一种从 Excel 导入到 phpMyAdmin 的简单可靠的方法。以 OpenDocument 电子表格格式保存在 Excel 中。以相同的格式导入 phpMyAdmin。噗，全都有。它将列名设置为 A、B、C、...等等，但除此之外它是完美的。
I have just saved myself another day of fiddling with quote marks, escapes, trailing tabs, extra carriage returns, delimiting delimiters, and professional database wrestling.
我刚刚又节省了一天摆弄引号、转义符、尾随制表符、额外回车、定界符和专业数据库摔跤的日子。
