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 …

insert image description here

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:

insert image description here

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

insert image description here

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';

insert image description here

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!

You may also be interested in:

Problems and solutions of error 08001 when linking to MySQL in IDEA and no table display after successful connection
Problems and solutions for IDEA connecting to MySQL
Solution to the problem that synchronous replication errors cannot be skipped in MySQL5.6 GTID mode
Solution to ONLY_FULL_GROUP_BY error in Mysql5.7 and above
MySQL Error 1290 (HY000) Solution
Detailed explanation of the solution to the error in creating a user and granting permissions in mysql8.0
Django restarts after reinstalling MySQL and reports an error: How to solve the problem of No module named 'MySQLdb'
Solution to the error message "java.sql.SQLException: Incorrect string value:'\xF0\x9F\x92\xA9\x0D\x0A...'" when storing emoticons in MySQL
Description and solution of MySQLdb error when installing Python
How to solve the error when connecting to MySQL in Linux: Access denied for user 'root'@'localhost'(using password: YES)
Teach you how to solve the error when storing Chinese characters in MySQL database

<<: Building a selenium distributed environment based on docker

>>: Common structural tags in XHTML