0

I'm trying to load a CSV file into a database table, but having some issues.

LOAD DATA LOCAL INFILE 'Report_1.csv' 
INTO TABLE report_1 
Fields terminated by ',' ENCLOSED BY '\"' 
LINES terminated by '\n\r'(
company,key_code,dlr_code,rep_code,dlr_name,first_name,last_name)

This gives me the following error: File 'Report_1.csv' not found (Errcode: 2) So, I tried removing the LOCAL from the SQL above and get this error: Access denied for user 'myusername'@'localhost' (using password: YES) From what I understand, this means I don't have grant privileges for File, but when I do a "show grants;" I get: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myusername'@'localhost' WITH GRANT OPTION Isn't this telling me I should already have all grant privileges? Any ideas?

2
Contributors
4
Replies
5
Views
6 Years
Discussion Span
Last Post by roachae
0

Is LOAD DATA LOCAL enabled on your server?

If not (or are not sure) refer to:
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

specifically:

For the mysql command-line client, enable LOAD DATA LOCAL by specifying the --local-infile[=1] option, or disable it with the --local-infile=0 option. For mysqlimport, local data file loading is off by default; enable it with the --local or -L option. In any case, successful use of a local load operation requires that the server permits it.

0

From what I can find, it doesn't look like it. I'm not a sys admin, I'm just a programmer. The sys admin people I'd need to check this for me to be sure are not currently available. I'm not able to make those changes on the server without their knowledge.

What I don't understand is the different errors I'm getting when I do or don't include LOCAL in my query statement. To be more specific with my query (I simplified it earlier), the full path of the file is /tmp/Report_1.csv and I do use this whole pathname in the query statement, and still get the errors I mentioned above with or without including LOCAL.

0

IF you use LOCAL, I expect the file to be on the same machine where the server is running. So if you are on machineA connecting a server that is on machineB, then your file should exist in /tmp/Report1.csv in machineB, not A. IF this is not the case you should be getting a file not found.

As for the other error, you may have the privilege, but the feature still needs to be enabled on the server.

0

I got something, thanks to your last reply. I put the LOCAL back into my query and changed the pathname of my file to C:\TEMP\Report_1.csv. This did what I want, but might not do for what I'm working towards. It's very possible I need to have the load data infile feature enabled on the server, because once I get what I'm doing completed, that's where the files will definitely end up having to be. I'll make some notes and pass things on to the sys admin guys and see what they have to say. Thanks!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.