3

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?

Progman
  • 16,827
  • 6
  • 33
  • 48
Anna
  • 41
  • 1
  • 3

2 Answers2

0

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;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

Check the CSV column the error point to, and make sure the values inside are the same as the int data type in your column

okunade yusuf
  • 9
  • 3