CODEWITHSUNDEEP
mysqlfile-ioloadlocal
aCarella
aCarella

Reputation: 2568

Error When Using LOAD DATA LOCAL INFILE

What I Am Trying To Do:

I am trying to use LOAD DATA LOCAL INFILE. I have a csv file on a server I work on that I want to load onto a table in a MySQL database. I have used the following:

LOAD DATA LOCAL INFILE '/var/www/load_data_test.csv'
INTO TABLE name_age 
FIELDS TERMINATED BY ',' 
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

What My Problem Is:

My problem is an error code. When I run this in MySQL (HeidiSQL), I am getting the following error message:

SQL Error (2): File '\var\www\load_data_test.csv' not found (Errcode: 2)

What I Have Tried:

I googled this problem and all the links that I clicked on lead me to try the following:

  1. Setting the permissions on the file to Octal: 7777. I have given Read, Write, and Execute permissions to Owner, Group and Others. This did not work. I was still given the same error message as above.

  2. Taking out the LOCAL word in the MySQL statement. I Changed the first line of the SQL command to LOAD DATA INFILE '/var/www/load_data_test.csv'. This did not work, and produced the following error message:
    SQL Error (29): File 'var/www/load_data_test.csv' not found (Errcode: 13).

  3. Switching the owner under the file's properties to root. This does not work. When I try to do this, I get the error message Command 'chown "root" "load_data_test.csv"' failed with return code 1 and error message chown: changing ownership of 'load_data_test.csv': Opertion not permitted..

Possible Constraints

I am 100% sure that the CSV file I am trying to load onto the database table is on the server, so I am confused as to why it won't even recognize it in the first place. Any help would be greatly appreciated.

Upvotes: 0

Views: 2635

Answers (1)

aCarella
aCarella

Reputation: 2568

I just found a quick solution to my own problem. Posting it up just in case anyone who encounters this needs a solution too.

In HeidiSQL, you can upload CSV files through the GUI, and when you do, it gives you the syntax for the file upload (the GUI also uses the LOAD DATA LOCAL INFILE command). I was able to get the same CSV file, on my own computer, to upload into the MySQL table on my server using the following syntax:

LOAD DATA LOCAL INFILE 'C:\\Users\\acarella\\Desktop\\load_data_test.csv' 
INTO TABLE `ug_warehouse`.`name_age` 
FIELDS TERMINATED BY ',' 
IGNORE 1 LINES (`name`, `age`);

This is probably my first time answering a question on StackOverflow, so if it is unorthodox to answer your own question then I will avoid doing it in the future.

-Anthony

Upvotes: 1

Related Questions