CSV File To DB In Yii

Question

Am working with yii and anyone can help me to upload CSV file with multiple rows to Database using yii.

Table structure

CREATE TABLE users ( id int NOT NULL, title varchar(1000) NOT NULL, description varchar(1000) NOT NULL, phase varchar(1000) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

And my test CSV file contain following fields

title	Description	Phase
Login	Login & logout	4

My View page form.php

<div class="form">
<?php 
 $form = $this->beginWidget('LiveActiveForm', array(
        'id' => 'project-document-form',
        'enableAjaxValidation' => false,
        'htmlOptions'=>array('enctype' => 'multipart/form-data'),
            ));
    ?>   

     <div class="row"> 
    <div class="col-md-6">
         <?php echo $form->fileFieldRow($model,'filename');?>
         <?php if(Yii::app()->user->hasFlash('error')): ?>
            <div class="success" style="color:red;" id="error_file">
                    <?php echo Yii::app()->user->getFlash('error'); ?>
            </div> 
        <?php endif;?>
        <?php if(Yii::app()->user->hasFlash('success')): ?>

            <div id="success" class="success" style="color:green;">
                    <?php echo Yii::app()->user->getFlash('success'); ?>
            </div>
        <?php endif;?> 
        <?php Yii::app()->clientScript->registerScript('myHideEffect', ' $( "#ProjectDocument_filename" ).parent().append($( "#error_file" ));', CClientScript::POS_READY ); ?>   
             <div class="form-group">
                 <?php

                $this->widget('LiveButton', array(
                    'buttonType'=>'submit',
                    'type' => 'primary',
                    'label' => 'Upload File',
                    'size' => 'small',
                    'icon'=>'icon-upload icon-white',
                    'htmlOptions'=>array('name'=>'uploadCSV','style'=>'float:left;'),
                ));
                ?> 
         </div>
    </div>
  
         <div class="form-actions">
            <?php echo CHtml::link('<i class="icon-download-alt icon-white"></i>Download CSV Format',array('ProjectDocument/downloadfile'),array('class'=>'btn btn-success','style'=>'float:right;')); ?>
         </div>
    
    </div>
    
        <?php $this->endWidget(); ?>   
    
       <script type="text/javascript">
            $(document).ready(function(){$("#success").fadeOut(10000);});
        </script>
</div><!-- form -->

Csv file uploading is working proprly, but I have no idea how to insert the data from the file and store into the DB.

Answer 1

It seems you are using MySQL or MariaDB. If you have the file locally on the server (i.e. uploaded correctly), you can execute SQL

LOAD DATA LOCAL INFILE '/path/file' INTO TABLE users;

See MySQL documentation for details.

Direct SQL in Yii2 is done using:

Yii::$app->db->createCommand(sql, params)->execute();

Then it could look like

Yii::$app->db->createCommand('LOAD DATA LOCAL INFILE \':file\' INTO TABLE users', [':file' => path])->execute();