Load data infile does nothing

Question

I'm kinda new to SQL. I am trying to add data to an alredy created table, the csv looks like these, it is 132645 lines long:

iso_code;continent;location;date;population;total_cases;new_cases;new_cases_smoothed;total_deaths;new_deaths;new_deaths_smoothed;total_cases_per_million;new_cases_per_million;new_cases_smoothed_per_million;total_deaths_per_million;new_deaths_per_million;new_deaths_smoothed_per_million;reproduction_rate;icu_patients;icu_patients_per_million;hosp_patients;hosp_patients_per_million;weekly_icu_admissions;weekly_icu_admissions_per_million;weekly_hosp_admissions;weekly_hosp_admissions_per_million
AFG;Asia;Afghanistan;24/02/2020;398354280;50;50;NULL;NULL;NULL;NULL;126;126;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;
AFG;Asia;Afghanistan;25/02/2020;398354280;50;0;NULL;NULL;NULL;NULL;126;0;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;
AFG;Asia;Afghanistan;26/02/2020;398354280;50;0;NULL;NULL;NULL;NULL;126;0;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;
AFG;Asia;Afghanistan;27/02/2020;398354280;50;0;NULL;NULL;NULL;NULL;126;0;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;
AFG;Asia;Afghanistan;28/02/2020;398354280;50;0;NULL;NULL;NULL;NULL;126;0;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;NULL;

And my sql query is this:

LOAD DATA LOCAL INFILE 'C:\\Users\\Usuario\\Desktop\\CovidDeaths.csv' INTO TABLE coviddeaths
columns terminated by ';'
LINES TERMINATED BY '\r\n';

I have some alredy uploaded data to the coviddeaths table using the import wizards but the upload was really slow, so tried to finish importing using the LOAD DATA INFILE query. To see how many lines have been alredy uploaded I used

select count(*) from coviddeaths;

And threw:

count(*)
11312

When I try to run the LOAD DATA LOCAL INFILE query, nothing happens. The mouse pointer spins for a second like it tries to run it but it doesnt add any additional row. It doesnt throw any errors like the ones I have read about Error Code: 1148. The used command is not allowed with this MySQL version. I also tried to place the csv file in the Uploads folder, where my Secure File Priv option pointed to in my.ini:

# Secure File Priv.
secure-file-priv="C:/ProgramData/MySQL/MySQL Server 8.0/Uploads"

It just does nothing. Any ideas?

Thanks in advance!

EDIT 2: Database Schema:

Check this out if it helps @LuisGarcíaTíscar https://stackoverflow.com/questions/14127529/mysql-import-data-from-csv-using-load-data-infile — ishant kaushik, Nov 12 '21 at 20:00
if your csv file contains header then you probably need to ignore header. LOAD DATA INFILE '/tmp/test.txt' INTO TABLE test IGNORE 1 LINES; — ishant kaushik, Nov 12 '21 at 20:02
@ishantkaushik Alredy tried IGNORE 1 LINES, still does nothing... Thanks anyway! — , Nov 12 '21 at 20:06

score 0 · Accepted Answer · answered Nov 12 '21 at 21:48

The error comes from the fact that both client and server must enable an option to allow local datafiles to be imported. If you use the command-line client, you would need to open the client like this:

mysql --local-infile -u root -p

In addition, the MySQL Server would need to be configured with the local-infile option.

The secure-file-priv is not needed, because that controls importing or exporting files on the server, not from the client.

See also ERROR 1148: The used command is not allowed with this MySQL version or my answer to "mysql 8.0" local infile fails I tried and display the settings. Could it be permission issues?

The error message is unusually obscure. MySQL error messages are usually more accurate and informative. I logged a bug in 2019 to request an improved error message, and they did fix it in MySQL 8.0.

Thanks! Accessing through the cmd with --local-infile did the trick. is there any way to do the same with MySQL WorkBench? — , Nov 12 '21 at 22:03
https://stackoverflow.com/questions/31450389/connect-with-local-infile-option-in-mysql-workbench — Bill Karwin, Nov 12 '21 at 22:16

Load data infile does nothing

1 Answers1