+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Using CodeIgniter (https://forum.codeigniter.com/forumdisplay.php?fid=5)
+--- Forum: Libraries & Helpers (https://forum.codeigniter.com/forumdisplay.php?fid=11)
+--- Thread: Reading large Excel files (/showthread.php?tid=68700)
Reading large Excel files - Wouter60 - 08-14-2017
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: 346I'm making some fundamental mistake, I guess. Who can help me to get this working?
RE: Reading large Excel files - Paradinight - 08-14-2017
(08-14-2017, 12: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.
RE: Reading large Excel files - InsiteFX - 08-14-2017
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
RE: Reading large Excel files - Wouter60 - 08-14-2017
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.
RE: Reading large Excel files - dsivic - 08-14-2017
Is this one time import?
RE: Reading large Excel files - Wouter60 - 08-14-2017
Quote:Is this one time import?
No, I want to be able to import a new version of the Excel file every week.
RE: Reading large Excel files - Paradinight - 08-14-2017
(08-14-2017, 02: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?
RE: Reading large Excel files - projack89 - 08-15-2017
(08-14-2017, 12: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-file-excel-besar-dengan-spout
RE: Reading large Excel files - Wouter60 - 08-15-2017
(08-14-2017, 11:51 PM)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.
RE: Reading large Excel files - tksains - 08-23-2017
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