Populating a MySQL table with data from a csv file

[Venom Rush]

Hi everyone

I have a csv file with data such as first name, surname, tel etc and I'd like to populate a table inside my MySQL database automatically.

So far I have this but I keep getting an access denied error and I don't know why:

<?php

$server = 'localhost';
$user = 'username';
$password = 'password';

// open connection to MySQL server
$connection = mysql_connect($server,$user,$password)
or die ('Unable to connect!');
	
//select database
mysql_select_db('table_name') or die ('Unable to select database!');

// clear the table down
$sql = "TRUNCATE TABLE table_name";

// run the first query to clear table
mysql_query($sql) or die(mysql_error());

// set up query to import data, assuming the
// csv file is at the top level of the C drive
$sql = "LOAD DATA INFILE 'users.txt'
        INTO TABLE table_name
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '\"'
        LINES TERMINATED BY '\r\n'";

// run the query to load the data
mysql_query($sql) or die(mysql_error());

?>

Any help would be greatly appreciated. Virtual cookies are up for grabs as a reward ;)

[nav33n]

Your code works fine on my computer. You are probably getting 'access denied' because you don't have enough privileges for the mysql user. Use grant to grant permissions for the users.

[Venom Rush]

Your code works fine on my computer. You are probably getting 'access denied' because you don't have enough privileges for the mysql user. Use grant to grant permissions for the users.

Ok, I'll give that a try and let you know. ;)

[nav33n]

Oh, btw, users.txt should be in mysql/data/dbname/ ! That's where it looks.

[Venom Rush]

Ok, it can't be grant access because I'm using administrator. I thought it might be my windows firewall preventing access but I've turned it off and I'm still getting the access denied error. Are there any other possibilities?

I've tried this on 2 separate servers not associated with one another at all and I'm getting the same access denied error.

[Venom Rush]

Oh, btw, users.txt should be in mysql/data/dbname/ ! That's where it looks.

Ok, but that won't cause the access error would it?

[nav33n]

Ok, it can't be grant access because I'm using administrator. I thought it might be my windows firewall preventing access but I've turned it off and I'm still getting the access denied error. Are there any other possibilities?

I've tried this on 2 separate servers not associated with one another at all and I'm getting the same access denied error.

No. I am talking about mysql user. Not the one you use to log on to windows.

[nav33n]

Ok, but that won't cause the access error would it?

Nope. If it's outside that directory, it will generate an error with errorcode 2.

[Venom Rush]

No. I am talking about mysql user. Not the one you use to log on to windows.

What I meant was I'm using the MySQL root user.

[nav33n]

Hmm.. user root has all the permissions AFAIK. But if you have access to phpmyadmin, go to my sql database and select table user. Check the permission option under File_priv. If its N, make it Y.
Hope that helps.

[Venom Rush]

Hmm.. user root has all the permissions AFAIK. But if you have access to phpmyadmin, go to my sql database and select table user. Check the permission option under File_priv. If its N, make it Y.
Hope that helps.

I can't find anywhere in phpmyadmin to "select table user".

I just realised something....would the reason for the access error be that I'm logged in with the user in phpmyadmin and trying to run the script with the same username and password?

[nav33n]

No. You can open multiple instances of phpmyadmin's while running your script :P lol..

And, user table is in mysql database.

Server: localhost - Database: mysql - Table: user "Users and global privileges"

[Venom Rush]

No. You can open multiple instances of phpmyadmin's while running your script :P lol..

And, user table is in mysql database.

There is no option for me to select the mysql database in phpmyadmin. The only db I can select is the one I created :(

[nav33n]

huh! Well, I dunno then. :( Sorry.

[Venom Rush]

huh! Well, I dunno then. :( Sorry.

Bugger, well I guess the only thing left to say is "The universe is winning" ;)

[nav33n]

Bugger, well I guess the only thing left to say is "The universe is winning" ;)

:P Maybe you should contact your administrator who provides you mysql service. Btw, is this your local computer or are you testing it on the server ?

[Venom Rush]

:P Maybe you should contact your administrator who provides you mysql service. Btw, is this your local computer or are you testing it on the server ?

It's a server. I've had 3 calls with the hosting company trying to figure out what the problem is. No luck so far.

[Venom Rush]

I've tried an insert query and that seems to work fine. Surely that should mean that the user I'm using does not have full privileges?

[nav33n]

Yep. File_priv ie.,

[Venom Rush]

Is there any other query/method of taking the data from a csv or txt file and populating the table?

EDIT: One other thing...if you specified the path to the file would it start from the very root of the site eg. /public_html/directory1/directory2/directory3/user.txt

EDIT 2: I think I may be onto something here. I'm getting the following error:

Error in query: LOAD DATA LOCAL INFILE 'C:\user.txt' INTO TABLE mt_user_test FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '. File 'C:user.txt' not found (Errcode: 2)

I'm now using LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE. My problem just seems to be scope. Any ideas here? As you can see I tried pointing it to where it was on my hard drive but it isn't working.

[nav33n]

As far as your 1st question is concerned, you can get the values from a csv file and insert those to a table. fgetcsv is the function name.
I don't know from where its gonna start !
And yep, that's what I was talking about. Errorcode: 2 ! The path you have specified is not the one which its looking for !

[Venom Rush]

I'm a bit confused about this error:

Error in query: LOAD DATA LOCAL INFILE 'C:\user.txt' INTO TABLE mt_user_test FIELDS TERMINATED BY ',' LINES TERMINATED BY ' '. File 'C:user.txt' not found (Errcode: 2)

Why is there a \ missing after C: right at the end of the error ???

[Venom Rush]

Hi nav33n

I've read that link you gave for fgetcsv() and I understand it all. The one thing I don't understand though is how I'd populate my db table with just 1 or 2 variables when my table/csv both have equally more than one column/item of data

Here's an example I got from the fgetcsv page:

$handle = fopen('somefile.csv', 'r');
if ($handle)
{
    set_time_limit(0);
   
    //the top line is the field names
    $fields = fgetcsv($handle, 4096, ',');
   
    //loop through one row at a time
    while (($data = fgetcsv($handle, 4096, ',')) !== FALSE)
    {
        $data = array_combine($fields, $data);
    }

    fclose($handle);
}

Now I'm completely lost when it comes to trying to modify that to insert for example name, surname, tel, fax, birthday, address

[nav33n]

<?php
$handle = fopen('somefile.csv', 'r');
if ($handle)
{
  
    //the top line is the field names
    $fields = fgetcsv($handle, 4096, ',');
   
    //loop through one row at a time
    while (($data = fgetcsv($handle, 4096, ',')) !== FALSE)
    {
        $name=$data[0];
        $address=$data[1];
        $bday=$data[2]; //considering name will be in the first field, address in the second and bday in the third field.
        // Get all the fields from the csv file
       $query="insert into table (name,address,birthday) values ('$name',$address','$bday')";
       mysql_query($query);
    }

    fclose($handle);
}
?>

That's how you do it.

[Venom Rush]

Aaaah, thanks a lot nav33n. I managed to find a program that does what I needed before I saw this post of yours. But since it's only a trial version I'll definitely be keeping your example locked away in my vault ;)

P.S. The program is called Navicat 8 for those that might be interested.

[nav33n]

;)