The number of row value expressions in the INSERT statement exceeds the maximum

[marksie1988]

Hi,

 

i have a script that reads a csv file and then imports all of the data into a mssql db i have one issue though the insert statement for this is large as these are stocklists which can have thousands of lines.

 

is there a way that i can get around this? i have inserted the code i am using below.

 

<?php
// define table name and csv file location and name
$host = "";
$user = "";
$pass = "";
$mydb = "";
$table = "";
$supp_id = "";
$csvupload = "pricelist.csv";
// Define DB mapping (Fill in rest of the mapping in order of appearance in CSV)
$fields = array(
"supp_part_no",
"manf_part_no",
"description",
"avail_qty",
"cost_price",
"ignore",
"ignore"
);
// Open the CSV file
$handle = fopen($csvupload, "r") or die("Unable to open $csvupload for reading");

// Get each row's values to an array 
$i = 1;
// define tab delimited or comma
while (($data = fgetcsv($handle, 512, ",")) !== FALSE) {
	// First row's headers and not included, otherwise row field count must match wanted field count
	if(count($data)==count($fields) && $i!=1){
		$rows[] = $data;
	}
	elseif(count($data)!=count($fields)) {			
		die("Erroneus CSV file: incorrect field count on row $i");
	}
	$i++;
}

// Close file handle
fclose($handle);

// Create SQL
if(count($rows)==0) die("No rows found in user file");
$sql = "INSERT INTO ".$table." (";
foreach($fields as $field)
	if($field!="ignore")
		$sql .= "$field,";
$sql = substr($sql,0,-1).") VALUES "; // Remove last comma
foreach($rows as $i => $row){
	$sql .= "(";
	foreach($row as $j => $value){
		if($fields[$j]!="ignore"){
			if(empty($value))
				$value = "NULL";
			// Quote strings, try to remove existing quotes
			elseif(!is_numeric($value))
				$value = "\"".str_replace("\"","",$value)."\"";
			$sql .= "$value,";
		}
	}
	$sql = substr($sql,0,-1)."),"; // Remove last comma
}
$sql = substr($sql,0,-1); // Remove last comma
	$sql2 = "DELETE FROM ".$table." WHERE supp_id = ".$supp_id."";


// Debug output
//echo "SQL became: $sql2";

// Insert into database, comment out for testing
$db = mssql_connect($host,$user,$pass);
mssql_select_db($mydb);
mssql_query($sql2) or die ("Failed to insert to db");
mssql_query($sql) or die("Failed to insert to db ");
mssql_close($db);
?>

 

Regards

Steve

[Mark Baker]

Try inserting each line one at a time rather than all in one go. It's slower, but less likely to have any problems

[marksie1988]

ok thanks,

 

how would i go about doing this? im not sure how i can select each row from the csv 1 at a time in a loop until the last row and execute the query? i havent done something like this in php as i am currently migrating a website from asp so am new to php

[marksie1988]

ok i think i have done it by rearanging the code slightly will test then post final code if it works