Importing CSV file to mysql table using "LOAD DATA" command.

You are viewing revision #3 of this wiki article.
This version may not be up to date with the latest version.
You may want to view the differences to the latest version or see the changes made in this revision.

« previous (#2)next (#4) »

hi, Here i am giving you some simple step to import huge data from a csv file to your mysql table.this is useful when you have a very huge data to import from csv format.

Imagine that you have a table, tbl_user(id(int AI),

       name(varchar(100)),
       age(int),
       location(varchar(100))).

in your db and you have a csv file like this format:Download test format.

step1:Define a Form model ie

class UserImportForm extends CFormModel
{
	public $file;
	/**
	 * @return array validation rules for model attributes.
	 */
	public function rules()
	{
		// NOTE: you should only define rules for those attributes that
		// will receive user inputs.
		return array(  
			 array('file', 'file', 
                                            'types'=>'csv',
                                            'maxSize'=>1024 * 1024 * 10, // 10MB
                                            'tooLarge'=>'The file was larger than 10MB. Please upload a smaller file.',
                                            'allowEmpty' => false
                              ),
		           );
	}

	/**
	 * @return array customized attribute labels (name=>label)
	 */
	public function attributeLabels()
	{
		return array(
			'file' => 'Select file',
		);
	}
	
}

step2:

Now you need to define a form in your view.ie

Note: i have used bootstrap form widget here.you can change it based on your needs.

<div class="form">

<?php
$form = $this->beginWidget('bootstrap.widgets.BootActiveForm', array(
    'id'=>'service-form',
	'enableAjaxValidation'=>false,
	'method'=>'post',
	'type'=>'horizontal',
	'htmlOptions'=>array(
		'enctype'=>'multipart/form-data'
	)
)); ?>

	<fieldset>
		<legend>
			<p class="note">Fields with <span class="required">*</span> are required.</p>
		</legend>
		
		<?php echo $form->errorSummary($model, 'Opps!!!', null, array('class'=>'alert alert-error span12')); ?>
		
		<div class="control-group">		
			<div class="span4">
                              	<div class="control-group <?php if ($model->hasErrors('postcode')) echo "error"; ?>">
		<?php echo $form->labelEx($model,'file'); ?>
		<?php echo $form->fileField($model,'file'); ?>
		<?php echo $form->error($model,'file'); ?>
                        	</div>


			</div>
		</div>

		<div class="form-actions">
			<?php $this->widget('bootstrap.widgets.BootButton', array('buttonType'=>'submit', 'type'=>'primary', 'icon'=>'ok white', 'label'=>'UPLOAD')); ?>
			<?php $this->widget('bootstrap.widgets.BootButton', array('buttonType'=>'reset', 'icon'=>'remove', 'label'=>'Reset')); ?>
		</div>
	
	</fieldset>

<?php $this->endWidget(); ?>

</div><!-- form -->

step3:Now you need to write an action in your controller to import the file.ie

public function actionImportCSV()
        {
           $model=new UserImportForm;
           
           if(isset($_POST['UserImportForm']))
             {

               $model->attributes=$_POST['UserImportForm'];

               if($model->validate())
                 {
          
                  $csvFile=CUploadedFile::getInstance($model,'file');  
                  $tempLoc=$csvFile->getTempName();
        
                    $sql="LOAD DATA LOCAL INFILE '".$tempLoc."'
        INTO TABLE `tbl_user`
        FIELDS
            TERMINATED BY ','
            ENCLOSED BY '\"'
        LINES
            TERMINATED BY '\n'
         IGNORE 1 LINES
        (`name`, `age`, `location`)
        ";
         
                    $connection=Yii::app()->db;
                    $transaction=$connection->beginTransaction();
                        try
                            {

                                $connection->createCommand($sql)->execute();
                                $transaction->commit();
                            }
                            catch(Exception $e) // an exception is raised if a query fails
                             {
                                print_r($e);
                                exit;
                                $transaction->rollBack();
                                                     
                             }
                      $this->redirect(array("user/index"));


                 }
             }  

           $this->render("importcsv",array('model'=>$model));
        }

-Sirin

Hire Yii developer