CODEWITHSUNDEEP
mysql
F.L.P.M. Jayawardena
F.L.P.M. Jayawardena

Reputation: 23

How to import data of a text file skipping the Primary Key column in to a MySQL Database

I want to import data from a text file into MySQL Database, Here my requirement is to auto increment the ID (Also the Primary Key) column without importing it from the text file. I also have set ID column as Primary Key, Non-Negative and Auto-Incrementing.

The code I have used

use vehicledetails;
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Test.txt'
INTO TABLE vehiclespeeddetail
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n';

When I run the above code I get an error saying

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

My Text file is of as follows

enter image description here

I want an output with an Auto Incrementing ID Column by importing data from the above text file:

enter image description here

Upvotes: 0

Views: 352

Answers (1)

danblack
danblack

Reputation: 14666

With an explicit list of columns that excludes the Auto increment column:

use vehicledetails;
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Test.txt'
INTO TABLE vehiclespeeddetail
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\r\n'
(`Date`, `Time`, `NoPlate`, `CurrentSpeed`, `MaxSpeed`, `OverspeedDetection`);

Upvotes: 1

Related Questions