access denied for load data infile in MySQL

Ensure your MySQL user has the FILE privilege granted.

If you are on shared web hosting, there is a chance this is blocked by your hosting provider.

I found easy one if you are using command line

Login asmysql -u[username] -p[password] --local-infile

then SET GLOBAL local_infile = 1;

select your database by use [db_name]

and finally LOAD DATA LOCAL INFILE 'C:\\Users\\shant\\Downloads\\data-1573708892247.csv' INTO TABLE visitors_final_test FIELDS TERMINATED BY ','LINES TERMINATED BY '\r \n' IGNORE 1 LINES;

If you are trying this on MySQL Workbench,

Go to connections -> edit connection -> select advanced tab

and add OPT_LOCAL_INFILE=1 in the 'Others' text field.

Now restart the connection and try.

I discovered loading MySQL tables can be fast and painless (I was using python / Django model manager scripts):

1) create table with all columns VARCHAR(n) NULL e.g.:

mysql> CREATE TABLE cw_well2( api VARCHAR(10) NULL,api_county VARCHAR(3) NULL);


 2) remove headers (first line) from csv, then load (if you forget the LOCAL, you’ll get “#1045 - Access denied for user 'user'@'localhost' (using password: YES)”):

mysql> LOAD DATA LOCAL INFILE "/home/magula6/cogswatch2/well2.csv" INTO TABLE cw_well2 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'     -> ; Query OK, 119426 rows affected, 19962 warnings  (3.41 sec)


 3) alter columns:

mysql> ALTER TABLE cw_well2 CHANGE spud_date spud_date DATE;


mysql> ALTER TABLE cw_well2 CHANGE latitude latitude FLOAT;

voilà!

I was trying to insert data from CSV to MYSQL DB using python. You can try the below method to load data from CSV to Database.

1. Make a connection with the Database using pymysql or MySQL.connector any library you want in python.
2. Make Sure you are able to use the in-line while connecting for that while providing host, user, and password try to add local_inline=True.

Skipping to load data part. sql = f'''LOAD DATA LOCAL infile "filename.csv" INTO TABLE schema.tablename FILED TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'''' Note: If you have column names in CSV, use IGNORE ROW 1 LINES. The execute the sql by: cursor.execute(sql) conn.commit() conn.close()

It probably means that the password you supplied for 'user'@'localhost' is incorrect.