LOAD DATA INFILE – MySQL – Errors

Rows from a text file are quickly read into a table using the LOAD DATA statement. Depending on whether the LOCAL modifier is specified, the file can be read from either the server host or the client host. Data interpretation and error handling are also impacted by LOCAL.

Nevertheless, after resolving one problem, I continued encountering new ones in the beginning.

First, let’s begin with the syntax and example.
LOAD DATA INFILE <fileName> INTO TABLE <tableName>;
LOAD DATA LOCAL INFILE <fileName> INTO TABLE <tableName>;

Example:
LOAD DATA INFILE ‘data.csv’ INTO TABLE MyTable;

The problems you might run into:

1290 – The MySQL server is running with the –secure-file-priv option so it cannot execute this statement
You can experience this problem if the file you want to import was placed in a specific folder on your local or remote workstation. It’s because MySQL only permits uploads from its default directory, which is often C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\.

The permitted file location can be verified using the statement below.

SHOW VARIABLES LIKE “secure_file_priv”;
+————————-+—————————————————————————-+
| Variable_name | Value |
+————————+—————————————————————————–+
| secure_file_priv | C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\|
+————————+—————————————————————————–+
Place your file in that location or modify the file location as per your requirement in my.cnf (Mac, Linux) or my.ini (Windows) file. You cannot modify it using SQL statement. If you attempt you will receive the following error.

ERROR 1238 (HY000): Variable ‘secure_file_priv’ is a read only variable

> 3948 – Loading local data is disabled; this must be enabled on both the client and server sides
If you are receiving this error, then check the default value of “local_infile” variable using the below statement.

SHOW VARIABLES LIKE “local_infile”;
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| local_infile | OFF |
+———————–+———–+

> > 2 – File ‘data.txt’ not found (Errcode: 2)
This error message is typically displayed when an improper file location is used. The location of your Windows files is – “C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\”

You have to change the backslash (“\”) to forward slash (“/”).

Your SQL statement should be –
LOAD DATA LOCAL INFILE “C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/data.csv” INTO TABLE MyTest;