CODEWITHSUNDEEP
mysqlbulk-loadmysql-error-1264
Abhishek Yadav
Abhishek Yadav

Reputation: 1

Why I am facing an error while Loading Data Infile Error code: 1264

0 6 20:30:00 LOAD DATA INFILE 'D:/BulkDataUpload/CovidDeaths.csv' into table coviddeaths FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS Error Code: 1264. Out of range value for column 'total_cases_per_million' at row 2036 0.047 sec

Try to upload the bulk data 101028.926

CREATE TABLE IF NOT EXISTS coviddeaths(

iso_code VARCHAR(20),   

continent VARCHAR(20),  

location VARCHAR(40),   

\`date\` date,

population int DEFAULT NULL,

total_cases int DEFAULT NULL,

new_cases int DEFAULT NULL, 

new_cases_smoothed decimal(10,5) DEFAULT NULL,  

total_deaths int DEFAULT NULL,

new_deaths int DEFAULT NULL,    

new_deaths_smoothed decimal(10,5) DEFAULT NULL,

total_cases_per_million decimal(12,6),

new_cases_per_million decimal(10,5) DEFAULT NULL,   

new_cases_smoothed_per_million decimal(10,5) DEFAULT NULL,  

total_deaths_per_million decimal(10,5) DEFAULT NULL,    

new_deaths_per_million decimal(10,5) DEFAULT NULL,  

new_deaths_smoothed_per_million decimal(10,5) DEFAULT NULL, 

reproduction_rate decimal(10,5) DEFAULT NULL,   

icu_patients int DEFAULT NULL,

icu_patients_per_million decimal(10,5) DEFAULT NULL,    

hosp_patients int DEFAULT NULL,

hosp_patients_per_million decimal(10,5) DEFAULT NULL,   

weekly_icu_admissions decimal(10,5) DEFAULT NULL,   

weekly_icu_admissions_per_million decimal(10,5) DEFAULT NULL,   

weekly_hosp_admissions decimal(10,5) DEFAULT NULL,

weekly_hosp_admissions_per_million decimal(10,5) DEFAULT NULL

);

Upvotes: 0

Views: 50

Answers (1)

DrJay
DrJay

Reputation: 469

It looks like the value in that column at row 2036 is out of bounds.

The 'total_cases_per_million' column is defined with the data type decimal(12,6). If there is a number larger, it will fail.

Re-define the column, say as data type decimal(15,6)

If it is a decimal that is too long, then round it e.g.

In SQL

total_cases_per_million DECIMAL(12,6),
-- ...
SET total_cases_per_million = ROUND(@total_cases_per_million, 6)

The last way is to check the .csv data and optionally correct it.

Upvotes: 0

Related Questions