Inserting multiple rows at different lengths?

[Travis959]

I have a script to import a daily generated csv file to a database, and it works fine for all other the other files, but on this file I am running into a problem of it reporting back "Column count doesn't match value count at row 1" on some of the rows. There are 20 fields in the database, and some rows have the required 20 fields, but others are missing the very last field, and there is only 19 fields in that row. Is there anything I can do to fix this problem, without adding another field to the file?

 

Basically I just want it to forget the last field in the row, if it's not there in the file, and just go to the next row. Is this possible?

 

Here is my code:

 

$conn = mysql_connect($databasehost, $databaseusername, $databasepassword);
if (!$conn) {
	echo mysql_error();
	exit;
}
if (!mysql_select_db($databasename)) {
	echo mysql_error();
	exit;
} else {
$sql2 = "DELETE FROM `".$databasetable."`";
    mysql_query($sql2);
}
$fcontents = file($csvfile);
for($i=0; $i<sizeof($fcontents); $i++) {
	$line = trim($fcontents[$i]);		
	$arr = explode("\t", $line);
	$arr = preg_replace( "`[^a-zA-Z0-9+&@#%=~_|!:/\*,.( ]`", "", $arr);
	$sql = "INSERT INTO $databasetable VALUES ('". implode("','", $arr) ."')";
	mysql_query($sql);
	echo $sql ."<br>\n";
		if(mysql_error()) {
			echo mysql_error() ."<br>\n";
		}
}
?>

[dougjohnson]

Can you determine which of the records does NOT have the last field, and before inserting it just add a blank value into that field?

[PFMaBiSmAd]

After you explode the line, you can get a count of the number of elements. If the missing field is always the last one, you can add a blank/default value onto the end of the exploded elements.

 

Alternatively, you should already be listing the field names in the query. When one field is missing, you could build a query that does not contain that field name and simply leave that field and missing-value out of the query.