MYSQL使用加载数据文件从csv导入数据

I am importing some data of 20000 rows from a CSV file into Mysql.

我正在从CSV文件中导入20000行数据到Mysql中。

Columns in the CSV are in a different order than MySQL table's columns. How to automatically assign columns corresponding to Mysql table columns?

CSV中的列的顺序与MySQL表的列不同。如何自动分配与Mysql表列对应的列?

When I execute

当我执行

LOAD DATA INFILE'abc.csv' INTO TABLE abc

this query adds all data to the first column.

该查询将所有数据添加到第一列。

Please suggest auto syntax for importing data to Mysql.

请建议将数据导入Mysql的自动语法。

9 个解决方案

#1

You can use LOAD DATA INFILE command to import csv file into table.

可以使用LOAD DATA INFILE命令将csv文件导入到表中。

Check this link MySQL - LOAD DATA INFILE.

检查这个链接MySQL加载数据文件。

LOAD DATA LOCAL INFILE 'abc.csv' INTO TABLE abc
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES
(col1, col2, col3, col4, col5...);

#2

You probably need to set the FIELDS TERMINATED BY ',' or whatever the delimiter happens to be.

您可能需要设置以'，'或任何分隔符为结尾的字段。

For a CSV file, your statement should look like this:

对于CSV文件，您的语句应该如下所示:

LOAD DATA INFILE 'data.csv' INTO TABLE tbl_name
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

#3

Before importing the file, you must need to prepare the following:

在导入档案前，你必须准备以下文件:

• A database table to which the data from the file will be imported.
• 从文件中导入数据的数据库表。
• A CSV file with data that matches with the number of columns of the table and the type of data in each column.
• 一个CSV文件，其数据与表的列数和每个列中的数据类型匹配。
• The account, which connects to the MySQL database server, has FILE and INSERT privileges.
• 该帐户连接到MySQL数据库服务器，具有文件和插入特权。

Suppose we have following table :

假设我们有下表:

CREATE TABLE USING FOLLOWING QUERY :

使用以下查询创建表:

CREATE TABLE IF NOT EXISTS `survey` (
  `projectId` bigint(20) NOT NULL,
  `surveyId` bigint(20) NOT NULL,
  `views` bigint(20) NOT NULL,
  `dateTime` datetime NOT NULL
);

YOUR CSV FILE MUST BE PROPERLY FORMATTED FOR EXAMPLE SEE FOLLOWING ATTACHED IMAGE :

您的CSV文件必须正确格式化，例如，请参阅附件中的图片:

If every thing is fine.. Please execute following query to LOAD DATA FROM CSV FILE :

如果一切顺利。请执行以下查询从CSV文件加载数据:

NOTE : Please add absolute path of your CSV file

注意:请添加您的CSV文件的绝对路径

LOAD DATA INFILE '/var/www/csv/data.csv' 
INTO TABLE survey 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

If everything has done. you have exported data from CSV to table successfully

如果一切都完成了。您已经成功导出了从CSV到表的数据。

#4

Syntax:

语法:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL]
INFILE 'file_name' INTO TABLE `tbl_name`
CHARACTER SET [CHARACTER SET charset_name]
FIELDS [{FIELDS | COLUMNS}[TERMINATED BY 'string']] 
[LINES[TERMINATED BY 'string']] 
[IGNORE number {LINES | ROWS}]

See this Example:

看这个例子:

LOAD DATA LOCAL INFILE
'E:\\wamp\\tmp\\customer.csv' INTO TABLE `customer`
CHARACTER SET 'utf8'
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

#5

Insert bulk more than 7000000 record in 1 minutes in database(superfast query with calculation)

在数据库中1分钟内插入超过7000000条记录(计算查询超快)

