load data local infile ERROR 2 file not found

Question

I've been ramming my face against this sql error for about 45 minutes, and I have a feeling it's going to be something silly.

I'm trying to load a .txt file into my database, which is on a server elsewhere. I'm using putty on windows 7.

The sql call I am using is the following: LOAD DATA LOCAL INFILE "C:/Users/Sam/Desktop/students_data.txt" INTO TABLE students;

The response I get is ERROR 2 (HYOOO): File 'C:/Users/Sam/Desktop/students_data.txt' not found (Errcode:2)

If anyone could shed some light on this that'd be extravagant. I already tried switching the / to \ and using single quotes, etc., but nothing seems to work. The file path is copied by shift+clicking the actual file and pasting it.

Solution 1

I have found a solution. First delete the word LOCAL from the sql statement. Second - place your file into MySQL DATA folder usually - bin/mysql/msql5.5.8/data/and your DB with which you are working. It worked for me. You might want to check your MAX_FILE upload number in php.ini file if file is large.

Solution 2

Removing the word LOCAL seemed to work for me; try it out!

Solution 3

Try to type path as C:\\mydir\\myfile.csv i.e. use \\ instead of \

Solution 4

I had this problem too, then I read this:

The file name must be given as a literal string. On Windows, specify backslashes in path names as forward slashes or doubled backslashes

(from http://dev.mysql.com/doc/refman/5.1/en/load-data.html)

I did use the LOCAL keyword, but escaped the file path like this: str_replace('\\','/',$file), then it worked like a charm!

Solution 5

Had this too and solved it by using cmd.exe and found that the filename was mistakenly in the form filename.txt.txt and fixed it.

View more solutions

Comments

[saikiran]

i am working with MySQL and the first solution is okay.. but why we need php.ini file and where can i found php.ini, @prkarpi

[saikiran]

Have done the above procedure still facing the error.. given file permissions to full control and copied file to c>programdata>mysql>mysqlserver 5.6>data>sample . please help.

[saikiran]

my file is nearly has 2 GB of data.

[luckyfox]

if your are in Windows, same way.