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

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

5 0
20 followers
Viewed: 60 832 times
Version: 1.1
Category: Tips
Tags: csv, import, mysql
Written by: sirin k
Last updated by: Maurizio Domba Cerin
Created on: Jun 8, 2012
Last updated: 3 years ago
Update Article

Revisions

View all history

Related Articles