mysqli_query($cons, '
    LOAD DATA LOCAL INFILE "'.$file.'"
    INTO TABLE tablename
    FIELDS TERMINATED by \',\'
    LINES TERMINATED BY \'\n\'
    IGNORE 1 LINES
    (isbn10,isbn13,price,discount,free_stock,report,report_date)
     SET RRP = IF(discount = 0.00,price-price * 45/100,IF(discount = 0.01,price,IF(discount != 0.00,price-price * discount/100,@RRP))),
         RRP_nl = RRP * 1.44 + 8,
         RRP_bl = RRP * 1.44 + 8,
         ID = NULL
    ')or die(mysqli_error());
$affected = (int) (mysqli_affected_rows($cons))-1; 
$log->lwrite('Inventory.CSV to database:'. $affected.' record inserted successfully.');

RRP and RRP_nl and RRP_bl is not in csv but we are calculated that and after insert that.

RRP和RRP_nl和RRP_bl不在csv中，但是我们会计算它，然后插入它。

#6

let suppose you are using xampp and phpmyadmin

假设您正在使用xampp和phpmyadmin

you have file name 'ratings.txt' table name 'ratings' and database name 'movies'

你有文件名评级。txt'表名'ratings'和数据库名'movies'

if your xampp is installed in "C:\xampp\"

如果你的xampp安装在“C:\xampp\”中

copy your "ratings.txt" file in "C:\xampp\mysql\data\movies" folder

复制你的“评级。txt"文件在"C:\xampp\mysql\数据\影片"文件夹

LOAD DATA INFILE 'ratings.txt' INTO TABLE ratings FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Hope this can help you to omit your error if you are doing this on localhost

希望这能帮助您在本地主机上执行此操作时忽略错误

#7

You can load data from a csv or text file. If you have a text file with records from a table, you can load those records within the table. For example if you have a text file, where each row is a record with the values for each column, you can load the records this way.

您可以从csv或文本文件加载数据。如果您有一个包含来自表的记录的文本文件，您可以在表中加载这些记录。例如，如果您有一个文本文件，其中每一行都是一个记录，每个列的值都是记录，那么您可以以这种方式加载记录。

table.sql

id //field 1

name //field2

table.txt

1,peter

2,daniel

...

--example on windows

——windows上的例子

LOAD DATA LOCAL INFILE 'C:\\directory_example\\table.txt'
INTO TABLE Table
CHARACTER SET UTF8
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'; 

#8

If you are running LOAD DATA LOCAL INFILE from the windows shell, and you need to use OPTIONALLY ENCLOSED BY '"', you will have to do something like this in order to escape characters properly:

如果您正在运行从windows shell中加载的本地文件，并且您需要使用“”“”，您将不得不这样做，以便正确地转义字符:

"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysql" -u root --password=%password% -e "LOAD DATA LOCAL INFILE '!file!' INTO TABLE !table! FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"^""' LINES TERMINATED BY '\n' IGNORE 1 LINES" --verbose --show-warnings > mysql_!fname!.out

#9

I was getting Error Code: 1290. The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

我得到了错误代码:1290。MySQL服务器使用- securefile -priv选项运行，因此无法执行此语句。

This worked for me on windows 8.1 64 bit using wampserver 3.0.6 64bit.

这对我来说在使用wampserver 3.0.6 64位的windows 8.1 64位上是有效的。

Edited my.ini file from C:\wamp64\bin\mysql\mysql5.7.14

我的编辑。ini文件C:\ wamp64 \ bin \ mysql \ mysql5.7.14

Delete entry secure_file_priv c:\wamp64\tmp\ (or whatever dir you have here)

删除输入secure_file_priv c:\wamp64\tmp\(或您在这里拥有的任何dir)

Stopped everything -exit wamp etc.- and restarted everything; then punt my cvs file on C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur (the last dir being my database name)

停止一切-退出wamp等-重新启动一切;然后把我的cvs文件放在C:\wamp64\bin\mysql\mysql5.7.14\data\u242349266_recur(最后一个目录是我的数据库名)

executed LOAD DATA INFILE 'myfile.csv'

已执行的加载数据文件“myfile.csv”

INTO TABLE alumnos

成表alumnos

FIELDS TERMINATED BY ','

字段被”、“终止

ENCLOSED BY '"'

封闭”的

LINES TERMINATED BY '\r\n'

行终止由“\ r \ n”

IGNORE 1 LINES

忽略1行

... and VOILA!!!

…瞧! ! !