• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Reading large Excel files

#1
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?
Reply

#2
(08-14-2017, 01:01 PM)Wouter60 Wrote: 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.
Reply

#3
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 the CodeIgniter Community in 2009.          ( Skype: insitfx )
Reply

#4
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.
Reply

#5
Is this one time import?
Reply

#6
Quote:Is this one time import?

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

#7
(08-14-2017, 03:03 PM)Wouter60 Wrote:
Quote: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?
Reply

#8
(08-14-2017, 01:01 PM)Wouter60 Wrote: 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
Reply

#9
(08-15-2017, 12:51 AM)Paradinight Wrote: 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.
Reply

#10
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 
Reply


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


Users browsing this thread:
1 Guest(s)


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2017 MyBB Group.