Reading large Excel files

I need to import a relatively large .xlsx file (40,000 rows, 15 columns) into my database.
I tried the PHPExcel library, but it gives a memory error.
I've found a library that should be able to read a big file row by row: Spout.

I created a folder named "spout" in application/third_party.
In that folder, the directory structure of Spout looks like this:
Autoloader
Common
Reader
Writer

In the application/libraries folder, I created a file named Spout.php, with this contents:

PHP Code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 
require_once APPPATH . '/third_party/spout/Autoloader/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;    
        
class Spout { 
    public function __construct() { 
    
    } 
} 


In my Import.php controller, I have this:

PHP Code:

$this->load->library('spout');
$reader = ReaderFactory::create(Type::XLSX); // for XLSX files 


But CI gives an error:

Code:

A PHP Error was encountered
Severity: Error
Message: Class 'ReaderFactory' not found
Filename: controllers/Import.php
Line Number: 346

I'm making some fundamental mistake, I guess. Who can help me to get this working?

I need to import a relatively large .xlsx file (40,000 rows, 15 columns) into my database.
I tried the PHPExcel library, but it gives a memory error.
I've found a library that should be able to read a big file row by row: Spout.

I created a folder named "spout" in application/third_party.
In that folder, the directory structure of Spout looks like this:
Autoloader
Common
Reader
Writer

In the application/libraries folder, I created a file named Spout.php, with this contents:

PHP Code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 
require_once APPPATH . '/third_party/spout/Autoloader/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type; 
 
class Spout { 
    public function __construct() { 
 
    } 
} 


In my Import.php controller, I have this:

PHP Code:

$this->load->library('spout');
$reader = ReaderFactory::create(Type::XLSX); // for XLSX files 


But CI gives an error:

Code:

A PHP Error was encountered
Severity: Error
Message: Class 'ReaderFactory' not found
Filename: controllers/Import.php
Line Number: 346

I'm making some fundamental mistake, I guess. Who can help me to get this working?

use Box\Spout\Reader\ReaderFactory; <- wrong place. it need to be in Import.php or you write a wrapper.

I think you are having a namespace problem.

Box is the namespace for spout which your showing that its in third_party

Your use clause is looking for a namespace called Box

What did you Try? What did you Get? What did you Expect?

Joined CodeIgniter Community 2009.  ( Skype: insitfx )

Can't get it working. I also tried the ChunkReadFilter as described in the PHPExcel documentation, but I can't figure that out either. My script runs endlessly. It doesn't seem to read the file in chunks at all.
Anyone who has a working example for that?
Ultimately, I want to read every row in the Excel file into an array.

Is this one time import?

Is this one time import?

No, I want to be able to import a new version of the Excel file every week.

Is this one time import?

No, I want to be able to import a new version of the Excel file every week.

Wouter60 what about csv?

I need to import a relatively large .xlsx file (40,000 rows, 15 columns) into my database.
I tried the PHPExcel library, but it gives a memory error.
I've found a library that should be able to read a big file row by row: Spout.

I created a folder named "spout" in application/third_party.
In that folder, the directory structure of Spout looks like this:
Autoloader
Common
Reader
Writer

In the application/libraries folder, I created a file named Spout.php, with this contents:

PHP Code:

<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
 
require_once APPPATH . '/third_party/spout/Autoloader/autoload.php';
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;    
        
class Spout { 
    public function __construct() { 
    
    } 
} 


In my Import.php controller, I have this:

PHP Code:

$this->load->library('spout');
$reader = ReaderFactory::create(Type::XLSX); // for XLSX files 


But CI gives an error:

Code:

A PHP Error was encountered
Severity: Error
Message: Class 'ReaderFactory' not found
Filename: controllers/Import.php
Line Number: 346

I'm making some fundamental mistake, I guess. Who can help me to get this working?

Maybe you can see this tutorial, this tutorial so clearly
http://teknosains.com/i/codeigniter-baca...ngan-spout

Wouter60 what about csv?

With csv Spout runs flawlessly. My csv-file is 33,800 rows, 15 columns. Spout reads it in 3.78 seconds! And a memory peak of only 3.75 MB.
The same table as an xlsx file won't import at all. I set the time limit to 600 (10 minutes). After that, I get a message that the time limit was exceeded.
My Excel file has only one worksheet. I tried the example Projack89 pointed out. Didn't work.

For now, I'm very happy with the csv option. It's lightning fast!
Thanks for helping me out.

You dont need to create a Library. 

Once you've put in in third party, you can easily do this 

PHP Code:

?php
/**
* Excel dengan CI & Spout
*
*/
//load Spout Library
require_once APPPATH.'/third_party/spout/src/Spout/Autoloader/autoload.php';

//lets Use the Spout Namespaces
use Box\Spout\Reader\ReaderFactory;
use Box\Spout\Common\Type;

class Export extends CI_Controller {

      public function readExcelFile() {

          try {
           
               //Lokasi file excel       
               $file_path = "C:\file_excel.xlsx";                     
               $reader = ReaderFactory::create(Type::XLSX); //set Type file xlsx
               $reader->open($file_path); //open the file          
           
                $i = 0; 
                                   
                /**                  
                * Sheets Iterator. Kali aja multiple sheets                  
                **/           
                foreach ($reader->getSheetIterator() as $sheet) {

                    //Rows iterator                
                    foreach ($sheet->getRowIterator() as $row) {

                        print_r($row); 
                  
                        ++$i;
                    }
                }

                echo "Total Rows : " . $i;              
                $reader->close();
                            

               echo "Peak memory:", (memory_get_peak_usage(true) / 1024 / 1024), " MB";

      } catch (Exception $e) {

              echo $e->getMessage();
              exit;   
      }

  }//end of function 


}//end of class


this will output 

Array
(
    [0] => SPP-16755
    [1] => 42198
    [2] => Mester SERVER
    [3] => Rp.9000
    [4] => Banjarmasin
)
....
....

Total Rows : 6171 
Peak memory usage: 2 MB

Read Big Excel File Using PHP Spout Library