Import data to database from a Excel

[mdfaisalpapa]

Importing data from a Excel file is not difficult. In my project I have 7 fields. One field (Ref_No) is common for the items to be imported . So In the add page i ve selected only Ref_No to be displayed.

You should save your file (in Excel) as xml. (File->Save As->Save As type->XML Spread Sheet )

For this follow the steps as below.

1. in the Row_Rendered() add this line to the custommsg of the field( in my case Ref_No) where you want a File box.

   $this->Ref_No->CustomMsg="</td></tr><tr><td class='ewTableHeader'>File Name</td><td><input type='file' name='file' id='file' /></td></tr>";

2.in the Row_Inserting(&$rs) function add the following code

   $data = array();
   if ( $_FILES['file']['tmp_name'] )
    {  
          $dom = DOMDocument::load( $_FILES['file']['tmp_name'] );
          $rows = $dom->getElementsByTagName( 'Row' );
          foreach ($rows as $row)
          {
                $field1="";
                $field2 = "";
                $field3 = "";
                $field4= "";      
                $field5= "";
                $field6="";
                $index = 1;
                $cells = $row->getElementsByTagName( 'Cell' );
                foreach( $cells as $cell )
                { 
                    $ind = $cell->getAttribute( 'Index' );
                    if ( $ind != null ) $index = $ind;
                    if ( $index == 1 )  $field1 = $cell->nodeValue;        
                    if ( $index == 2 ) $field2 = $cell->nodeValue;
                    if ( $index == 3 ) $field3 = $cell->nodeValue;
                    if ( $index == 4 ) $field4= $cell->nodeValue;
                    if ( $index == 5 ) $field5= $cell->nodeValue;
                    if ( $index == 9 ) $field6=$cell->nodeValue;
                    $index += 1;
                    $data []= array('field1'=>$field1,'field2' => $field2,'field3' => $field3, 'field4' => $quantity,'field5' => $field5,  'field6'=>$field6);
           
              }
          }
     }
    $GLOBALS["conn"]->Execute("BEGIN");// so that upload should be complete or no upload
    foreach($data as $value)     
     {
            $sInsertSql="insert into tender_rates(field1, field2, field3, field4, Ref_No, field5,field6) values( '".$value['field1']."','".$value['field2']."',". $value['field3'].",". $value['field4'].",'".$rs['Ref_No']."','". $value['field5']."','".$value['field6']."')";
            $GLOBALS["conn"]->Execute($sInsertSql);
     }
 $GLOBALS["conn"]->Execute("COMMIT");

[umararif]

in my case ref_no (First field) os primary incremental. what will be alternate code? Thanks in advance

[fpfpfp]

Im in the same situation as umararif.
Any idea?

• I have tried the above code, it only adds an empty record... Can someone please help?

[halogbi]

same case I want upload csv file into my application with out using mysql how can I do that?

[mdfaisalpapa]

The following function can be used to import data from Excel. This function requries PHPExcel classes. call the function as shown below:

		$args=array("AA"=>"STR_TO_DATE('columvalue','%d/%m/%Y')", "AC"=>"STR_TO_DATE('columvalue','%d/%m/%Y')");
		importExcel2MySQL($conn,"tablename",$_FILES["filename"]["tmp_name"],$args);

firtst parameter is the mysqli connection object.
second parameter is the tablename where the data has to be inserted. if u pass a null value then the WorkSHeet name is taken as tablename
third parameter is the excel file.
fourth parameter is the arguments which is an array. If u need to perform any formatting on any column in the excel file pass the column name and the function as shown above. $args is optional.In the above code I am formatting the date in Column AA and AC of the excel file. 'columnvalue' should not be changed and is replaced with the value of AA / AY column from the excel file in the above example. you can have any Mysql function in the $args.

function importExcel2MySQL($conn,$tablename=null,$excelfile,$args=null)
{
include("phpexcel181/Classes/PHPExcel/IOFactory.php");// point to the correct path of PHPExcel
$valid = false;
$types = array('Excel2007', 'Excel5'); //check if it is an excel file
foreach ($types as $type)
{
$reader = PHPExcel_IOFactory::createReader($type);
if ($reader->canRead($excelfile))
{
$valid = true;
break;
}
}
if($valid)
{
$object = PHPExcel_IOFactory::load($excelfile);
foreach($object->getWorksheetIterator() as $worksheet)
{
$highestRow = $worksheet->getHighestRow();
$row=1;
if ($tablename==null) $tablename=$worksheet->getTitle();
$sql="INSERT INTO $tablename (";
$lastColumn = $worksheet->getHighestColumn();
$lastColumn++;
// getting the field names from first row
for ($col = 'A'; $col != $lastColumn; $col++)
{
$colvalue = mysqli_real_escape_string($conn, $worksheet->getCell($col.$row));
$sql.="$colvalue,";

		}
		$sql=substr($sql, 0, -1);
		$sql.=") VALUES";
		for($row=2; $row<=$highestRow; $row++) 
		{  
			$sql.="(";
			for ($col = 'A'; $col != $lastColumn; $col++)
			{
				$colvalue = mysqli_real_escape_string($conn, $worksheet->getCell($col.$row)); 
				if(isset($args[$col])) $sql.=str_replace("columvalue","$colvalue",$args[$col]).",";
				else $sql.="'$colvalue',";
			}
			$sql=substr($sql, 0, -1);
			$sql.="),";
		}
		$sql=substr($sql, 0, -1);
	}  
	mysqli_query($conn,$sql) or trigger_error("Import Failed! - Error: ".mysqli_error($conn), E_USER_ERROR);
	
}
else
	echo "Invalid File. EXCEL file expected";  

}

[mdfaisalpapa]

the above code will take the field names from the first row of the excel file.