如何将CSV文件导入MySQL表?

我有一个来自客户端的非规范化事件日记CSV，我试图将其加载到MySQL表中，以便我可以重构成一个正常的格式。我创建了一个名为“CSVImport”的表，它有一个字段为CSV文件的每一列。CSV包含99列，所以这本身就已经是一项艰巨的任务:

CREATE TABLE 'CSVImport' (id INT);
ALTER TABLE CSVImport ADD COLUMN Title VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN Company VARCHAR(256);
ALTER TABLE CSVImport ADD COLUMN NumTickets VARCHAR(256);
...
ALTER TABLE CSVImport Date49 ADD COLUMN Date49 VARCHAR(256);
ALTER TABLE CSVImport Date50 ADD COLUMN Date50 VARCHAR(256);

表上没有约束，所有字段都包含VARCHAR(256)值，除了包含计数(用INT表示)、是/否(用BIT表示)、价格(用DECIMAL表示)和文本介绍(用text表示)的列。

我尝试将数据加载到文件中:

LOAD DATA INFILE '/home/paul/clientdata.csv' INTO TABLE CSVImport;
Query OK, 2023 rows affected, 65535 warnings (0.08 sec)
Records: 2023  Deleted: 0  Skipped: 0  Warnings: 198256
SELECT * FROM CSVImport;
| NULL             | NULL        | NULL           | NULL | NULL               |
...

整个表被NULL填充。

我认为问题在于文本简介包含多行，MySQL解析文件时就好像每个新行对应一个数据库行一样。我可以毫无问题地将文件加载到OpenOffice中。

csv文件包含2593行和570条记录。第一行包含列名。我认为它是用逗号分隔的，文本显然是用双引号分隔的。

更新:

如果有疑问，请阅读手册:http://dev.mysql.com/doc/refman/5.0/en/load-data.html

我在LOAD DATA语句中添加了一些信息，OpenOffice足够聪明地推断出这些信息，现在它加载了正确的记录数量:

LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

但是仍然有很多完全NULL记录，并且没有一个被加载的数据似乎在正确的地方。

1181692 次浏览
小开
最佳答案

问题的核心似乎是将CSV文件中的列与表中的列进行匹配。

许多图形化mySQL客户端都有非常好的导入对话框。

我最喜欢的工作是基于Windows的HeidiSQL。它为你提供了一个图形界面来构建LOAD DATA命令;您可以稍后以编程方式重用它。

Import textfile

截图:"Import textfile"对话框 .

要打开Import textfile"对话框，请到Tools > Import CSV file:

enter image description here

小开

mysql命令行在导入时容易出现太多问题。你可以这样做:

  • 使用excel编辑标题名称，使其没有空格
  • 另存为。csv
  • 使用免费的Navicat Lite Sql浏览器导入并自动创建一个新表(给它一个名字)
  • 打开新表，为ID插入一个主要的汽车编号列
  • 根据需要更改列的类型。
  • 完成了!
小开

使用mysqlimport将一个表加载到数据库:

mysqlimport --ignore-lines=1 \
--fields-terminated-by=, \
--local -u root \
-p Database \
TableName.csv

我在http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/找到的

要使分隔符为制表符，请使用--fields-terminated-by='\t'

小开

phpMyAdmin可以处理CSV导入。以下是步骤:

  1. 准备CSV文件，使字段与MySQL表字段的顺序相同。

  2. 从CSV中删除标题行(如果有的话)，以便文件中只有数据。

  3. 进入phpMyAdmin界面。

  4. 在左侧菜单中选择表。

  5. 单击顶部的导入按钮。

  6. 浏览到CSV文件。

  7. 选择选项“CSV using LOAD DATA”。

  8. 在“以”结尾的字段中输入“，”。

  9. 按与数据库表中相同的顺序输入列名。

  10. 点击go按钮，你就完成了。

这是我为将来使用准备的笔记，如果其他人能从中受益，就分享在这里。

小开

最简单的方法，我已经导入200+行是以下命令在phpmyadmin sql窗口

我有一个简单的国家表，有两列 CountryId, CountryName < / p >

这里是.csv dataCSV FILE

下面是命令:

LOAD DATA INFILE 'c:/country.csv'
INTO TABLE country
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

记住一件事，永远不要出现在第二列，否则导入将停止

小开

