tell csv import script to ignore blank rows?

[marksie1988]

Hi i have the below script but i get an error if a row is blank ie a return in the csv file at the end i need to somehow tell it to skip the rows that have nothing on them:

 

<?php
// define table name and csv file location and name

$supp_id = "15";
$pricecode = "6";
$csvupload = "../ccd/Catalogue_ZZMILGAT.csv";

// create DB connection
$host = "localhost";
$user = "user";
$pass = "pass";
$mydb = "db";
$table = "table";

	$db = mssql_connect($host,$user,$pass);
	mssql_select_db($mydb);

// delete all old data
	$sql2 = "DELETE FROM ".$table." WHERE supp_id = ".$supp_id."";

mssql_query($sql2) or die("Failed to insert to db ");

// Define DB mapping (Fill in rest of the mapping in order of appearance in CSV)
$fields = array(
"manufacturer",
"ignore",
"manf_part_no",
"supp_part_no",
"description",
"rrp",
"cost_price",
"avail_qty");


// 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");


 // Remove last comma
foreach($rows as $i => $row){
	$sql = "INSERT INTO ".$table." (";
foreach($fields as $field)
	if($field!="ignore")
		$sql .= "$field,";
$sql = substr($sql,0,-1).",supp_id, date_added, PriceCode) VALUES ";
	$sql .= "(";
	foreach($row as $j => $value){
		if($fields[$j]!="ignore"){
			if(empty($value))
				$value = "0";
			// Quote strings, try to remove existing quotes
			elseif(!is_numeric($value))
				$value = str_replace("'","",$value);
				$value = str_replace("\"","",$value);
			$sql .= "'$value',";
		}
	}

	$sql = substr($sql,0,-1).",'".$supp_id."', getdate(),'".$pricecode."')"; // Remove last comma
	//echo "SQL became: $sql";

	mssql_query($sql) or die("Failed to insert to db ");

}

?>

[Garethp]

      if(count($data)==count($fields) && $i!=1 && preg_match('~[\S]~', $data)){
         $rows[] = $data;
      }

[marksie1988]

Hi Thanks,

 

unfortunately i get this error:

 

Warning: preg_match() expects parameter 2 to be string, array given in E:\CSV\CSV_GALTEC\ImportScripts\ccd.php on line 43

 

line 43 is:

 

if(count($data)==count($fields) && $i!=1 && preg_match('~[\S]~', $data)){

[Garethp]

Instead, try this

 

      if(count($data)==count($fields) && $i!=1){
$j = 0;
foeach($fields as $v)
	{
	if(!preg_match("~\S~", $v)
		{
		$j = 1;
		}
	}

if($j == 1)
	{
	continue;
	}

         $rows[] = $data;
      }

[marksie1988]

Parse error: syntax error, unexpected T_AS in E:\CSV\CSV_GALTEC\ImportScripts\ccd.php on line 45

 

line 47

      		if(!preg_match("~\S~", $v){

 

also changed

foeach($fields as $v)

 

to:

foreach($fields as $v)

[Garethp]

Woops. Sorry, it's 3 AM where I am. So did the foreach work?

[marksie1988]

Woops. Sorry, it's 3 AM where I am. So did the foreach work?

 

once i had changed the foreach i got the error:

Parse error: syntax error, unexpected '{' in E:\CSV\CSV_GALTEC\ImportScripts\ccd.php on line 47

 

line 47:

if(!preg_match("~\S~", $v){

[Garethp]

Gah!

 

if(!preg_match("~\S~", $v)){

 

If it wasn't so cold I'd go make myself coffee

[marksie1988]

ok i think that it still sees the blank rows as i get this error:

 

Erroneus CSV file: incorrect field count on row 14388

 

row 14388 is a blank row

[Garethp]

Okay, change

 

      if(count($data)==count($fields) && $i!=1){
   $j = 0;
   foeach($fields as $v)
      {
      if(!preg_match("~\S~", $v)
         {
         $j = 1;
         }
      }

   if($j == 1)
      {
      continue;
      }

         $rows[] = $data;
      }

 

To

 

   $j = 0;
   foeach($fields as $v)
      {
      if(!preg_match("~\S~", $v)
         {
         $j = 1;
         }
      }

   if($j == 1)
      {
      continue;
      }

      if(count($data)==count($fields) && $i!=1){


         $rows[] = $data;
      }

[marksie1988]

unfortunately this gives the same issue:

 

Erroneus CSV file: incorrect field count on row 14388

 

row 14388 is a blank row

[marksie1988]

Anyone else have any Ideas how to do this?? Cheers

[marksie1988]

the blank lines are only ever at the bottom of the CSV file so i dont suppose this would make it easier?

 

one idea would be to open the csv file remove the blank lines save it and then start again with the script to import the csv file to the DB

[Garethp]

Try this

 

<?php
// define table name and csv file location and name

$supp_id = "15";
$pricecode = "6";
$csvupload = "../ccd/Catalogue_ZZMILGAT.csv";

// create DB connection
$host = "localhost";
$user = "user";
$pass = "pass";
$mydb = "db";
$table = "table";

      $db = mssql_connect($host,$user,$pass);
      mssql_select_db($mydb);

// delete all old data
    $sql2 = "DELETE FROM ".$table." WHERE supp_id = ".$supp_id."";

   mssql_query($sql2) or die("Failed to insert to db ");
   
// Define DB mapping (Fill in rest of the mapping in order of appearance in CSV)
$fields = array(
   "manufacturer",
   "ignore",
   "manf_part_no",
   "supp_part_no",
   "description",
   "rrp",
   "cost_price",
   "avail_qty");


   // 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) && count($data) > 1) {         
         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");

   
    // Remove last comma
   foreach($rows as $i => $row){
      $sql = "INSERT INTO ".$table." (";
   foreach($fields as $field)
      if($field!="ignore")
         $sql .= "$field,";
   $sql = substr($sql,0,-1).",supp_id, date_added, PriceCode) VALUES ";
      $sql .= "(";
      foreach($row as $j => $value){
         if($fields[$j]!="ignore"){
            if(empty($value))
               $value = "0";
            // Quote strings, try to remove existing quotes
            elseif(!is_numeric($value))
               $value = str_replace("'","",$value);
               $value = str_replace("\"","",$value);
            $sql .= "'$value',";
         }
      }
      
      $sql = substr($sql,0,-1).",'".$supp_id."', getdate(),'".$pricecode."')"; // Remove last comma
      //echo "SQL became: $sql";

      mssql_query($sql) or die("Failed to insert to db ");
      
   }

?>

[marksie1988]

thats wicked works great thankyou so much help much appreciated

[marksie1988]

ok i now have an issue with the fixed script that if the csv file has a column with e.g.

 

Dell Computer Memory\

 

it will think that the \ is signifying a new column so it says there the field count is incorrect on the row with the \ is there i way i can str_replace this from the csv file?

 

when i add it to the script where the current str_replace is it doesnt work.

[marksie1988]

bumop