4 solutions to mysql import csv errors

This is to commemorate the 4 pitfalls I stepped on today...

Pitfall 1: Local’s fault

Error:
ERROR 3948 (42000): Loading local data is disabled; this must be enabled on both the client and server sides
Fix: Remove local

mysql> load data infile …

Pitfall 2: Wrong csv address

Error:
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
Correction: Put the correct address in csv and find out the address of the security file first:

mysql> load data infile … 

Then put the csv file to be imported into the folder:

mysql>load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/user_info_utf.csv' …

Note: \ must be changed to /

Pitfall 3: Wrong file format

Error:
ERROR 1366 (HY000): Incorrect integer value: '\FEFF1' for column 'userId' at row 1

Correction: To modify the file format, first open it with Excel, save as, save type - utf8, tools - web options - encoding utf8, replace the original file:

Open it with Notepad, save as, save type - all files, encoding - utf8, replace the original file:

Pitfall 4: Null value error

Error:
ERROR 1292 (22007): Incorrect date value: '' for column 'birth' at row 18
Fixes:
Modify sql_mode:

mysql> set @@sql_mode=ANSI;

Notes:
ANSI mode: A relaxed mode that changes syntax and behavior to make it more consistent with standard SQL. Verify the inserted data. If it does not conform to the defined type or length, adjust the data type or truncate it before saving, and issue a warning. If there is an error message when importing null values ​​from csv, you can first set sql_mode to ANSI mode, so that you can insert data. For field values ​​with no data, the database will replace them with NULL values.

Finally climbed out of the pit:

mysql>load data infile 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/user_info_utf.csv' into table data.userinfo fields terminated by ',' optionally enclosed by '"' escaped by '"'lines terminated by '\r\n'; 

Imported successfully! So touching!

This concludes this article on 4 solutions to MySQL import errors in CSV. For more information on MySQL import errors in CSV, please search previous articles on 123WORDPRESS.COM or continue browsing the following related articles. I hope you will support 123WORDPRESS.COM in the future!