Last Updated: February 25, 2016
·
6.282K
· frisocki

Mysql - Load data infile ignore blank lines

Although there is no direct way to ignore blank lines when using load data infile, it can be achieved by altering the data file. If you have control over the data file, you can prefix the lines you want to load with a character sequence. Only the lines starting with . will be loaded.

Salutation|Name|Occupation
. Mr.|Yellow|Circus Clown

. Mrs.|Green|Gardener

Then in load data file ...

load data local infile 'data.txt'
into table table_name
fields terminated by '|'
optionally enclosed by '"'
lines starting by '. '
ignore 1 lines(
    @salutation,
    @name,
    @occupation
)

The prefix can easily be filtered out with:

awk '/^\. / {sub("^\. ", ""); print;}'