i required to create one csv to imported data in to database but i have one confusion
my csv data related with 3 tables
so how separated and what a possible way to create csv like 1)If data alredy in table 1 so checking required data insert or not or updated that data 2)I also required to if data not insert result also disply on same time inserted not inserted etc...
By using phpexcel you can import excel/csv easily in Yii framework
1.download latest phpexcel from http://phpexcel.codeplex.com extract into extensions folder
2.create a action in your controller ann give proper basepath and phpexcelpath
public function actionImport()
{
$message = '';
if (!empty($_POST))
{
$file = CUploadedFile::getInstanceByName('import');
$spec = Yii::app()->basePath.'/data/imports/'.$file->name;
$file->saveAs($spec);
spl_autoload_unregister(array('YiiBase','autoload'));
$phpExcelPath = Yii::getPathOfAlias('ext.phpexcel.vender');
include($phpExcelPath . DIRECTORY_SEPARATOR . 'PHPExcel.php');
spl_autoload_register(array('YiiBase', 'autoload'));
try {
$inputFileType = PHPExcel_IOFactory::identify($spec);
$objReader = PHPExcel_IOFactory::createReader($inputFileType);
if ($inputFileType != 'xls')
{
$objReader->setReadDataOnly(true);
}
$objPHPExcel = $objReader->load($spec);
$objWorksheet = $objPHPExcel->setActiveSheetIndex(0);
$highestRow = $objWorksheet->getHighestRow();
for ($row = 1;$row < $highestRow+1; $row++)
{
$model= new ExamScores; // Yii AR model
$model->id = $objWorksheet->getCellByColumnAndRow(1, $row)->getValue();
$model->student_id = $objWorksheet->getCellByColumnAndRow(2, $row)->getValue();
$model->exam_id = $objWorksheet->getCellByColumnAndRow(3, $row)->getValue();
$model->marks = $objWorksheet->getCellByColumnAndRow(4, $row)->getValue();
$model->save(false);
$model->detachBehaviors(); // PHP < 5.3 memory management
unset($model);
Yii::app()->user->setFlash('sucess','Mark saved Successfully');
}
}
catch (Exception $e)
{
$message = 'There was a problem handling your file. Technical details: '.$e->getMessage();
}
if (! empty($message))
{
Yii::app()->user->setFlash('error',$message);
}
}
$this->render('import');
}
3.in your view file
<!--<div class="formCon">
<div class="formConInner">-->
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="247" valign="top">
<?php $this->renderPartial('left_side');?>
</td>
<td valign="top">
<h1>IMPORT EXCEL SHEET</h1>
<?php if(Yii::app()->user->hasFlash('sucess')): ?>
<div class="flash-success">
<?php echo Yii::app()->user->getFlash('sucess'); ?>
</div>
<?php endif; ?>
<div style="background-color: #E6E4E4;">
<table width="100%" cellspacing="2" cellpadding="2">
<tr>
<?php echo CHtml::beginForm('', 'post', array('enctype'=>'multipart/form-data')); ?>
<tr><td> </td></tr>
<td><?php echo CHtml::label('File to Import', 'fti'); ?>
<?php echo CHtml::fileField('import', '', array('id'=>'fti')); ?></td>
</tr>
<tr><td> </td></tr>
<tr><td> </td></tr>
<tr><td> </td></tr>
<tr>
<td><?php echo CHtml::submitButton('Submit'); ?> </td>
</tr>
<?php echo CHtml::endForm(); ?>
</tr>
</table>
</div>
</td>
</table>
<!--</div>
</div>-->
I have implement this code and it is tested code. I think it is very use full
You have follow some rule:-
1.your csv file according to database table name (ex: db table name is users then csv should be users.csv)
2.Your csv file's first row should be db table fields name (ex: Id, name etc) after then start your data entry
3.you can download data source class from :- http://code.google.com/p/php-csv-parser/ because i have require below the code: require_once 'CSV/DataSource.php';
<?php
ini_set('memory_limit','512M');
$dbhost = "localhost";
$dbname = "excel_import";
$dbuser = "root";
$dbpass = "";
$conn=mysql_connect ($dbhost, $dbuser, $dbpass) or die ("I cannot connect to the database because: " . mysql_error());
mysql_select_db($dbname) or die("Unable to select database because: " . mysql_error());
require_once 'CSV/DataSource.php';
$filename = "Book1.csv";
//$filename = $_POST['filename'];
$ext = explode(".",$filename);
$path = "uploads/".$filename;
$dbtable = $ext[0];
import_csv($dbtable, $path);
function import_csv($dbtable, $csv_file_name_with_path)
{
$csv = new File_CSV_DataSource;
$csv->load($csv_file_name_with_path);
$csvData = $csv->connect();
$res='';
foreach($csvData as $key)
{
$myKey ='';
$myVal='';
foreach($key as $k=>$v)
{
$myKey .=$k.',';
$myVal .="'".$v."',";
}
$myKey = substr($myKey, 0, -1);
$myVal = substr($myVal, 0, -1);
$query="insert into ".$dbtable." ($myKey)values($myVal)";
$res= mysql_query($query);
}
if($res ==1)
{
echo "record successfully Import.";
}else{
echo "record not successfully Import.";
}
}
?>
$row = 1;
if (($handle = fopen("path to your csv file/data.csv", "r")) !== FALSE)
{
while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
{
$row++;
print_r($data);
}
fclose($handle);
}
Based on the printed data you can do further steps.