如果你使用的是装有Excel电子表格的windows电脑，那么新的mySql插件将会是非凡的。甲骨文的人在这个软件上做得很好。您可以直接从Excel建立数据库连接。这个插件会分析你的数据，并为你建立与数据一致的表格。我有一些超大的csv文件要转换。这个工具大大节省了时间。

http://dev.mysql.com/downloads/windows/excel/

您可以从Excel内部进行更新，这些更新将在线填充到数据库中。这对于在超级便宜的GoDaddy共享主机上创建的mySql文件非常有效。(注意，当你在GoDaddy上创建表时，你必须选择一些非标准设置来启用数据库的离线访问…)

有了这个插件，你可以在XL电子表格和在线mySql数据存储之间实现纯交互。

小开

可以通过在LOAD DATA语句中列出列来解决这个问题。从手册:

LOAD DATA INFILE 'persondata.txt' INTO TABLE persondata (col1,col2,...);

.．.因此，在您的情况下，您需要按照它们在CSV文件中出现的顺序列出99列。

小开

PHP查询导入csv文件到mysql数据库

$query = <<<EOF
LOAD DATA LOCAL INFILE '$file'
INTO TABLE users
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mobile,email)
EOF;
if (!$result = mysqli_query($this->db, $query))
{
exit(mysqli_error($this->db));
}

** CSV文件样本数据**

name,mobile,email
Christopher Gritton,570-686-3439,ChristopherKGritton@inbound.plus
Brandon Wilson,541-309-5149,BrandonMWilson@inbound.plus
Craig White,516-795-8065,CraigJWhite@inbound.plus
David Whitney,713-214-3966,DavidCWhitney@inbound.plus
小开

我使用此方法在0.046秒中导入超过100K的记录(~ 5 mb)

你可以这样做:

LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

包含最后一行是非常重要的，如果你有多个字段，通常会跳过最后一个字段。

LINES TERMINATED BY '\n'
(field_1,field_2 , field_3);

然后，假设你的字段有第一行作为标题，你可能也想包括这一行

IGNORE 1 ROWS

如果您的文件有一个标题行，它看起来就像这样。

LOAD DATA LOCAL INFILE
'c:/temp/some-file.csv'
INTO TABLE your_awesome_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(field_1,field_2 , field_3);
小开

以下是excel文件截图示例:

enter image description here

另存为并选择.csv。

如果您使用notepad++或任何其他记事本打开，您将会看到如下所示的.csv数据屏幕截图。

enter image description here

确保在.csv中删除标题和列对齐，就像在mysql表中一样。 将folder_name替换为文件夹名称

LOAD DATA LOCAL INFILE
“D: / folder_name / myfilename.csv” 进入表格邮件 以'，'结尾的字段 (fname,lname,email, phone);

.

如果大数据，你可以拿着咖啡装!

这就是你所需要的。

小开

修改服务器名、用户名、密码、数据库名、文件路径、表名和你想要插入的数据库字段

<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "bd_dashboard";
//For create connection
$conn = new mysqli($servername, $username, $password, $dbname);


$query = "LOAD DATA LOCAL INFILE
'C:/Users/lenovo/Desktop/my_data.csv'
INTO TABLE test_tab
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(name,mob)";
if (!$result = mysqli_query($conn, $query)){
echo '<script>alert("Oops... Some Error occured.");</script>';
exit();
//exit(mysqli_error());
}else{
echo '<script>alert("Data Inserted Successfully.");</script>'
}
?>
小开

试试这个，对我很管用

    LOAD DATA LOCAL INFILE 'filename.csv' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' IGNORE 1 ROWS;

IGNORE 1 ROWS这里忽略包含字段名的第一行。注意，对于文件名，必须键入文件的绝对路径。

小开

如果你正在使用MySQL工作台(目前是6.3版本)，你可以通过以下方法来做到这一点:

  1. 右键点击“表格”;
  2. 选择表数据导入向导;
  3. 选择您的csv文件并按照说明(JSON也可以使用); 好的一点是，您可以基于您想要导入或加载数据到现有表
    4. 的csv文件创建一个新表

enter image description here

小开

另一个解决方案是使用来自神奇的csvkit套件的csvsql工具。

使用的例子:

csvsql --db mysql://$user:$password@localhost/$database --insert --tables $tablename  $file

该工具可以自动推断数据类型(默认行为)，创建表并将数据插入到创建的表中。--overwrite选项可用于删除已经存在的表。--insert选项-从文件中填充表。

安装套件

pip install csvkit

