skip to Main Content

My file is excel .xlsx contains more than 20,000 rows, im using Centos 7 with Nginx web server. When i upload a small size file with few rows it works but when i introduce ShouldQueue and WithChunkReading interfaces it fails even if the file is small. Please I need help. Thanks for your time

Here is the error in a log file

 vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php  
[2019-07-11 14:48:47] development.ERROR: [0] File "/tmp/laravel-excel-4noteGu1gFjJoFClKJQsLw8SgDShm1nd.xlsx" does not exist. on line 344 of file vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php  
[2019-07-11 14:48:47] development.ERROR: [0] File "/tmp/laravel-excel-4noteGu1gFjJoFClKJQsLw8SgDShm1nd.xlsx" does not exist. on line 344 of file vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php 

Here is a queue error

    [2019-07-12 10:11:47][532] Processing: MaatwebsiteExcelJobsQueueImport
[2019-07-12 10:11:47][532] Processed:  MaatwebsiteExcelJobsQueueImport
[2019-07-12 10:11:47][533] Processing: MaatwebsiteExcelJobsReadChunk
[2019-07-12 10:11:47][534] Processing: MaatwebsiteExcelJobsReadChunk
[2019-07-12 10:11:47][535] Processing: MaatwebsiteExcelJobsReadChunk
[2019-07-12 10:11:47][535] Failed:     MaatwebsiteExcelJobsReadChunk

Here is my controller function

public function store(Request $request)
{
     Excel::import(new HsCodeImport(),"650.xlsx",'local'); 
     return redirect()->back()->withFlashSuccess(__('label.app_success'));
}

Here is my Import file on AppImportsHsCodeImport.php

<?php

namespace AppImports;

use AppExceptionsGeneralException;
use AppModelsApplicationHscode;
use AppModelsReceiptCodeReceiptCode;
use CarbonCarbon;
use IlluminateContractsQueueShouldQueue;
use IlluminateQueueInteractsWithQueue;
use IlluminateSupportCollection;
use IlluminateSupportFacadesDB;
use IlluminateSupportFacadesLog;
use MaatwebsiteExcelConcernsImportable;
use MaatwebsiteExcelConcernsToCollection;
use MaatwebsiteExcelConcernsToModel;
use MaatwebsiteExcelConcernsWithBatchInserts;
use MaatwebsiteExcelConcernsWithChunkReading;
use MaatwebsiteExcelConcernsWithHeadingRow;
use MaatwebsiteExcelConcernsWithMultipleSheets;

class HsCodeImport implements ToCollection, WithHeadingRow, ShouldQueue, WithChunkReading
{
    use Importable;

    protected $receiptCode;
    protected $chunk = 500;
    protected $hscode_key = 'hscode';
    protected $description_key = 'description';
    protected $regulatory_status_key = 'regulatory_status';
    protected $comment_key = 'comment';
    protected $headings = ['hscode','description','regulatory_status','comment'];

    public function __construct(/*ReceiptCode $receiptCode*/)
    {
//        $this->receiptCode = $receiptCode;
    }

