Importing CSV files to MYSQL using PHP

PHP
1

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


<?php

$databasehost = "test";
$databasename = "test";
$databasetable = "test";
$databaseusername ="test";
$databasepassword = "test";
$fieldseparator = ",";
$lineseparator = "\
";
$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.\
";
	exit;
}

$file = fopen($csvfile,"r");

if(!$file) {
	echo "Error opening data file.\
";
	exit;
}

$size = filesize($csvfile);

if(!$size) {
	echo "File is empty.\
";
	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," \	");
	
	$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 . "\
";

	@mysql_query($query);
}

@mysql_close($con);

if($save) {
	
	if(!is_writable($outputfile)) {
		echo "File is not writable, check permissions.\
";
	}
	
	else {
		$file2 = fopen($outputfile,"w");
		
		if(!$file2) {
			echo "Error writing to the output file.\
";
		}
		else {
			fwrite($file2,$queries);
			fclose($file2);
		}
	}
	
}

connect_db(); 

                $q="LOAD DATA 
                                INFILE 'QTest.csv' 
                                INTO TABLE qtest 
                                FIELDS TERMINATED BY \\"\	\\" 
                                LINES TERMINATED BY \\"\
\\" 
                                
                                ( 
                                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.\
";

?>
2

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.

3

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?

4

I was referring to the original code damo2009 posted

5

Well, if we’re discussing brevity of code…


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

foreach($csv as $line){
  #do something with $line array
}
?>

:wink:

6

Definitely too much code/work.
Try this.


$file = "test.csv";
if (($handle = fopen($file, "r")) !== FALSE) {
	if(!filesize($file)){echo "File is empty.\
";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());
}
7

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.

8

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

9

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.

10

Thanks Isma
I used your code and its working perfect,
alto AnthonySterling yours looks good too, just Isma got before ya :wink:

Thanks folks