i have a csv file local to my pc, i need to import this comma delimited csv automatically to my mysql database on my web server,
i now think??? i need a php script that i can run as a cron job on my web server, this would automate the process and i could have a refresh button on the website to refresh the page automatically
i thought i could schedule a ftp program to upload the csv file to the web server
but there's a problem,, iam no good at php, can any help me????
Moved to Scripts forum from PHP
How much help do you require?
Have you got a PHP script that is connecting to your MySQL database, or do you want it all?
i have nothing
Okay, here's a starter.
Firstly, I suggest reading the following articals:
PHP 'MySQL Functions': http://uk.php.net/manual/en/ref.mysql.php
MySQL 'Load Data': http://www.mysql.com/doc/en/LOAD_DATA.html
I'll give you a short script so you're started.
Call the file something like 'update.php'.
<?php
## Connect to a local database server (or die) ##
$dbH = mysql_connect('localhost', 'user', 'pass') or die('Could not connect to MySQL server.<br>' . mysql_error());
## Select the database to insert to ##
mysql_select_db('test') or die('Could not select database.<br>' . mysql_error();
## CSV file to read in ##
$CSVFile = 'data.txt';
mysql_query('LOAD DATA LOCAL INFILE "data.txt" INTO TABLE News FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());
## Close database connection when finished ##
mysql_close($dbH);
?>
This is tested and working on my system. So, hopefully, it'll work on yours :)
Obviously, you'll have to change the server username and password and the database name for it to work.
Hope it helps
Hi,
Is there a way of importing a text file that has the likes of...
'WRX\'s,Subaru,Impreza'
as an entry for a field where fields are seperated by , and enclosed by ' but any characters ' are escaped by / as above.
I've tried modifying the original posted here as in...
mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "\\" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error());
mysql_query('LOAD DATA LOCAL INFILE "test" INTO TABLE "t1" FIELDS TERMINATED BY "," ENCLOSED BY "\'" ESCAPED BY "\\" LINES TERMINATED BY "\r\n";') or die('Error loading data file.<br>' . mysql_error());
..but I keep get...
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 '"mms3" FIELDS TERMINATED BY "," ENCLOSED BY "'" ESCAPED BY "\"
Is there a way for dealing with the ' in the ENCLOSED BY " " part so that the statement doesn't break up and get parsed wrongly?
I've tried phmyadmin 2.5.4-rc1 but getting " You should select file which you want to insert.", which I think is being caused by the above parsing problem.
Any help is greatly appreciated as I've tried various things but no luck.
Vic.
p.s. Probably should have put this post in the PHP forum!
Hi!
I'm new in php scripting and i need exatly the same script that u posted. Could u give me some more detail about runing the script? Where do i need to plase the php script? On my webserver? (it is a linux) The csv file is on my windows machine.
Okay, here's a starter.
Firstly, I suggest reading the following articals:
PHP 'MySQL Functions': http://uk.php.net/manual/en/ref.mysql.php
MySQL 'Load Data': http://www.mysql.com/doc/en/LOAD_DATA.html
I'll give you a short script so you're started.
Call the file something like 'update.php'.
<?php
## Connect to a local database server (or die) ##
$dbH = mysql_connect('localhost', 'user', 'pass') or die('Could not connect to MySQL server.<br>' . mysql_error());
## Select the database to insert to ##
mysql_select_db('test') or die('Could not select database.<br>' . mysql_error();
## CSV file to read in ##
$CSVFile = 'data.txt';
mysql_query('LOAD DATA LOCAL INFILE "data.txt" INTO TABLE News FIELDS TERMINATED BY "," LINES TERMINATED BY "\\r\\n";') or die('Error loading data file.<br>' . mysql_error());
## Close database connection when finished ##
mysql_close($dbH);
?>
This is tested and working on my system. So, hopefully, it'll work on yours :)
Obviously, you'll have to change the server username and password and the database name for it to work.
Hope it helps
I was trying something, and your code really helped me, thanks Wite_Noiz !!
I was trying your script, but i don't understand how take data from local. Becouse error , file not found..
Please answer my question.... :tntworth: