3

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?

3 Answers 3

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.

1
  • This would not be helpful for skipping whole rows though. Commented Apr 22, 2016 at 6:32
0

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)
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.