Difference between #1 and #2 of Importing CSV file to mysql table using "LOAD DATA" command.

unchanged
Title
Importing CSV file to mysql table using "LOAD DATA" command.
unchanged
Category
Tips
unchanged
Tags
csv, mysql, import
changed
Content
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](https://dl.dropbox.com/u/74072668/test.csv "Csv test format").

step1:Define a Form model ie

~~~
[php]
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.


~~~
[php]
     <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
    

~~~
[php]
 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
Nintriva Wireless


[Yii developer](http://www.nintriva.com "Yii developer")