__abc3 __abc0， __abc1， __abc2

apt-get install python-dev libmysqlclient-dev
pip install MySQL-python
小开

如果你使用Intellij https://www.jetbrains.com/datagrip/features/importexport.html < / p >

enter image description here

小开
我看到一些奇怪的东西。用于转义的字符与用于封闭的字符相同。因此，当引擎发现一个“”时，它不知道该做什么，我认为这就是为什么似乎没有什么东西在正确的地方。 我觉得如果去掉了escape这条线，应该会跑得很棒。如:< / p > 
LOAD DATA INFILE "/home/paul/clientdata.csv"
INTO TABLE CSVImport
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

除非你分析(手动的，视觉的，…)你的CSV和找到哪个字符用于转义。有时是“\”。但如果你没有，就不要使用它。

小开

我使用mysql工作台来做同样的工作。

  1. 创建新模式
  2. 打开新创建的模式
  3. 右键点击“表格”，选择“表格数据导入向导”
  4. 给出CSV文件路径和表名，最后配置列类型，因为向导会根据它们的值设置默认列类型。

注意:查看mysql工作台的日志文件，使用"tail -f [mysqlworkbenchpath]/log/wb*.log"

小开

如何将csv文件导入sql表

文件示例:Overseas_trade_index data CSV文件

步骤:

  1. 需要为overseas_trade_index创建表。

  2. 需要创建csv文件相关的列。

    SQL查询:

    ( id int not null primary key auto_increment,
series_reference varchar (60),
period varchar (60),
data_value decimal(60,0),
status varchar (60),
units varchar (60),
magnitude int(60),
subject text(60),
group text(60),
series_title_1 varchar (60),
series_title_2 varchar (60),
series_title_3 varchar (60),
series_title_4 varchar (60),
series_title_5 varchar (60),
);

  3. Need to connect mysql database in terminal.

    =>show databases;
=>use database;
=>show tables;

  4. Please enter this command to import the csv data to mysql tables.

    load data infile '/home/desktop/Documents/overseas.csv' into table trade_index fields terminated by ',' lines terminated by '\n' (series_reference,period,data_value,status,units,magnitude,subject,series_title1,series_title_2,series_title_3,series_title_4,series_title_5);

  5. Find this overseas trade index data on sqldatabase:

    select * from trade_index;
小开

我用phpmyadmin的简单方法做到了。我遵循了@Farhan的步骤，但所有数据都是在单列中输入的。 我是怎么做的:

  1. 创建CSV文件，删除带列名的标题行。只保存数据。
  2. 我创建了一个列名与csv列匹配的表。
  3. 记住为每一列分配适当的类型。
  4. 我只是选择导入，然后点击导入选项卡。
  5. 在浏览中，我选择了CSV文件，并保持所有选项，因为它是。
  6. 令我惊讶的是，所有数据都成功地导入到相应的列中。
小开
我知道我的回答有点晚了，但我想提一些其他的方法。 最简单的方法是使用命令行。步骤如下:

  1. 以如下命令进入MySQL命令行:

Mysql -u my_user_name -p

  1. 在数据库中创建一个表
use new_schema;


CREATE TABLE employee_details (
id INTEGER,
employee_name VARCHAR(100),
employee_age INTEGER,
PRIMARY KEY (id)
);
  1. 导入CSV文件到表格中。我们可以指定文件路径，也可以将文件存储在MySQL服务器的默认目录中。
LOAD DATA INFILE 'Path to the exported csv file'
INTO TABLE employee_details
FIELDS TERMINATED BY ','
IGNORE 1 ROWS;

这是众多解决方案中的唯一一个，我在教程中找到了它 如果将CSV文件加载到MySQL数据库是你的日常任务，那么最好将这个过程自动化。在这种情况下，你可以使用一些第三方工具，允许你在计划中加载数据

小开

执行MySQL查询导入CSV时，我得到错误 “错误码:1290。MySQL服务器正在使用——secure-file-priv选项运行，所以它不能执行这条语句”< / p >

所以我把文件移到安全文件位置

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv'
INTO TABLE orderdetails.orders
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS

文件的位置是“C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Orders.csv”，这是因为，我将我的CSV文件移动到“secure_file_priv”位置，否则我就会得到上面的错误

您可以使用查询SHOW VARIABLES LIKE "secure_file_priv";来获取secure_file_priv

来源:导入CSV文件到MySQL(查询或使用Workbench)


提交答案