[0o0o0]

I send out retrieve a file, and it gets placed into a folder I call "databucket"

/databucket/datafile.csv


Somehow for some reason I cant get the mysql right to grab it and place it in my table. Started on this at 11pm last night its now 5 am..lol my eyes are burning. Yup running here for help.

So Before I write the script im/I was trying to run it in the "query window" in phpmyadmin.

as follows..

LOAD DATA LOCAL INFILE 'http://www.my_site.com/databucket/datafile.csv' INTO TABLE `MY_TABLE` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

Doesnt wanna work for me.
Is it even possible to retrieve a file from my own ftp.. Ive read all the instructions and forums around the net, im in a skeptical mood now thinking the file has to be placed elsewhere on the hosts server or something.

.. is there another place on my hosts server I have to dump this file to??

I even tried just..

LOAD DATA LOCAL INFILE '/databucket/datafile.csv' INTO TABLE `MY_TABLE` FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'


heres what i will use when i write the script, but will it work? if my "query window console in phpmyadmin wont do the task as a test place?

$sql = 'LOAD DATA LOCAL INFILE \'http://www.my_site.com/databucket/datafile.csv\' INTO TABLE `MY_TABLE` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';

or

$sql = 'LOAD DATA LOCAL INFILE \'/databucket/datafile.csv\' INTO TABLE `MY_TABLE` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\'';

Hopefully any of you have conquered this delemia or know if or if not you can grab csv files directly from your own ftp/online webspace.

[0o0o0]

hmm just thought .. forget the query window thing in phpmyadmin! am I right?


and instead of using /databucket/datafile.csv..

replace it with $data

so

PHP Code:

$data= www.my_site.com/databucket/datafile.csv;



$sql = 'LOAD DATA LOCAL INFILE \'$data\' INTO TABLE `MY_TABLE` FIELDS TERMINATED BY \',\' ENCLOSED BY \'"\' ESCAPED BY \'\\\\\' LINES TERMINATED BY \'\\r\\n\''; 

?? I just read some more before I completely pass out.

Is this one of two ways to fill a table with a csv file?

I noticed another

PHP Code:

/*   DO MYSQL CONNECTION HERE    */ 



$file = 'contacts.csv'; //file name here 



//we read the CSV file here 

$lines = file($file); 



//now we take each line and explode it then insert to DB 

foreach ($lines as $line) { 

   $cols = explode(',', $line); 

   mysql_query("INSERT INTO $table (F_name, L_name, S_name, M_name, N_name, address, city, state, zip, country, phone, email, years, participation, notes, designation, code) VALUES ('$cols[0]', '$cols[1]', '$cols[2]', '$cols[3]', '$cols[4]', '$cols[5]', '$cols[6]', '$cols[7]', '$cols[8]', '$cols[9]', '$cols[10]', '$cols[11]'), '$cols[12]', '$cols[13]', '$cols[14]', '$cols[15]', '$cols[16]', '$cols[17]'")or 

   die(mysql_error()); 

} 

which would be quicker? lol im so tired probably both are hallucinations right now. lol

If anyones willing rewrite me a correct and efficient example thanks. and good night!

[jfulton]

Hey, just got your pm.

Using LOAD DATA INFILE will be faster than the php script...if you can get it to work .

I've never tried using the command from phpMyAdmin, but don't see why it wouldn't work. Chances are that your problem is either a permissions issue (MySQL user / web server user / you), or that you are using the wrong path to find the file.

If the csv file is physically located on the same server as MySQL, you shouldn't need to use the "LOCAL" option. Then try to find out the absolute path of the csv file and use that rather than a relative path, because chances are that MySQL is using a different one.

If it can't be resolved quickly (or at all), it may be easier to just use the php script.

[0o0o0]

ah theres the keywords.. mysql server..

so when they say local.. means /blabla.csv

on the sql server.

NOT my webserver.

right? no? ya? no? ya do? ya dont? ya do? .. I should buy a boat!

[jfulton]

Hmmm...not quite...
Here, the mysql site does a good job at explaining it.
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
There's a big section on the LOCAL keyword.

[0o0o0]

Im not gettin this tho..

**it has to be from my C: or D: drive. Is this right??

cant get the csv from another source like ftp www.blabla.com/need/thisfile.csv
????

[mab]

For the non-LOCAL version, if you give an absolute path on the server, you can probably get it to work, but only if the mysql server and your web space are on the same server, as you will typically only be able to place the file in your own web space. Any relative paths are with respect to the mysql server's data path and should probably not be attempted.

An absolute path is something like the following -

PHP Code:

$filename = 'C:/Program Files/Apache Group/Apache2/htdocs/anycsvdata.csv'; 

Something like http://mydomain.com/anycsvdata.csv is not an absolute file system path and won't work.

The only advantage to the non-LOCAL version, if you can use it, is that the mysql server directly reads the file, which would result in the fastest possible loading of data.

For the LOCAL version, the absolute path method is your best choice. I tried this on a windows/apache 2/php 5/mysql 4 system and could not get any relative path version to work.

The following code works for me, and note the file checking and mysql error reporting, these will probably tell you why it is not working -

PHP Code:

$filename = 'C:/Program Files/Apache Group/Apache2/htdocs/anycsvdata.csv';

if (is_readable($filename)) {

echo 'The file is readable<br />';

} else {

echo 'The file is not readable<br />';

}

$query = "LOAD DATA LOCAL INFILE '$filename' INTO TABLE my_table FIELDS TERMINATED BY ','";

$result = mysql_query($query) or die('Query failed: ' . mysql_error()); 

[mab]

Originally Posted by 0o0o0

Im not gettin this tho..

**it has to be from my C: or D: drive. Is this right??

cant get the csv from another source like ftp www.blabla.com/need/thisfile.csv
????

You are correct. The file being loaded can only be in one of two places -

Using the LOCAL keyword - where your PHP script is running.

Without the LOCAL keyword - where the mysql server is running.

To get the file from a remote location would require reading it using PHP, then either creating a local file and using the LOAD DATA LOCAL INFILE method, or using PHP to directly insert the data into the database.

You mentioned FTP in your original post and I was going to ask if this was a remotely located file, but nothing in your posts indicated that the file was not in the same location as your script. Your original post also indicated you were dumping this file into the location listed, so no one assumed that this was a remote file in their answers.

[0o0o0]

ah man thanks for the final answer, i was goin insane.
So theres two ways, I just completed the other way..
exploding the csv file and runnin it into the Table. and oh of course now im having sql vs. ' troubles. Doesnt like the ' s.

Stumped again.

As for this threads method.. LOAD DATA LOCAL INFILE how sweet it would be if it actually worked. lol. and the additon of REPLACE.

I'll keep trying but im burnin out.

In the mean time.. is there any fixable for the sql vs. ' s?
any suggestions without having to manually change the csv files ' to /

Thanks.
The error..

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'Yamahas New'"', '"Top Quality"', '""', '"PartYAM21

thats what it spits out.. so im looking for what? another little nifty robot to dig into the csv file and take out all the ' s?

lemme know ( while im searching in the meantime)
Thanks.