CODEWITHSUNDEEP
mysqlmysql-workbenchload-data-infile
Anna
Anna

Reputation: 71

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

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?

Upvotes: 3

Views: 4885

Answers (2)

achur00
achur00

Reputation: 9

The reason for a data truncate error is usually when the value or data type that was supposed to be inputted in the expected column wasn't what is detected. Check the CSV you are trying to upload carefully to check if the value that corresponds to the EmpID is really an 'INT' as you set it to be.

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522741

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;

Upvotes: 0

Related Questions