Error during Load data infile: Data truncated for column user_type at row 392. user_type is empty in row 393

Question

load data infile "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Table-2 events.csv" 
into table events 
fields terminated by "," 
lines terminated by "\r\n" 
ignore 1 lines 
(user_id, occurred_at, event_type, event_name, location, user_type, device);

It repeatedly shows the Data truncated error. Describing table shows that it is allowed to have null values. The datatype is double. At Row 393 onwards the column starts to have null values, which is what is the problem I guess. How do I resolve this?

Initially the column user_type was the last column so I even tried rearranging (thinking its a newline character problem) but that did not resolve it. I even changed the data type to int to see if it would help, and no it didn't

This is how it looks

Field	Type	Null
user_id	double	YES
occurred_at	text	YES
event_type	text	YES
event_name	text	YES
location	text	YES
user_type	double	YES
device	text	YES

This is how the csv file looks at around 392

11768,01-05-2014 08:05,engagement,home_page,France,3,macbook pro
11768,01-05-2014 08:05,engagement,like_message,France,3,macbook pro
11769,01-05-2014 02:37,signup_flow,create_user,United Kingdom,,lenovo thinkpad
11770,01-05-2014 06:07,signup_flow,create_user,Japan,,iphone 5s
11770,01-05-2014 06:07,signup_flow,enter_email,Japan,,iphone 5s
11770,01-05-2014 06:08,signup_flow,enter_info,Japan,,iphone 5s
11770,01-05-2014 06:08,signup_flow,complete_signup,Japan,3,iphone 5s
11770,01-05-2014 06:08,engagement,login,Japan,3,iphone 5s
11770,01-05-2014 06:09,engagement,like_message,Japan,3,iphone 5s

Answer 1

Empty values can be handled using input preprocessing see manual https://dev.mysql.com/doc/refman/8.0/en/load-data.html

for example

load data infile 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\data.txt'
into table t 
fields terminated by ',' 
lines terminated by '\r\n'
#ignore 1 lines 
(user_id
, occurred_at, event_type, event_name, location, @user_type, device
)
set user_type=case when @user_type = '' then null else @user_type end;