Laravel-excel: chunk filter every time gets first 250 Rows

[adnandogar]

Excel::filter('chunk')->load($path)->chunk(250, function($results) use ($data)
{
print_r($results);
});

this function gets every time first 250 rows of excel , not get the other rows ,
my excel file contains 12000 Rows but it returns first 250 rows.

Please help me , may be i am wrong or may be this function returns only first 250 rows , is there any other way to get all rows in chunk wise ?

Note : i am using multiple sheets .

[jackfruh]

What does the use ($data) do? I don't have that, and mine returns more than the first chunk set.

[adnandogar]

use $data will give the option to use $data (outside variable) inside of function .

[patrickbrouwers]

You can't use the chunk filter with multiple sheets. You have to select the first one. See https://github.com/Maatwebsite/Laravel-Excel/issues/270

[tony-was]

Getting the same issue importing a CSV file.

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
    foreach($results as $row)
    {
        // do stuff
    }
});

using:
"phpoffice/phpexcel": "1.8.0"
"maatwebsite/excel": "~1.3.0" -> my dev version is V1.3.4 which seems to be working with phpexcel 1.8.0

I am using a basic CSV file and the first chunk is read successfully, I can iterate through all records without any errors, though chunks after the first chunk are not detected. I try changing the chunk size and all the rows successfully appear but the subsequent chunks do not.

[drakakisgeo]

Same problem.

[adnandogar]

At least i solved my problem by this solution : i need to save date as it is in mysql db , because in xlsx date when i get it is converted in its calculated value , so i first convert this file to csv then use the

Excel::filter('chunk')->load('file.csv')->chunk(250, function($results)
{
foreach($results as $row)
{
// do stuff
}
});

because csv removes (does not support) all formats conversions etc .

Also if only date concerns then you can use your own formula to convert date back .

[davidsivocha]

+1 on this.

Excel::filter('chunk')->load($csv->getRealPath())->chunk(200, function($results){
    foreach($results as $row) {
        //Do stuff
    }
});

Gets the first chunk of the CSV successfully and then stops after that. However when using an xlsx it works perfectly fine.

[dpk3062]

I've tracked the issue down to $spreadsheetInfo saying the worksheet has a single row and half a million columns. The proper values should be around 1500 totalRows and 20 totalColumns for my csv file. I'm guessing it's parsing columns that have data like ="002342" incorrectly which is causing it to not see the line breaks. However when you actually read the data it reads it in correctly.

Opening up the csv file in LibreOffice and re-saving using the data with "Save cell content as shown" and "Quote all text cells" is a good enough work around for me right now, so I don't think I'll be debugging this further.

[patrickbrouwers]

If the problem lies within $spreadsheetInfo, it means the problem lies within the parent package PHPExcel.

[gabrieltong]

+1

[ghost]

