从定界文件将数据加载到mysql表中时跳过错误行 [英] Skip error lines while loading data to mysql table from delimited file

问题描述

我正在使用以下查询将文本文件中的数据加载到mysql表中:

I am loading data from text file to mysql table using following query:

LOAD DATA INFILE "myFile.csv"
INTO TABLE some_table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (admin,consumer);

但是，当我的文件包含一些错误行时，无法跳过该行，整个过程就在该位置停止.我的意思是如果我的文件格式是:

But when my file contains some error lines it is not able skip that line and the whole process stops at that place. I mean if my file format like :

----------
sankr : kumar
----------
ramesh:rao
----------
new users add here
----------
sri : vennla
----------
anu : bhavya
----------

我必须通过跳过新用户在此处添加"行进行加载.该怎么办?

I have to load by skipping the line "new users add here". How can do this?

推荐答案

您要查找的关键字是IGNORE.

如:

LOAD DATA INFILE "myFile.csv" IGNORE
INTO TABLE some_table
COLUMNS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n' (admin,consumer);

这篇关于从定界文件将数据加载到mysql表中时跳过错误行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！