CODEWITHSUNDEEP
phpmysqlcodeigniterimport-from-excel
manjusha
manjusha

Reputation: 123

i want to import excel file into mysql in codeigniter?

iwant to import excel file into mysql.i created a table in db ,name of the table is statement.please guide me

mycontroller

 function my_test()
        {
                $table = 'statement';
        $filename ='expense.xls';

      $pathToFile = './uploads/' . $filename;

    //           print_r($pathToFile);die;
               $valuesSql="";
                $this->load->library('Excel_Reader');
                $data = new Excel_Reader($pathToFile);
                $sql = "INSERT INTO $table (";
                for($index = 1;$index <= $data->sheets[0]['numCols']; $index++){
                $sql.= strtolower($data->sheets[0]['cells'][1][$index]) . ", ";
                }

                $sql = rtrim($sql, ", ")." ) VALUES ( ";
                for ($i = 2; $i <= $data->sheets[0]['numRows']; $i++) {
                $valuesSQL = '';
                for ($j = 1; $j <= $data->sheets[0]['numCols']; $j++) {
                $valuesSql .= "\"" . $data->sheets[0]['cells'][$i][$j] . "\", ";
                }
                echo $sql . rtrim($valuesSql, ", ")." ) <br>";
                }                                           // add this line


        }                        



    }

my view page is

<html>
<head>
<title>Upload Form</title>
</head>
<body>

<?php // echo $error;?>

<?php echo form_open_multipart('Upload_c/my_test');?>

<input type="file" name="file_name" size="20" />

<br /><br />

<input type="submit" name="up" value="upload" />

</form>

</body>
</html>

when i click the upload button in view page,it is showing error like this

A PHP Error was encountered

Severity: Notice

Message: Undefined offset: 0

Filename: controllers/Upload_c.php

Line Number: 33

A PHP Error was encountered

Severity: Notice

Message: Undefined offset: 0

Filename: controllers/Upload_c.php

Line Number: 38

Upvotes: 0

Views: 18838

Answers (3)

Sonu Chohan
Sonu Chohan

Reputation: 273

