Yii 1.1: 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', 
                                            '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">
$form = $this->beginWidget('bootstrap.widgets.BootActiveForm', array(
)); ?>
            <p class="note">Fields with <span class="required">*</span> are required.</p>
        <?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 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')); ?>
<?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;
                    $sql="LOAD DATA LOCAL INFILE '".$tempLoc."'
        INTO TABLE `tbl_user`
            TERMINATED BY ','
            ENCLOSED BY '\"'
            TERMINATED BY '\n'
         IGNORE 1 LINES
        (`name`, `age`, `location`)
                            catch(Exception $e) // an exception is raised if a query fails


Total 6 comments

#19520 report it
coriolan at 2015/08/19 07:54am
Fix for "Syntax error or access violation: 1148 The used command is not allowed with this MySQL version"

I was able to use this by removing 'LOCAL' in

LOAD DATA INFILE '".$tempLoc."'

and fixing permissions before running the sql

chmod($file->tempName, 0755);
#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


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


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

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);



Leave a comment

Please to leave your comment.

Write new article