Mysql – Ignore empty rows while loading data into a table

importMySQL

I created a table (tbldemo) with 22 columns using a CREATE TABLE statement in MySQL. When trying to insert the data from a .csv file using a LOAD DATA INFILE statement, I get this error:

Error Code: 1261. Row 11513 doesn't contain data for all columns

How can I ignore the rows whose entire row is null or empty?

This is what I used to load the data from .csv:

LOAD DATA INFILE 'D:/Singapore/rau_sales_order.csv' INTO TABLE tbldemo
 FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'  LINES TERMINATED BY '\r\n'
 IGNORE 1 LINES;

I don't want to open the .csv file and clean the data from the file by filtering it for null. Instead, I want to do the filtering directly in the query. Is there any way to achieve this?

Best Answer

load data local infile 'C:\\temp\\abc.csv'
into table table_name
fields terminated by ',' ignore 1 lines 
(@col1,@col2,@col3,@col4)
set 
table.column_name = @col1,
table.column_name = @col2,
table.column_name = @col3,
table.column_name = @col4;

(@col1,.....) extend the number of columns to your actual number of columns in csv file. and relate these column numbers to table columns. It will be done.

Related Question