public function dipExcelUpload($file=""){
    
        ini_set('MAX_EXECUTION_TIME', -1);
        ini_set('memory_limit', '-1');   

        $file_upload    = $file;

        if (isset($file_upload) && !empty($file_upload)){ 

        //You can add directly the Composer Autoloder in your controller: 
        require FCPATH . 'vendor/autoload.php';

        try{   
            $objPHPExcel = PHPExcel_IOFactory::load($file_upload);   
        }   
        catch (Exception $e){
            die('Error loading file "' . pathinfo($file_upload, PATHINFO_BASENAME) . '": '.@$e->getMessage()); 
        }   
    
        $allDataInSheet         = $objPHPExcel->getActiveSheet()->toArray(null, true, true, true);  
        $sheet                  = $objPHPExcel->getSheet(0);  
        $highestRow             = $sheet->getHighestRow();  
        $highestCol             = $sheet->getHighestColumn();  
        $highestco              = $sheet->getHighestDataColumn();  
        $arrayCount             = count($allDataInSheet);   
        $now                    = date("Y-m-d H:i:s");      
        $flag = 0;

        $check_template = array(
            'A' => 'FIN_ID',
            'B' => 'SECCODE',
            'C' => 'SCHEME_NO',
            'D' => 'SEC_SCH',
            'E' => 'DIPNO',
            'F' => 'DIPDT',
            'G' => 'DIPQTY',
            'H' => 'RATE',
            'I' => 'BILLQTY',       
            'J' => 'BILLREF',       
            'K' => 'DUMMY',       
            'L' => 'BILLDT',       
            'M' => 'EORP',       
        );

        $input_template = $allDataInSheet[1];

        array_walk($input_template, $this->trim_value);

        $result = array_diff($check_template, $input_template);

        if(empty($result))
        {
            $this->srObject->truncTableDip();

            # loop for inserting data 
            for ($i = 2,$j=0; $i <= $highestRow; $i++){

                $db_ch_ot = 64;

                $fin_id         = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $sec_code       = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $sch_no         = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $sec_sch        = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $dip_no         = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $dip_dt         = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);

                if(empty($dip_dt)) {
                    $dip_dt = null;
                }else{
                    $dip_dt = date("Y-m-d",strtotime($dip_dt));                    
                }

                $dip_qty        = trim($allDataInSheet[$i][chr(++$db_ch_ot)]); 
                $rate           = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $bill_qty       = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $bill_ref       = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $dummy          = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);
                $bill_dt        = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);

                if(empty($bill_dt)){
                    $bill_dt = null;
                }

                $eorp           = trim($allDataInSheet[$i][chr(++$db_ch_ot)]);

                $dt             = date('Y-m-d H:i:s');
                
                $insert_data = array(  
                    "fin_id_data"           => $fin_id,
                    "sec_code_data"         => $sec_code,
                    "sch_no_data"           => $sch_no,
                    "sec_sch_data"          => $sec_sch,
                    "dip_no_data"           => $dip_no,
                    "dip_dt_data"           => $dip_dt,
                    "dip_qty_data"          => $dip_qty,
                    "rate_data"             => $rate,
                    "bill_qty_data"         => $bill_qty,
                    "bill_ref_data"         => $bill_ref,
                    "dummy_data"            => $dummy,
                    "bill_dt_data"          => $bill_dt,
                    "eorp_data"             => $eorp,
                    "created_at_data"       => $dt,
                    "updated_at_data"       => $dt,           
                );
                
                if($this->srObject->insertSdipData($insert_data))
                {
                    ++$flag;
                }


            } //loop close  

            // routine call          
            $this->load->library('mydb');
            $this->mydb->GetMultiResults("CALL ut_sreco_dip_routine()");

        }  else {
            $this->session->set_flashdata('error', 'Error. Invalid Excel Template');
            redirect(site_url('schill-bill-checking-suuti/sreco'));
        }   

        $this->session->set_flashdata('success', 'File Uploaded Successfully ..'.' New Record Inserted : '.$flag);
        redirect(site_url('schill-bill-checking-suuti/sreco'));

     
    }
 
}

Upvotes: 0

Sorav Garg
Sorav Garg

Reputation: 1067

Please Try This In This Example You Have To Not Needed Any library it will work without any library --

My View File (index.php) --

<form class="form-horizontal well" action="<?php echo base_url(); ?>index.php/welcome/import" method="post" name="upload_excel" enctype="multipart/form-data">
<input type="file" name="file" id="file" class="input-large">
<button type="submit" id="submit" name="Import" class="btn btn-primary button-loading">Upload</button>
</form>

My Model (welcome_model.php) --

<?php 
class Welcome_model extends CI_Model
    {
        public function insertCSV($data)
            {
                $this->db->insert('subject', $data);
                return $this->db->insert_id();
            }
    }
?>

My Controller (welcome.php) --

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class Welcome extends CI_Controller {

    public function index()
        {
            $this->load->model('welcome_model');
            $this->load->view('index');
        }

    public function import()
        {
          if(isset($_POST["Import"]))
            {
                $filename=$_FILES["file"]["tmp_name"];
                if($_FILES["file"]["size"] > 0)
                  {
                    $file = fopen($filename, "r");
                     while (($emapData = fgetcsv($file, 10000, ",")) !== FALSE)
                     {
                            $data = array(
                                'SUBJ_CODE' => $emapData[1],
                                'SUBJ_DESCRIPTION' => $emapData[2],
                                'UNIT' => $emapData[3],
                                'PRE_REQUISITE' => $emapData[4],
                                'COURSE_ID' => $emapData[5],
                                'AY' => $emapData[6],
                                'SEMESTER' => $emapData[7],
                                );
                        $this->load->model('welcome_model');
                        $insertId = $this->welcome_model->insertCSV($data);
                     }
                    fclose($file);
                    redirect('welcome/index');
                  }
            }
        }
}

Please Try This It will Work Definitely..

Upvotes: 0

Keval Rathi
Keval Rathi

Reputation: 986

Try PHPEXCEL instead of EXCEL_READER...

Upvotes: 1

Related Questions