Closed Hanson closed 3 years ago
Thanks for submitting the ticket. Unfortunately the information you provided is incomplete. We need to know which version you use and how to reproduce it. Please include code examples. Before we can pick it up, please check (https://github.com/Maatwebsite/Laravel-Excel/blob/3.1/.github/ISSUE_TEMPLATE.md) and add the missing information. To make processing of this ticket a lot easier, please make sure to check (https://laravel-excel.maatwebsite.nl/3.1/getting-started/contributing.html) and double-check if you have filled in the issue template correctly. This will allow us to pick up your ticket more efficiently. Issues that follow the guidelines correctly will get priority over other issues.
I have this issue too. I done have this issue in my mac book . but in server centos7 I have this issue
version ob laravel-excel : 3.1 php version 7.3 laravel 5.8.35
Its my code:
`namespace App\Xls; use App\AssignedBook; use App\Book; use Illuminate\Contracts\Queue\ShouldQueue; use Illuminate\Support\Collection; use Illuminate\Support\Facades\Log; use Maatwebsite\Excel\Concerns\RegistersEventListeners; use Maatwebsite\Excel\Concerns\ToCollection; use Maatwebsite\Excel\Concerns\WithChunkReading; use Maatwebsite\Excel\Concerns\WithEvents; use Maatwebsite\Excel\Concerns\WithStartRow; use Maatwebsite\Excel\Events\AfterImport;
class ShopImport implements ToCollection , WithChunkReading, ShouldQueue,WithStartRow , WithEvents { use RegistersEventListeners; private $isbn; private $seller_id; private $price; private $show_price; private $saleable; private $shelf_id; private $count; private $xls_id;
public function __construct($seller_id , $isbn , $price , $show_price , $saleable , $shelf_id , $count , $xls_id)
{
$this->price = $price;
$this->show_price = $show_price;
$this->isbn = $isbn;
$this->saleable = $saleable;
$this->shelf_id = $shelf_id;
$this->count = $count;
$this->seller_id = $seller_id;
$this->xls_id = $xls_id;
}
public function collection(Collection $rows)
{
foreach ($rows as $row)
{
$book = Book::where('bar_code' , str_replace('-' , '' , $this->isbn))->first();
if ($book)
AssignedBook::updateOrCreate(
[
'isbn' => $row[$this->isbn],
'seller_id' => $this->seller_id,
'book_id' => 1000,
],
[
'isbn' => $row[$this->isbn],
'count' => $this->count ? $row[$this->count] : 0,
'price' => $this->price ? $row[$this->price] : 0,
'show_price' => $this->show_price ? $row[$this->show_price] : 0,
'saleable' => $this->saleable == 'true_available' ? 1 : 0,
'shelf_id' => $this->shelf_id ? $row[$this->shelf_id] : null,
'seller_id' => $this->seller_id,
'xls_id' => $this->xls_id,
]);
}
return false;
}
public function startRow(): int
{
return 1;
}
public function batchSize(): int
{
return 500;
}
public function chunkSize(): int
{
return 500;
}
public static function afterImport(AfterImport $event)
{
Log::info(' after import excel file');
}
} `
this is my job payload . maybe help.
{"displayName":"Maatwebsite\\Excel\\Jobs\\AfterImportJob","job":"Illuminate\\Queue\\CallQueuedHandler@call","maxTries":null,"delay":null,"timeout":null,"timeoutAt":null,"data":{"commandName":"Maatwebsite\\Excel\\Jobs\\AfterImportJob","command":"O:37:\"Maatwebsite\\Excel\\Jobs\\AfterImportJob\":9:{s:45:\"\u0000Maatwebsite\\Excel\\Jobs\\AfterImportJob\u0000import\";O:18:\"App\\Xls\\ShopImport\":8:{s:24:\"\u0000App\\Xls\\ShopImport\u0000isbn\";s:1:\"7\";s:29:\"\u0000App\\Xls\\ShopImport\u0000seller_id\";i:82;s:25:\"\u0000App\\Xls\\ShopImport\u0000price\";s:1:\"8\";s:30:\"\u0000App\\Xls\\ShopImport\u0000show_price\";s:0:\"\";s:28:\"\u0000App\\Xls\\ShopImport\u0000saleable\";s:14:\"true_available\";s:28:\"\u0000App\\Xls\\ShopImport\u0000shelf_id\";s:0:\"\";s:25:\"\u0000App\\Xls\\ShopImport\u0000count\";s:2:\"23\";s:26:\"\u0000App\\Xls\\ShopImport\u0000xls_id\";i:6;}s:45:\"\u0000Maatwebsite\\Excel\\Jobs\\AfterImportJob\u0000reader\";O:24:\"Maatwebsite\\Excel\\Reader\":5:{s:14:\"\u0000*\u0000spreadsheet\";N;s:15:\"\u0000*\u0000sheetImports\";a:0:{}s:14:\"\u0000*\u0000currentFile\";O:42:\"Maatwebsite\\Excel\\Files\\LocalTemporaryFile\":1:{s:52:\"\u0000Maatwebsite\\Excel\\Files\\LocalTemporaryFile\u0000filePath\";s:101:\"\/var\/www\/mayamey.com\/panel\/local\/uploads\/xls\/temp\/laravel-excel-0H9NBpVD78zpZFbQP8eGXV5tSOcZ6d5i.xlsx\";}s:23:\"\u0000*\u0000temporaryFileFactory\";O:44:\"Maatwebsite\\Excel\\Files\\TemporaryFileFactory\":2:{s:59:\"\u0000Maatwebsite\\Excel\\Files\\TemporaryFileFactory\u0000temporaryPath\";s:50:\"\/var\/www\/mayamey.com\/panel\/local\/uploads\/xls\/temp\/\";s:59:\"\u0000Maatwebsite\\Excel\\Files\\TemporaryFileFactory\u0000temporaryDisk\";N;}s:9:\"\u0000*\u0000reader\";O:36:\"PhpOffice\\PhpSpreadsheet\\Reader\\Xlsx\":8:{s:53:\"\u0000PhpOffice\\PhpSpreadsheet\\Reader\\Xlsx\u0000referenceHelper\";O:40:\"PhpOffice\\PhpSpreadsheet\\ReferenceHelper\":0:{}s:15:\"\u0000*\u0000readDataOnly\";b:1;s:17:\"\u0000*\u0000readEmptyCells\";b:1;s:16:\"\u0000*\u0000includeCharts\";b:0;s:17:\"\u0000*\u0000loadSheetsOnly\";N;s:13:\"\u0000*\u0000readFilter\";O:49:\"PhpOffice\\PhpSpreadsheet\\Reader\\DefaultReadFilter\":0:{}s:13:\"\u0000*\u0000fileHandle\";N;s:18:\"\u0000*\u0000securityScanner\";O:51:\"PhpOffice\\PhpSpreadsheet\\Reader\\Security\\XmlScanner\":2:{s:60:\"\u0000PhpOffice\\PhpSpreadsheet\\Reader\\Security\\XmlScanner\u0000pattern\";s:9:\"<!DOCTYPE\";s:61:\"\u0000PhpOffice\\PhpSpreadsheet\\Reader\\Security\\XmlScanner\u0000callback\";N;}}}s:10:\"connection\";N;s:5:\"queue\";s:10:\"xls_import\";s:15:\"chainConnection\";N;s:10:\"chainQueue\";s:10:\"xls_import\";s:5:\"delay\";N;s:7:\"chained\";a:0:{}s:9:\"\u0000*\u0000events\";a:0:{}}"}}
and this is my log error :
[2020-05-21 01:54:48] production.ERROR: SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value "32768" is out of range for type smallint (SQL: update "jobs" set "reserved_at" = 1590026088, "attempts" = 32768 where "id" = 931137) {"exception":"[object] (Illuminate\\Database\\QueryException(code: 22003): SQLSTATE[22003]: Numeric value out of range: 7 ERROR: value \"32768\" is out of range for type smallint (SQL: update \"jobs\" set \"reserved_at\" = 1590026088, \"attempts\" = 32768 where \"id\" = 931137) at
Seems you are trying to inserting something in jobs table that has an id that is too large for its type of small int. Sounds unrelated to this package though.
I have this issue too. When exporting large records @patrickbrouwers @GlennM Version Detail:
Exception:
[2020-09-13 04:01:21] local.ERROR: Maatwebsite\Excel\Jobs\AppendQueryToSheet has been attempted too many times or run too long. The job may have previously timed out. {"exception":"[object] (Illuminate\\Queue\\MaxAttemptsExceededException(code: 0): Maatwebsite\\Excel\\Jobs\\AppendQueryToSheet has been attempted too many times or run too long. The job may have previously timed out. at /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Worker.php:632)
[stacktrace]
#0 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(446): Illuminate\\Queue\\Worker->maxAttemptsExceededException()
#1 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(358): Illuminate\\Queue\\Worker->markJobAsFailedIfAlreadyExceedsMaxAttempts()
#2 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(314): Illuminate\\Queue\\Worker->process()
#3 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Worker.php(134): Illuminate\\Queue\\Worker->runJob()
#4 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(112): Illuminate\\Queue\\Worker->daemon()
#5 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Queue/Console/WorkCommand.php(96): Illuminate\\Queue\\Console\\WorkCommand->runWorker()
#6 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/horizon/src/Console/WorkCommand.php(46): Illuminate\\Queue\\Console\\WorkCommand->handle()
#7 [internal function]: Laravel\\Horizon\\Console\\WorkCommand->handle()
#8 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(32): call_user_func_array()
#9 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Container/Util.php(36): Illuminate\\Container\\BoundMethod::Illuminate\\Container\\{closure}()
#10 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(90): Illuminate\\Container\\Util::unwrapIfClosure()
#11 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Container/BoundMethod.php(34): Illuminate\\Container\\BoundMethod::callBoundMethod()
#12 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Container/Container.php(590): Illuminate\\Container\\BoundMethod::call()
#13 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Console/Command.php(134): Illuminate\\Container\\Container->call()
#14 /home/gech/workspace/laravel/oss-statistics/vendor/symfony/console/Command/Command.php(255): Illuminate\\Console\\Command->execute()
#15 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Console/Command.php(121): Symfony\\Component\\Console\\Command\\Command->run()
#16 /home/gech/workspace/laravel/oss-statistics/vendor/symfony/console/Application.php(1001): Illuminate\\Console\\Command->run()
#17 /home/gech/workspace/laravel/oss-statistics/vendor/symfony/console/Application.php(271): Symfony\\Component\\Console\\Application->doRunCommand()
#18 /home/gech/workspace/laravel/oss-statistics/vendor/symfony/console/Application.php(147): Symfony\\Component\\Console\\Application->doRun()
#19 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Console/Application.php(93): Symfony\\Component\\Console\\Application->run()
#20 /home/gech/workspace/laravel/oss-statistics/vendor/laravel/framework/src/Illuminate/Foundation/Console/Kernel.php(131): Illuminate\\Console\\Application->run()
#21 /home/gech/workspace/laravel/oss-statistics/artisan(37): Illuminate\\Foundation\\Console\\Kernel->handle()
#22 {main}
"}
[2020-09-13 09:08:09] local.ERROR: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 803127296 bytes) {"userId":2,"exception":"[object] (Symfony\\Component\\Debug\\Exception\\FatalErrorException(code: 1): Allowed memory size of 2147483648 bytes exhausted (tried to allocate 803127296 bytes) at /home/gech/workspace/laravel/oss-statistics/vendor/symfony/http-foundation/Response.php:368)
[stacktrace]
#0 {main}
"}
[2020-09-13 09:08:16] local.ERROR: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 776286208 bytes) {"userId":2,"exception":"[object] (Symfony\\Component\\Debug\\Exception\\FatalErrorException(code: 1): Allowed memory size of 2147483648 bytes exhausted (tried to allocate 776286208 bytes) at /home/gech/workspace/laravel/oss-statistics/vendor/symfony/http-foundation/Response.php:368)
[stacktrace]
#0 {main}
"}
[2020-09-13 09:08:24] local.ERROR: Allowed memory size of 2147483648 bytes exhausted (tried to allocate 776286208 bytes) {"userId":2,"exception":"[object] (Symfony\\Component\\Debug\\Exception\\FatalErrorException(code: 1): Allowed memory size of 2147483648 bytes exhausted (tried to allocate 776286208 bytes) at /home/gech/workspace/laravel/oss-statistics/vendor/symfony/http-foundation/Response.php:368)
[stacktrace]
#0 {main}
"}
My Job Class
<?php
namespace App\Jobs;
use App\Exports\PeopleExport;
use App\Models\Setting\Code\SiteCode;
use App\Models\Setting\Report;
use App\Notifications\Backend\Report\ReportDoneNotification;
use Hash;
use Illuminate\Bus\Queueable;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Foundation\Bus\Dispatchable;
use Illuminate\Queue\InteractsWithQueue;
use Illuminate\Queue\SerializesModels;
use Storage;
use Str;
class ProcessRawDataReport implements ShouldQueue
{
use Dispatchable, InteractsWithQueue, Queueable, SerializesModels;
/**
* @var Report
*/
private $report;
private $data;
/**
* Create a new job instance.
*
* @param Report $report
* @param $data
*/
public function __construct(Report $report, $data)
{
$this->report = $report;
$this->data = $data;
}
/**
* Execute the job.
*
* @return void
*/
public function handle()
{
// Create path
$path = Str::uuid()->toString(). '.csv';
// Delete if file exist
if (Storage::exists($path)) {
Storage::delete($path);
}
// Export CSV
// Send an email after done.
(new PeopleExport(
$this->getSitesCode($this->data['approved_sites']),
$this->data['approved_attributes'],
$this->getHeadings($this->data['approved_attributes'])
)
)->queue($path)->chain(
[
new ReportDoneNotification($this->report, $this->data, $path)
]
);
}
public function getHeadings($approved_attributes)
{
$headings = [];
foreach ($approved_attributes as $attribute) {
array_push($headings, $this->makeItReadable($attribute));
}
return $headings;
}
public function makeItReadable($string)
{
return ucfirst(str_replace(' ', '_', $string));
}
public function getSitesCode($approved_sites)
{
$sites = [];
foreach ($approved_sites as $label) {
$site = SiteCode::query()->where('label', $label)->first();
array_push($sites, $site->code);
}
return $sites;
}
}
<?php
namespace App\Exports;
use App\Models\Setting\Person;
use Illuminate\Contracts\Queue\ShouldQueue;
use Illuminate\Contracts\Support\Responsable;
use Illuminate\Database\Eloquent\Builder;
use Maatwebsite\Excel\Concerns\Exportable;
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithHeadings;
use Maatwebsite\Excel\Excel;
class PeopleExport implements FromQuery, ShouldQueue, Responsable, WithHeadings
{
use Exportable;
/**
* Optional Writer Type.
*/
private $writerType = Excel::CSV;
/**
* Optional headers.
*/
private $headers = [
'Content-Type' => 'text/csv',
];
/**
* @var array
*/
private $headings;
private $sites;
private $attributes;
/**
* PeopleExport constructor.
*
* @param $sites
* @param $attributes
* @param array $headings
*/
public function __construct($sites, $attributes, $headings)
{
$this->headings = $headings;
$this->sites = $sites;
$this->attributes = $attributes;
}
/**
* @return Builder
*/
public function query()
{
return Person::query()->whereIn('site', $this->sites)->select($this->attributes);
}
/**
* {@inheritdoc}
*/
public function headings(): array
{
return $this->headings;
}
}
This bug report has been automatically closed because it has not had recent activity. If this is still an active bug, please comment to reopen. Thank you for your contributions.
I am getting this issue while importing a product in bulk.
I am facing same issue @jay-r-7span when importing more than 10k records.
We face the same issue when importing large number of records.
Unfortunately same here😅.
Did you find any solution or workaround?
im trying to import 4K-10K rows with QueuedImport, but non managed to finish...
Same Here i got same issue i try to import 20 k data
<?php
namespace App\Imports;
use Maatwebsite\Excel\Concerns\Importable;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithCustomCsvSettings;
use Maatwebsite\Excel\Validators\Failure;
use Maatwebsite\Excel\Concerns\SkipsFailures;
use Maatwebsite\Excel\Concerns\ToCollection;
use Maatwebsite\Excel\Concerns\SkipsOnError;
use Maatwebsite\Excel\Concerns\SkipsOnFailure;
use Maatwebsite\Excel\Concerns\WithValidation;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
use Illuminate\Support\Facades\Auth;
use Illuminate\Validation\Rule;
use App\Models\Invoice;
use App\Models\InvoiceProduct;
use App\Models\Tax;
use App\Models\Customer;
use App\Models\ProductServiceUnit;
use App\Models\ProductServiceCategory;
use App\Models\ProductService;
use App\Models\Utility;
use Maatwebsite\Excel\Concerns\WithChunkReading;
class InvoiceImport implements ToModel, WithValidation, WithHeadingRow, WithChunkReading, WithCustomCsvSettings
{
use Importable;
use SkipsFailures;
protected $userId;
public function __construct($userId)
{
$this->userId = $userId;
}
public function model(array $row)
{
$customer = Customer::where('name', $row['customer'])->first();
$categoryInvoice = ProductServiceCategory::where('name', $row['category_invoice'])->where('type', 'income')->first();
$invoice = Invoice::where('invoice_id', $row['invoice_id'])->first();
$product = ProductService::where('sku', $row['product_sku'])->first();
$productTax = Tax::where('name', $row['product_tax'])->first();
//Formula to calculate price/pcs
$productPrice = floatval((str_replace(',', '', $row['product_price'])));
$pricePerUnit = (int) $row['product_qty_unit'] > 0 ? (int) $productPrice / (int) $product->quantity_in_unit : 0;
$subTotalItemPrice = (int) $row['product_qty'] * $productPrice;
$subTotalItemPricePerUnit = (int) $row['product_qty_unit'] * $pricePerUnit;
$totalAmount = $subTotalItemPrice + $subTotalItemPricePerUnit;
if(empty($invoice)) {
$invoice = Invoice::create([
'invoice_id' => $row['invoice_id'],
'customer_id' => $customer->id,
'issue_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['issue_date']),
'due_date' => \Carbon\Carbon::createFromFormat('m/d/Y', $row['due_date']),
'category_id' => $categoryInvoice->id,
'ref_number' => $row['ref_number'],
'status' => 1,
'created_by' => $this->userId
]);
}
$invoiceProduct = new InvoiceProduct([
'invoice_id' => $invoice->id,
'product_id' => $product->id,
'quantity' => $row['product_qty'],
'quantity_unit' => !empty($row['product_qty_unit']) ? (int) $row['product_qty_unit'] : 0,
'tax_id' => $productTax->id,
'discount' => floatval((str_replace(',', '', $row['product_discount']))),
'price' => floatval((str_replace(',', '', $row['product_price']))),
'description' => $row['product_description'],
'total_amount' => floatval($totalAmount)
]);
$invoice->items()->save($invoiceProduct);
//inventory management (Quantity)
Utility::total_quantity('minus', (int) $row['product_qty'], $invoiceProduct->product_id);
return $invoice;
}
public function rules(): array
{
return [
'*.invoice_id' => 'required',
'*.ref_number' => 'required',
'*.category_invoice' => ['required', Rule::exists('product_service_categories', 'name')],
'*.customer' => ['required', Rule::exists('customers', 'name')],
'*.issue_date' => 'required|date',
'*.due_date' => 'required',
'*.due_date' => 'required',
'*.send_date' => 'required',
// '*.status' => 'required',
'*.product_sku' => ['required', Rule::exists('product_services', 'sku')],
'*.product_qty' => 'required|integer',
'*.product_qty_unit' => 'required|integer',
'*.product_discount' => 'required',
'*.product_tax' => ['required', Rule::exists('taxes', 'name')],
'*.product_price' => 'required',
'*.product_description' => 'nullable',
];
}
public function getCsvSettings(): array
{
return [
'input_encoding' => 'ISO-8859-1'
];
}
// public function batchSize(): int
// {
// return 100; // Adjust the batch size based on your needs
// }
public function chunkSize(): int
{
return 100; // Adjust the chunk size based on your needs
}
}
Same issue. Seems not fixed yet or I am missing something??
We are experiencing this issue when using vapor, is there any update on it or can it get reopened?
Hi @garyThrels
Last time I did skip failures and tracked the rows for correction -
https://docs.laravel-excel.com/3.1/imports/validation.html?#skipping-failures
Also don't forget to define batch size and chunk size.
If this is something can help you.
Thanks
I'm using 3.1 .
When I import a excel with chunk, some job may failed.
How could I fix this?