Error Code: 1262. Row 1 was truncated; it contained more data than there were input columns

user_15764103 0
Viewed 287

Trying to load data from CSV into a MySQL table. However, I'm getting the following error:

"Error Code: 1262. Row 1 was truncated; it contained more data than there were input columns"

Here is my SQL:

CREATE DATABASE db;
USE db;

CREATE TABLE covid (
    d INT NOT NULL,
    mo INT NOT NULL,
    yr YEAR NOT NULL,
    cases INT NOT NULL, 
    deaths INT NOT NULL, 
    country VARCHAR(255) NOT NULL,
    country_code VARCHAR(255) NOT NULL, 
    pop INT NOT NULL,
    continent VARCHAR(255) NOT NULL,
    rate DECIMAL(10, 5) NOT NULL
);

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/corgis-covid.csv'
INTO TABLE covid
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(d, mo, yr, cases, deaths, country, country_code, pop, continent, rate);

SELECT * FROM covid

And here is a portion of my CSV data:

d,mo,yr,cases,deaths,country,country_code,pop,continent,rate,
5,11,2020,121,6,Afghanistan,AFG,38041757,Asia,3.74588377,
4,11,2020,86,4,Afghanistan,AFG,38041757,Asia,3.78268543,
3,11,2020,95,3,Afghanistan,AFG,38041757,Asia,3.78794281,
2,11,2020,132,5,Afghanistan,AFG,38041757,Asia,3.76691329,
1,11,2020,76,0,Afghanistan,AFG,38041757,Asia,3.57501889,
31,10,2020,157,4,Afghanistan,AFG,38041757,Asia,3.55398937,

Please help. Thank you!

load-data-infile csv mysql sql
1 Answers

rate DECIMAL(10, 5) NOT NULL

First, the rate values contain 8 digits to the right of the decimal place. So you need to increase the column size to something larger, like DECIMAL(10,8).

LINES TERMINATED BY '\n'

You might want to verify lines are actually terminated by just \n. As it's often o/s dependent.

Anyway, as @Paul T. said in the comments, the extra trailing comma is causing the import to fail. The better solution is to fix the errors in the data file. However, you might also try changing the line terminator to include the trailing comma. Using LINES TERMINATED BY ',\r\n' seemed to resolve the error in my tests using Windows:

LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/corgis-covid.csv'
INTO TABLE covid
FIELDS TERMINATED BY ','
LINES TERMINATED BY ',\r\n'
IGNORE 1 ROWS
(d, mo, yr, cases, deaths, country, country_code, pop, continent, rate);
user_88952920
Related