#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    66
    Rep Power
    6

    Problem using LOAD DATA LOCAL INFILE


    I have created a simple csv file using Notepad and saved it in the root folder on my computer (c:\) as test3.csv.

    It contains two lines as follows:

    1,2,3,4
    5,6,7,8

    I now wish to import it into a table using LOAD DATA LOCAL INFILE. This is my code.....
    PHP Code:
    $conn = @mysql_connect"localhost""root""password" )
              or die( 
    "Sorry - could not connect to MySQL" );
    $db "dbname";
    $rs mysql_select_db($db,$conn);

    $tempfile "vsol_tbl_temp_data";

      
    $query "mem_num int, ";
      
    $query.= " mem_a int, ";
      
    $query.= " mem_b int, ";
      
    $query.= " mem_c int ";

      
    $sqltemp "create table $tempfile ($query)";

    $rstemp = @mysql_query ($sqltemp);

    $sql "LOAD DATA LOCAL INFILE C:\\test3.csv INTO TABLE \"$tempfile\" FIELDS TERMINATED BY \",\" LINES TERMINATED BY \"\\r\\n\"";

    $rs1 mysql_query$sql$conn );

    $numrpt mysql_num_rows$rs1);

    $msg.="<br><br>There are a total of ".$numrpt." records<br>";

    echo (
    $msg); 
    but no records are being imported. can someone help please?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,381
    Rep Power
    594
    First you are using the deprecated MySQL functions. Stop and rewrite your code using PDO. Second, you have masked all errors with the '@' so you are not able to see any errors. Finally none of your code uses any error checking. Where did you get this code? If looks like something you might have copy and pasted from the internet. That is always a bad idea because all you get is someone elses bad code.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,850
    Rep Power
    6351
    Since LOAD DATA INFILE is purely a mysql operation, I've moved the thread to the mysql forum. If it turns out your PHP (and your use of dangerous and deprecated functions) is at fault, the mods here will move it back.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    66
    Rep Power
    6
    Sorry, I removed all the error checking code to make it simpler. I have removed the @ sign but this does not give me any more information. It stil reports zero records uploaded. The PHP is based on the book "PHP in Easy steps". PDO came out later and it would take me months to re-write the whole of the web site (plus my time to learn PDO).
  8. #5
  9. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,850
    Rep Power
    6351
    Off-topic argument split to its own thread in the lounge.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  10. #6
  11. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Putting back my on-topic post after the mod moved the offtopic stuff:

    have you tried running this query from a database tool, ie: using something other than PHP?
    because MySQL uis probably not allowed to access files at any random location on disk. Under linux for example you have to add special permissons to allow it to go outside of it's own working directories (which makes sense, but gives a very cryptic error...)
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2009
    Posts
    66
    Rep Power
    6
    To close this thread...just in case anyone is looking for the answer. This is the code that I used successfully to import the csv file.

    Code:
    $db = new PDO('mysql:host=localhost;dbname=databasename;charset=utf8', 'nnnn', 'password', array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION));
    $tempfile = "tbl_temp_data";
    
    $sqldrop = "DROP TABLE IF EXISTS $tempfile";
    
    $dropTable = $db->prepare($sqldrop);
    $dropTable->execute();
    
    
      $query = "mem_csv char(4), ";
      $query.= "sex_csv char(1) ,";
      $query.= "fpg_csv varchar(10), ";
    
    
      $sqltemp = "create table $tempfile ($query)";
    
      $stmt = $db->prepare($sqltemp);
      $stmt->execute();
    
    $filecsv = fopen($fileload , "r");
    
    while (!feof($filecsv)  ) 
    
    		{	
    
    			$valcsv = fgetcsv($filecsv, 1024);
    				if ($valcsv )
        				{
    
    
    					$sqlins = "insert into $tempfile ( mem_csv,  sex_csv, fpg_csv ) values ( \"$valcsv[0]\", \"$valcsv[1]\",  \"$valcsv[2]\" )";
        				$stmt = $db->prepare($sqlins);
      					$stmt->execute();
    
        				}
    		}	
    
    fclose($filecsv);
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2011
    Location
    Sydney Australia
    Posts
    182
    Rep Power
    83
    Originally Posted by rps
    PHP Code:
    $sql "LOAD DATA LOCAL INFILE C:\\test3.csv INTO TABLE \"$tempfile\" FIELDS TERMINATED BY \",\" LINES TERMINATED BY \"\\r\\n\""
    but no records are being imported. can someone help please?
    The filename is a quoted string, and the tablename isn't
    PHP Code:
    $sql "LOAD DATA LOCAL INFILE 'C:\\test3.csv' INTO TABLE $tempfile FIELDS TERMINATED BY \",\" LINES TERMINATED BY \"\\r\\n\""

IMN logo majestic logo threadwatch logo seochat tools logo