If you follow the calls up the stack in the PHPExcel module it does a basic php fgetcsv (see http://php.net/manual/en/function.fgetcsv.php). If you then look up issues on line endings with CSVs, there's this article in stackoverflow: http://stackoverflow.com/questions/5539053/php-fgetcsv-returning-all-lines

By adding ini_set('auto_detect_line_endings', true); to the top of you php file it fixed the issue for me.

[BadChoice]

+1 on this...
Any solution yet?

[carlituxman]

+1
I have the same problem

[BadChoice]

is it solved in 2.1.1?

[carlituxman]

No sorry. I deleted.
It works with ini_set solution
El 23/1/2016 15:48, "Jordi Puigdellívol" [email protected]
escribió:

is it solved in 2.1.1?

—
Reply to this email directly or view it on GitHub
https://github.com/Maatwebsite/Laravel-Excel/issues/388#issuecomment-174191580
.

[akumar]

It seems chunk filter only work on first sheet so it is very important to pass the sheet index

Excel::filter('chunk')->selectSheetsByIndex(0)->load('file.csv')->chunk(250, function($results)
{
foreach($results as $row)
{
// save to DB
}
});

[jamestowers]

Using 2.1.0 and this still doesn't seem to have been fixed, anyone got a working solution for importing xls files?

[filsav]

+1, no chunked import of xls with 2.1.0.

[bozhidarc]

+1 Still not working.

[KingGhost]

+1 Not working for me, attempted using csv, xls, xltx and xlsx. My code:

Excel::filter('chunk')->load($file_name)->chunk(200, function($results) { foreach($results as $row) { // do stuff } });
Weird thing is that my file contains 1800 rows but only the first 1000 load...

[webberwang]

Still not working as of May 10, 2017. Only the first chunk imports for csv. Queue is set up and working correctly.

@akumar Selecting the sheet shouldn't matter if the first chunk is already working. I added

selectSheetsByIndex(0)

and it didn't resolve the issue.

@rogerweefc
That solution worked!! My queue started firing up all the chunks now, what a beautiful sight, thank you.

From @rogerweefc

By adding ini_set('auto_detect_line_endings', true); to the top of you php file it fixed the issue for me.

[patrickbrouwers]

Changing your ini setting (ini_set('auto_detect_line_endings', true);) is indeed the solution.

[aoxiaoxi]

it does not work like this ini_set('auto_detect_line_endings', true);It only get first chunk rows。Please help me

[lorisleiva]

Same here. None of the solutions mentioned above fixed it for me. Tried with xlsx files.

[udab]

+1
Same Problem. All solutions not works. CSV files, 1 sheet.
Any suggestions?
Thanks

[IronSolution]

Excel::filter('chunk') ->selectSheetsByIndex(0)->load($path)->chunk(250, function($results) use($data) {
$row_set = $results->toArray();
  foreach ($row_set as $key => $row) {
                  var_dump($row);  
                }
            });

Try this way.it worked for me.

[xresidentx]

ini_set('auto_detect_line_endings', true); worked for me. Or manually changing Line endings, in my case from Macintosh (CR) to Windows(CR LF).
Without it only first 250 rows are queued/loaded.

(My testing csv file is 4k size, contains single sheet and has 36k rows)

[gabva]

Hi everyone

I had this problem and got stuck for hours, and I solve by stopping using dd() (exit of the program) inside the callback !

Hope it helps,

[Sindoweb]

ini_set('auto_detect_line_endings', true); did the trick for me!

Thanks @rogerweefc

[helingfeng]

@aoxiaoxi
I had to go into the excel config file and change import.force_sheets_collection to false, then all the chunks began to have data.

[RajshreeTathe]

Hi, I am trying to import excel file in lumen using Maatwebsite, included use Maatwebsite\Excel\Facades\Excel; on top of the controller and in bootstrap/app.php included these lines

$app->register('Maatwebsite\Excel\ExcelServiceProvider');
class_alias('Maatwebsite\Excel\Facades\Excel', 'Excel');
class_alias('Illuminate\Support\Facades\Response', 'Response');
class_alias('Illuminate\Support\Facades\Config', 'Config');

And function code is
$array = [];
Excel::load('Untitled_2.xlsx', function ($reader) {

     $reader->each(function($sheet) {  
           var_dump($sheet);  
         foreach ($sheet->toArray() as $row) {
           // User::firstOrCreate($row);
            $array[] = $row;
         }
     });
});

Untitled_2.xlsx

given file si attached.

But data return blank, it doesn't shows me any error nothing. Please guide me for the same

[helingfeng]

@RajshreeTathe
you can try

$excel_data = [];
Excel::load($filename, function ($reader) use (&$excel_data) {
    $reader->ignoreEmpty();
    $reader->getSheet(0);
    $excel_data = $reader->toArray();
});

[thepeanutguy]

I'm still having this issue with xlsx files & setting ini_set('auto_detect_line_endings', true); does nothing.

[dsampaolo]

Still the same issue when trying to chunk csv files : nothing in the chunks, no errors shown anywhere.