使用 phpMyAdmin 将 CSV 文件导入 MySQL

2022-01-05 00:00:00 csv mysql 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 '	'
LINES TERMINATED BY '
'
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 文件.
  4. 为格式选择CSV using LOAD DATA".
  5. 选择其他格式特定选项.
  6. 点击开始.
  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.

相关文章