#1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    4
    Rep Power
    0

    php script for mysql import


    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????
  2. #2
  3. Moderator Emeritus
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2002
    Location
    Austin, TX
    Posts
    7,186
    Rep Power
    2265
    Moved to Scripts forum from PHP
    DrGroove, Devshed Moderator | New to Devshed? Read the User Guide | Connect with me on LinkedIn
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    London, England
    Posts
    279
    Rep Power
    12
    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?
  6. #4
  7. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    4
    Rep Power
    0
    i have nothing
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    London, England
    Posts
    279
    Rep Power
    12

    Re: php script for mysql import


    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 Code:
    <?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
  10. #6
  11. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    3
    Rep Power
    0
    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...

    PHP Code:

    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 getting...

    Parse error: parse error, unexpected T_CONSTANT_ENCAPSED_STRING

    I've also tried ....

    PHP Code:

    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!
    Last edited by fluidicity; November 20th, 2003 at 10:58 AM.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2005
    Posts
    1
    Rep Power
    0

    details


    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.

    Originally Posted by Wite_Noiz
    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 Code:
    <?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
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2005
    Posts
    6
    Rep Power
    0
    I was trying something, and your code really helped me, thanks Wite_Noiz !!
  16. #9
  17. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2010
    Posts
    1
    Rep Power
    0

    not work


    I was trying your script, but i don't understand how take data from local. Becouse error , file not found..
    Please answer my question....

IMN logo majestic logo threadwatch logo seochat tools logo