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

user_18711352 0
Viewed 763

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?

load-data-infile mysql-workbench mysql
1 Answers

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;
user_18632290
Related