Error Code: 1265. Data truncated for column at row 1 when I tried to input csv file to MySQL

Question

I created a table with a query:

CREATE TABLE Department (
EmpID INT NOT NULL,
Designation VARCHAR(80) NOT NULL,
Department VARCHAR(80) NOT NULL,
PRIMARY KEY(EmpID));

SELECT * FROM Department;

I enter data with a query:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Department.csv'
INTO TABLE Department
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\r\n';

But a message appears from the output:

Error Code: 1265. Data truncated for column 'EmpID' at row 1

Anyway, the EmpID value is no more than 6 digits and the value is positive.

Is there any solution so I can insert data from csv file?

Answer 1

The error message seems to be implying that there is some data in the CSV file which does not fit into an INT column. You could use this table definition:

CREATE TABLE Department (
    EmpID VARCHAR(100) NOT NULL,
    Designation VARCHAR(80) NOT NULL,
    Department VARCHAR(80) NOT NULL,
    PRIMARY KEY(EmpID)
);

Then populate the table via LOAD DATA and check for wide data in EmpID via:

SELECT *
FROM Department
WHERE LENGTH(EmpID) > 6;