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

19 followers

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 Nintriva Wireless

Total 12 comments

#15912 report it
gtrennert at 2013/12/28 04:09pm
tmp File not Found

Hello, I had to take LOAD DATA INFILE without local and I had to replace all bootstrap.widgets.Boot... by bootstrap.widgets.Tb... (new bootstrap version) But its not working : With : $csvFile=CUploadedFile::getInstance($model,'file');
$tempLoc=$csvFile->getTempName(); I get a path to file in tmp folder of wamp : "C:\wamp\tmp\phpB22.tmp"

But the following instruction does not work : $sql="LOAD DATA LOCAL INFILE '".$tempLoc...... I get this very long error message : CDbException Object ( [errorInfo] => Array ( [0] => HY000 [1] => 29 [2] => File 'C:wamp mpphpB22.tmp' not found (Errcode: 22) ) [... Seems that the path is corrupted !?

#14442 report it
subbu vemuri at 2013/08/12 03:39am
umlaut problem

hello sir, iam using this extenstion .it's working fro me.. the problem is iam getting umlaut problem..what is the solution

thanks

#10946 report it
Ivanda Nothabeer at 2012/12/06 01:18am
LOAD DATA INFILE has security issues.

http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html

Quoting from the mysql documentation ......

"To deal with these problems, we changed how LOAD DATA LOCAL is handled as of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows)"

This causes the error PDOStatement::execute() [pdostatement.execute]: LOAD DATA LOCAL INFILE forbidden

#9914 report it
spatan at 2012/09/21 09:07am
I got this error.

PDOStatement::execute() [pdostatement.execute]: LOAD DATA LOCAL INFILE forbidden

#9913 report it
spatan at 2012/09/21 08:49am
LOAD DATA INFILE

I didnt know someone already build this also. I found out about LOAD DATA INFILE from MYSQL website.

However, i built a form with a model-> My model is a simple one just like with is shown in the above example

Here is my controller Action......

public function actionUpload() { $model=new CsvUpload;

            // uncomment the following code to enable ajax-based validation
            /*
            if(isset($_POST['ajax']) && $_POST['ajax']==='csv-upload-createupload-form')
            {
                echo CActiveForm::validate($model);
                Yii::app()->end();
            }
            */

            if(isset($_POST['CsvUpload']))
            {
                $model->attributes=$_POST['CsvUpload'];
                if($model->validate())
                {

                     $model->file=CUploadedFile::getInstance($model,'file');
#9682 report it
MAHESH.E.U at 2012/09/03 05:26am
ok,let me check

then the problem will be on my side. Let me check. thanks

#9680 report it
sirin k at 2012/09/03 04:18am
Yes sure.

I have posted a well tested code.

#9677 report it
MAHESH.E.U at 2012/09/03 04:14am
Some problem

Sirin,Are you sure this code is working perfect.?

#8568 report it
Mariusz W. at 2012/06/12 04:14am
Transaction

Hmm, I was't sure about this transaction. I thought it has inner transaction...

#8558 report it
ferisoft at 2012/06/12 12:23am
transaction

Transaction is needed so that if in the middle of the import something breaks down you can rollback and try again...

#8519 report it
sirin k at 2012/06/10 05:00am
Thanks

Thanks.:)

#8516 report it
Mariusz W. at 2012/06/09 04:58pm
transaction

I think there is no need to use transaction here. We execute only one query. BTW nice tutorial :) I think reverse (export csv) could be interesting too :)

Leave a comment

Please to leave your comment.

Write new article
  • Written by: sirin k
  • Category: Tips
  • Yii Version: 1.1
  • Votes: +5
  • Viewed: 24,795 times
  • Created on: Jun 8, 2012
  • Last updated: never
  • Tags: csv, mysql, import