使用phpMyAdmin将CSV文件导入MySQL [英] Import CSV file into MySQL using phpMyAdmin

问题描述

我已经搜索并阅读了许多有关使用 phpMyAdmin 2.8.0.1 将CSV文件导入MySQL数据库的帖子/文章，它们使它听起来如此简单，实际上不是。我没有做正确的工作。

I have searched and read many posts/articles regarding importing a CSV file into a MySQL database using phpMyAdmin 2.8.0.1 and they make it sound so simple, in actually it is not. Nothing I do works correctly.

我有一个表有2列，都定义为 NOT NULL 。主索引配置为使用两个列。我有很多CSV文件要导入，但我从小的首先开始。以下是我的CSV数据文件示例：

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:

type    description
T   Antarctic Territory
T   Dependency
T   Independent State
T   Proto Dependency
T   Proto Independent State

只有17行要导入，但通常我得到0行插入，有时我得到1行插入，但它是错误的格式。我的意思是列1是空白，列2包含两个列的数据，按错误的顺序。这是我的导入尝试生成的SQL：

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:

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).

任何人都可以看到我会出错吗？我花了几天研究和尝试不同的事情，但我准备抛出phpMyAdmin。

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.

推荐答案

问题是与 LOCAL 。在这种情况下有两个本地的概念。您可能意味着CSV文件位于运行浏览器访问phpMyAdmin的工作站上。

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.

但是 LOAD DATA LOCAL INFILE 语句正在运行phpMyAdmin的Web服务器上运行。所以它在网络服务器上寻找文件。当我尝试这个我得到这个错误输出phpMyAdmin：

But the LOAD DATA LOCAL INFILE statement 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:

#7890 - Can't find file '/Users/billkarwin/t.csv'. 

您可以尝试使用phpMyAdmin的导入功能。

You can try using phpMyAdmin's Import feature.

1. 选择您的表格。


2. 点击导入标签。


3. 点击选择文件按钮浏览到您当地的csv文件。


Go 。

1. Select your table.
2. Click the Import tab.
3. Click the Choose file button to browse to your local csv file.
4. Select the 'CSV using LOAD DATA' for Format.
5. Choose other Format-Specific Options.
6. Click Go.

这篇关于使用phpMyAdmin将CSV文件导入MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！