    /**
     * @param Collection $collection
     * @throws GeneralException
     */
    public function collection(Collection $collection)
    {
        /*fetching the first Collection*/
        $columns = $collection->first();

        if (!$columns->has($this->headings)) {
            /*When file has different headings*/

        } else {
            /*When the file has expected headings*/

            /*Truncate temp table*/
            DB::table('hs_code_temps')->truncate();

            /*Counting rows on a file*/
            $original_file_rows_count = $collection->count();


            /*Chunk the file data according @var $chunk*/
            $chunks = $collection->chunk($this->chunk);

            /*read each chunks insert into Temporary table and validate to get if there is error in each row*/
            $chunks->each(function ($item) {
                /*Iterate on each chunk*/
                $item->toArray();

                foreach ($item as $row) {

                    /* start: Validating row entries */
                    $error_report = "";
                    $error = 0;

                    foreach ($row as $key => $value) {
                        if (trim($key) == $this->hscode_key) {
                            if (trim($value) == "" Or $value == NULL) {
                                $error = 1;
                                $error_report = $error_report . trans('exceptions.backend.upload.entries', ['column' => $key, 'entry' => $value]) . ", rn";
                                $row[$key] = NULL;
                            }
                        } elseif (trim($key) == $this->description_key) {
                            if (trim($value) == "" Or $value == NULL) {
                                $error = 1;
                                $error_report = $error_report . trans('exceptions.backend.upload.entries', ['column' => $key, 'entry' => $value]) . ", rn";
                                $row[$key] = NULL;
                            }
                        } elseif (trim($key) == $this->regulatory_status_key) {
                            if (trim($value) == "" Or $value == NULL) {
                                $error = 1;
                                $error_report = $error_report . trans('exceptions.backend.upload.entries', ['column' => $key, 'entry' => $value]) . ", rn";
                                $row[$key] = NULL;
                            }
                        }

                    }

                    /*Inserting into Temp table*/
                    DB::table('hs_code_temps')->insert([
                        'code' => $row[$this->hscode_key],
                        $this->description_key => $row[$this->description_key],
                        $this->regulatory_status_key => $row[$this->regulatory_status_key],
                        $this->comment_key => $row[$this->comment_key],
                        'receipt_code_id' => 1/*$receiptCode->id*/,
                        'error' => $error,
                        'error_report' => $error_report,
                        'created_at' => Carbon::now(),
                        ]);

                    /* end: Validating row entries*/

                }

            });

            /*compare total rows with no error to total rows of the file*/
            $total_temp_rows_count = DB::table('hs_code_temps')->whereError(0)->count();

            if ($total_temp_rows_count != $original_file_rows_count) {
                /*When there is error and rows are not equal*/
            } else {
                /*When there is no error*/
                $originalHsCode = new Hscode();
                $temp_table = DB::table('hs_code_temps')->get(['code', $this->description_key, $this->regulatory_status_key, $this->comment_key])/*->toArray()*/;
                /*Iterate throw the rows in a temp row*/
                foreach ($temp_table as $object) {
                    /*copy data from temp table to origin table*/
                    $originalHsCode->create(get_object_vars($object));
                }
                /*Truncate temp table*/
                DB::table('hs_code_temps')->truncate();

            }


        }


    }

    public function headings(): array
    {
        return ['HSCode','Description','Regulatory_Status','Comment'];
    }

    /*public function sheets(): array
    {

    }*/

    public function chunkSize(): int
    {
        return 500;
    }

    public function batchSize(): int
    {
        return 500;
    }

}

I expect the job to run a large data to a chunk to be processed

2

Answers


  1. pass false as third parameter to chunk() to disable queuing

    $data = [];
    Excel::filter('chunk')->load($path)->chunk(1000, function ($results) use (&$data) {
        foreach ($results as $row) {
            $data[] = $row;
        }
    }, $shouldQueue = false);
    return $data;
    
    Login or Signup to reply.
  2. I have a similar problem when deplying to heroku ,that points to a problem with the path.:

    `ERROR: [0] File "/tmp/laravel-excel-4noteGu1gFjJoFClKJQsLw8SgDShm1nd.xlsx" 
    does not exist. on line 344 of file 
    vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Reader/Xlsx.php`
    

    laravel-excel defines local_path variable in config/excel.php, so try changing the path here or check your public_path() where you are geting the problem.

    in my case the excemption saved in table failde_jobs is:

    "exception": """ InvalidArgumentException: File "/app/storage/framework/laravel-excel/laravel-excel-aCnUQTJT7ADvAnrf1AOEcaCBPLjhauij" does not exist. in /app/vendor/phpoffice/phpspreadsheet/src/PhpSpreadsheet/Shared/File.php:135n

    have been trying to see how to change sot that it does not go in the app folder..

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search