SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Importing CSV files to MYSQL using PHP

    Hi I got the below code of the net, great job, only its not importing the data to the database can someone help me out to see what I might be missing out on.

    I don't get an error message or anything, it just doesn't seem to add anything for me

    Thanks


    Code:
    <?php
    
    $databasehost = "test";
    $databasename = "test";
    $databasetable = "test";
    $databaseusername ="test";
    $databasepassword = "test";
    $fieldseparator = ",";
    $lineseparator = "\n";
    $csvfile = "LOCATION OF CSV FILE";
    
    $addauto = 1;
    $save = 0;
    $outputfile = "output.sql";
    
    if(!file_exists($csvfile)) {
    	echo "File not found. Make sure you specified the correct path.\n";
    	exit;
    }
    
    $file = fopen($csvfile,"r");
    
    if(!$file) {
    	echo "Error opening data file.\n";
    	exit;
    }
    
    $size = filesize($csvfile);
    
    if(!$size) {
    	echo "File is empty.\n";
    	exit;
    }
    
    $csvcontent = fread($file,$size);
    
    fclose($file);
    
    $con = @mysql_connect($databasehost,$databaseusername,$databasepassword) or die(mysql_error());
    @mysql_select_db($databasename) or die(mysql_error());
    
    $lines = 0;
    $queries = "";
    $linearray = array();
    
    foreach(split($lineseparator,$csvcontent) as $line) {
    
    	$lines++;
    
    	$line = trim($line," \t");
    	
    	$line = str_replace("\r","",$line);
    	
    	$linearray = explode($fieldseparator,$line);
    	
    	$linemysql = implode("','",$linearray);
    	
    	if($addauto)
    		$query = "insert into $databasetable values('','$linemysql');";
    	else
    		$query = "insert into $databasetable values('$linemysql');";
    	
    	$queries .= $query . "\n";
    
    	@mysql_query($query);
    }
    
    @mysql_close($con);
    
    if($save) {
    	
    	if(!is_writable($outputfile)) {
    		echo "File is not writable, check permissions.\n";
    	}
    	
    	else {
    		$file2 = fopen($outputfile,"w");
    		
    		if(!$file2) {
    			echo "Error writing to the output file.\n";
    		}
    		else {
    			fwrite($file2,$queries);
    			fclose($file2);
    		}
    	}
    	
    }
    
    connect_db(); 
    
                    $q="LOAD DATA 
                                    INFILE 'QTest.csv' 
                                    INTO TABLE qtest 
                                    FIELDS TERMINATED BY \"\t\" 
                                    LINES TERMINATED BY \"\n\" 
                                    
                                    ( 
                                    Test_Date_Received,
                                    Test_Date_Issued,
                                    Test_Issued_wkNo,
                                    Test_QuoteNo,
                                    Test_Status,
    								Test_New_Name,
    								Test_Cust_Name,
    								Test_Cust_Contact,
    								Test_Consultant,
    								Test_Nett_Value£,
    								Test_Nett_Value€,
    								Test_Number,
    								Test_Rep,
    								Test_Specified,
    								Test_Revision,
    								Test_Comments 
    								)";
                    mysql_query($q) or die(mysql_error());
    
    echo "Found a total of $lines records in this csv file.\n";
    
    ?>

  2. #2
    SitePoint Guru
    Join Date
    Nov 2003
    Location
    Huntsville AL
    Posts
    664
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Yikes. The should be a law against posting code like this. Not your post but whomever posted it to the net. Probably a joke of some kind.

    Read about fgetcsv (http://ca3.php.net/manual/en/function.fgetcsv.php) and get rid of all that explode/implode nonsense.

    Next add: die($query);

    Right before mysql_query(); It will be clear that the generated sql is not even close to what you want.

  3. #3
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Definitely too much code/work.
    Try this.

    Code PHP:
    $file = "test.csv";
    if (($handle = fopen($file, "r")) !== FALSE) {
    	if(!filesize($file)){echo "File is empty.\n";exit;}
        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
    		list($F1, $F2, $F3) = $data;		// depending on the number of fields expected
    		$SQL[] = sprintf("INSERT into tbl_name (field1, field2, field3) VALUES ('%s', '%s', '%s')", $F1, $F2, $F3);
        }
        fclose($handle);
    }else{
    	echo "Can't open file";
    	exit();
    }
     
    $conn = mysql_connect("localhost", "user", "pass", "db");
    mysql_select_db("db_test");
    foreach($SQL as $sql){
    	// run the queries individually
    	mysql_query($sql, $conn) or die(mysql_error());
    }

  4. #4
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Well, if we're discussing brevity of code...

    PHP Code:
    <?php
    $csv 
    = new SplFileObject('products.csv');
    $csv->setFlags(SplFileObject::READ_CSV);

    foreach(
    $csv as $line){
      
    #do something with $line array
    }
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  5. #5
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    6
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I was referring to the original code damo2009 posted

  6. #6
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks Isma
    I used your code and its working perfect,
    alto AnthonySterling yours looks good too, just Isma got before ya ;-)

    Thanks folks

  7. #7
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I notice if one of the records are blank in the csv file
    the php code gives an error, is there something I can add so it will leave this field blank and carry on to the next record?

  8. #8
    @php.net Salathe's Avatar
    Join Date
    Dec 2004
    Location
    Edinburgh
    Posts
    1,396
    Mentioned
    54 Post(s)
    Tagged
    0 Thread(s)
    The SplFileObject has a flag to ignore empty lines (see the very concise documentation). Or, if you mean that one of the fields is blank, where it shouldn't be, then a simple if to check for it would suffice.
    Salathe
    Software Developer and PHP Manual Author.

  9. #9
    SitePoint Addict Shaydez's Avatar
    Join Date
    Jul 2006
    Location
    Boca Raton, Florida
    Posts
    355
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    the code he posted is a extremely old style back in like 2004 lol

    what anthony posted is the method i would use too. it can get a bit more complicated depending on how you want to parse out the information before putting into the database but that's a skeleton start.

    i use SQLyog to import CSV files into a database if its just a 1 time deal.
    Sr. Website Developer and Internet Marketing
    www.CarlosJa.com Note: If anyone
    needs to get ahold of me please feel free to email me through
    my site. Apparently i missed quite a few private messages.

  10. #10
    SitePoint Zealot
    Join Date
    Aug 2009
    Posts
    159
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi Shaydez
    it wont be a once off upload, i'll be uploading loads of csv files to this database with different csv file names


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •