Ignore empty rows while loading data into a table

Question

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?

Answer 1

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.

• Ezequiel Tolnay: This would not be helpful for skipping whole rows though.

Answer 2

These are a couple of options to deal with this:

• Create the table with an auto_incremental id; or
• Delete the offending rows from the file prior to loading it (e.g. grep -v '^\( *,\)* *$' file > newfile)

Answer 3

Try below

LOAD DATA INFILE '/var/lib/mysql-files/data_file.csv'
INTO TABLE dbcs_info
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(col1, col2, col3, col4, col5)
SET 
 col1 = NULLIF(col1, ''),
 col2 = NULLIF(col2, ''),
 col3 = NULLIF(col3, ''),
 col4 = NULLIF(col4, ''),
 col5 = NULLIF(col5, '');

col1 = NULLIF(col1, '')

this sets value to null, if Field/column value is empty string.