CSV import to update recrods?

Hi all… I am trying to get File Upload (csv) to parse out and update my records in the Database.
Does anyone know of a tutorial or a Sample application?

One option is to add a file uploader and apply processing code in the onvalidate event. A sample of an uploader is
// Clean field message
{message} = ‘’;

// Starts array of File
$arr_file = array();

// Address Directory's uploader of Application
$path = $this->Ini->path_doc; 

// Absolute path of the file  
$fileid = $path . '/uploads/' . {fileid};
$row = 0;

if(is_file($fileid)) {
    $tran = 0;
	    sc_begin_trans();
    if (($handle = fopen($fileid, "r")) !== FALSE) {
   // (re)start transaction
   $tran++;
   if ($tran==5000) {
	   sc_commit_trans();javascript:void(0);
	   sc_begin_trans();
	   $tran=1;
   }
   // read header record
   $data=fgetcsv($handle, 1000, ",");		   
   
// does it start with 'val-ref'?
   if (strtolower($data[0]) != 'val_ref') {  
       {message} .= '<br>this is not a valid data file';
   }  else {
   
      while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {
		  
       $num = count($data);
		  
	   if ((isset($data[4])) && ($data[4] != '')) {	
		  if(!isset($data[16])) {
			  $data[16]='';
		  } 
			  
	   // prevent that quotes will cause mysql syntax errors
          $data[1]=addslashes($data[1]);
          $data[2]=addslashes($data[2]);
          $data[3]=addslashes($data[3]);
          $data[4]=addslashes($data[4]);
          $data[5]=addslashes($data[5]);
          $data[12]=addslashes($data[12]);
          $data[15]=addslashes($data[15]);
          $data[16]=addslashes($data[16]);
	  
	  $data[8] = str_replace('/', '-', $data[8]);
              $data[8] = date('Y-m-d', strtotime($data[8]));
		   
	   // process property
              $insert_sql = "INSERT INTO property (val_ref, property_address, suburb, town, district,".
		             "land_area, floor_area, bedrooms, building_age, category, zone,".
			         "capital_value, land_value, improvement_value, valuation_date) values (".
		             "'$data[0]','$data[1]','$data[2]','$data[3]','$data[4]',".
		             "$data[9],$data[10],$data[11],'$data[12]','$data[15]','$data[16]',".
			         "$data[7], $data[6], $data[5], '$data[8]') on duplicate key update ".
			         "val_ref='$data[0]',  property_address='$data[1]', suburb='$data[2]', town='$data[3]', district='$data[4]',".
		             "land_area=$data[9], floor_area=$data[10], bedrooms=$data[11], building_age='$data[12]', category='$data[15]', zone='$data[16]',".
			         "capital_value=$data[7], land_value=$data[6], improvement_value=$data[5], valuation_date='$data[8]'" ; 
          sc_exec_sql($insert_sql);
		   		  
	   // process valuation
          $insert_sql = "REPLACE INTO valuation (val_ref, valuation_date, improvement_value,".
		             "land_value, capital_value) values ('$data[0]','$data[8]',$data[5],".
		             "$data[6],$data[7])";
          sc_exec_sql($insert_sql);	
		  
	   // process sales record
	      $check_sql = "SELECT max(property_sale_seq) as cnt, max(sale_date) as last_sale, count(*) as c from property_sales where ".
		               "property_val_ref='$data[0]'";
	      sc_lookup(rs, $check_sql);
	   
	   // if the current sale date > max sale_date then there's a new sale. 
	   // but if the count is 0 then there is no record at all
          if($data[13]=='') {
		     $data[13]='null';  
	      } else { 
               $data[13] = str_replace('/', '-', $data[13]);
               $data[13] = date('Y-m-d', strtotime($data[13]));
	      }         
		   
		  $mx=({rs[0][0]});        	// latest sale seq
		  $sd=({rs[0][1]});        	// sale date from database
          $c=({rs[0][2]});        	// # records
		  if($c==0) { 				// no records
			 $mx=1;
		  } else {
			if(($data[13]!=$sd)&&($data[13]!='null')) { 	// saledates are not equal.
			   if($data[13]>$sd) { 	// sale date is newer (is ok)
				  $mx++; 
			   } else { 			// sale date is older which is not ok.
				   
			   } 
			}
		  }		   
		   
	      if($data[14]==null) {$data[14]='null';}			
		   
	      $insert_sql = "INSERT INTO property_sales (property_val_ref, property_sale_seq, sale_date,".
			            "sales_person, last_sale_date, last_sale_price) values ('$data[0]', $mx, '$data[13]',".
			            "0,'$data[13]',$data[14]) on duplicate key update ".
						"property_val_ref='$data[0]', property_sale_seq=$mx, sale_date='$data[13]',".
			            "sales_person=0, last_sale_date='$data[13]', last_sale_price=$data[14]";
	      sc_exec_sql($insert_sql);	  
   	             
          $row++;
        }
	}
  }	  
  sc_commit_trans(); 
  fclose($handle);
}
} else {
{message} .= "Please, check the document path of the application!";
}

// Now reset null to 0. That is needed to be able to make the view';
$update_sql = "UPDATE property SET correct_suburb=0 WHERE correct_suburb IS NULL";
sc_exec_sql($update_sql);

$update_sql = "UPDATE property SET complex_id=0 WHERE complex_id IS null";
sc_exec_sql($update_sql);

{message}.='<br>number of records processed: '.$row;
if($row>0) { 
{message}.='<br><b>processing done.</b>';
}
1 Like

Thanks so much!
In this example is the form field name that contains the Uploader called: {fileid} ?

Yes, formfields are always between the curly brackets.