Page 1 of 1
Import data to database from a Excel
Posted: Thu Dec 17, 2009 12:28 pm
by 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.
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");
Re: Import data to database from a Excel
Posted: Tue Jul 15, 2014 9:59 pm
by umararif
in my case ref_no (First field) os primary incremental. what will be alternate code? Thanks in advance
Re: Import data to database from a Excel
Posted: Fri Jul 31, 2015 9:06 pm
by 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?
Re: Import data to database from a Excel
Posted: Sun Aug 02, 2015 3:55 am
by halogbi
same case I want upload csv file into my application with out using mysql how can I do that?
Re: Import data to database from a Excel
Posted: Thu Jun 07, 2018 4:11 pm
by 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";
}
Re: Import data to database from a Excel
Posted: Thu Jun 07, 2018 4:14 pm
by mdfaisalpapa
the above code will take the field names from the first row of the excel file.