Load data infile gives me an access denied permissions error. How else can I import a text file into my table in my database?

Question

I want to import a text file that contains data separated by , . I read on several sources that most people use, "LOAD DATA INFILE". So, I figured it would work for me too.

I get this permissions error however when I do so. I ran this command and here is what I got:

LOAD DATA INFILE '/public_html/nyccrash.txt' INTO TABLE nyccrash; 

But it gives me this error:

ERROR 1045(28000): Access denied for user 'username'@'%' (using password: YES)

I read on some other threads that all I had to do was include the full file path and I did but it still didn't work.

Is there another way to import a text file into my table in my database? Using SQL or PHP.

EDIT:

I found this command I can use:

<?php
$row = 1;
$handle = fopen("nyccrash.txt", "r");
echo("<table>");
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    echo("<tr>\r\n");
    foreach ($data as $index=>$val) {
        echo("\t<td>$val</td>\r\n");
    }
    echo("</tr>\r\n");
}
echo("</table>");
fclose($handle);

?>

That allows me to read the data and create a table and print it. I can also use the INSERT INTO table sql command after using the above to collect the data but I'm not sure how to insert the values into the table. That is, loop through the values for insertion. My data in the txt file doesn't not contain the attributes or headers of what's contained. So... I'm a little confused on how to sort the data into the right columns.

Answer 1

In order to load data via LOAD DATA LOCAL INFILE you need two things:

1. FILE privilege. Have a superuser run GRANT FILE ON *.* TO 'username'@'%';.
2. Set local_infile to 1 in my.cnf. To avoid having to restart mysql, have a superuser run SET GLOBAL local_infile=1;.

CAVEAT : Both of these things would be deemed a security breach.

Answer 2

I made sure I gave my txt file permissions: chmod 711

Then I used LOAD DATA LOCAL INFILE 'nyccrash.txt' INTO TABLE nyccrash FIELDS TERMINATED BY ','; and it worked.