Import csv to mysql using Yii framework

Question

I have a problem with uploading csv file to database. This is my view:

<?php
$this->breadcrumbs = array(
__('People') => array('/contacts'),
__('Persons') => array('admin'),
__('Manage'),
);?>



<h1><?php echo __('People'); ?> <small><?php echo __('import contacts'); ?></small></h1><br/>
<div class="form">

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

<fieldset>


    <?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.TbButton', array('buttonType'=>'submit', 'type'=>'primary', 'icon'=>'ok white', 'label'=>'UPLOAD')); ?>
        <?php $this->widget('bootstrap.widgets.TbButton', array('buttonType'=>'reset', 'icon'=>'remove', 'label'=>'Reset')); ?>
    </div>

</fieldset>

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

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

This is my model:

<?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',
    );
 }

}
?>

And this is my function in Controller

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 `contacts`
    ";
                $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
                         {
                            echo "<pre>";
                            print_r($e);
                            echo "</pre>";
                            exit;
                            $transaction->rollBack();

                         }
                  $this->redirect(Yii::app()->createUrl("/contacts/importCSV"));


             }
         }  

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

And I have a problem with LOAD DATA, cause mySQL version don't support LOAD DATA. Anyone knows how can I replace load data to import csv file?

Answer 1

Follow the instructions at:

https://github.com/Ardem/yii-importcsv-extension

I followed the simple steps described in readme.md file having the 3 simple steps and it worked like a breeze.

Answer 2

If you are on a version of MySQL tat does not support LOAD DATA, then you are on a very old version indeed. The current online documentation includes LOAD DATA in every mentioned version, though the LOCAL clause is only supported as far back as 3.22.6.

Maybe the real problem is that if you do not specify the FIELDS clause it treats the file as if

FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
LINES TERMINATED by '\n' STARTING  BY ''

were specified. That is a TAB separated values file in Unix(Linux) text format. To make it comma separated, specify:

LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl_name
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

And if you are expecting files from a Windows machine you might want to include

LINES TERMINATED BY '\r\n'

after the FIELDS clause.