Laravel import excel large files

 1) Install Laravel excel in laravel existing project

https://docs.laravel-excel.com/3.1/getting-started/installation.html

2)Create new class Import.php

php artisan make:import SomeImport 

config->excel.php

'transactions' => [

'handler' => 'null',

'db' => [

'connection' => null,

],

],

controller Function

public function storeDataupload(Request $request)

{

$request->validate([

'data_file' => ['required', 'mimes:csv,xls,xlsx,ods']

]);

$headings = (new HeadingRowImport)->toArray($request->file('data_file'));

$firstSheetHeadings = $headings[0][0];

$updatedheading = [];

foreach ($firstSheetHeadings as $key => $value) {

if (is_string($value)) {

$updatedheading[] = $value;

}

}

$dataType = DataType::find($request->data_type_id);

$tableName = strtolower($dataType->type_name) . "_" . date("d_m_y_H_i_s") . "_" . rand(11, 99);

$query = "CREATE TABLE {$tableName} (id bigint(20) UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,";

$columns = $updatedheading;

foreach ($columns as $columnindex => $columnName) {

if ($columnName != "") {

$query .= "{$columnName} TEXT ,";

}

}

$query .= "created_at timestamp NULL DEFAULT NULL ,";

$query .= "updated_at timestamp NULL DEFAULT NULL ";

$query .= ");";

$results = DB::select(DB::raw($query));

$result=UploadedData::create([

'data_type_id' => $request->data_type_id,

'state_id' => $request->state_id,

'data_table_name' => $tableName

]);

$res = Excel::import(new UploadedDataImport($tableName,$columns), $request->file('data_file'));

// $reader = new Xlsx();

// $reader->setReadDataOnly(true);

// $spreadsheet = $reader->load($request->file('data_file'));

// $sheet = $spreadsheet->getSheet($spreadsheet->getCellXfByIndex(1));

// $data = $sheet->toArray();

// echo "<pre>";

// print_r(print_r($data[0], true));

// exit;

//

}

SomeImport.php

<?php

namespace App\Imports;

use Carbon\Carbon;

use Exception;

use Illuminate\Contracts\Queue\ShouldQueue;

use Illuminate\Support\Collection;

use Illuminate\Support\Facades\DB;

use Maatwebsite\Excel\Concerns\ToCollection;

use Maatwebsite\Excel\Concerns\WithBatchInserts;

use Maatwebsite\Excel\Concerns\WithChunkReading;

class UploadedDataImport implements ToCollection, WithChunkReading,ShouldQueue

{

public $table_name;

public $allColumns = [];

public function __construct($table_name, $allColumns)

{

$this->table_name = $table_name;

$this->allColumns = $allColumns;

}

/**

* @param Collection $collection

*/

public function collection(Collection $collection)

{

$records = [];

foreach ($collection as $index => $row) {

foreach ($this->allColumns as $allColumnsKey => $singleColumn) {

$records[$index][$singleColumn] = $row[$allColumnsKey];

}

$arr = ['created_at' => date('Y-m-d H:i:s')];

$records[$index] = array_merge($records[$index], $arr);

}

unset($records[0]);

// dd($records);

try {

// // begin transaction

DB::beginTransaction();

$result = DB::table($this->table_name)->insert($records);

if (!$result) {

throw new Exception('Custom exception!');

}

DB::commit();

return $result;

} catch (\Exception $e) {

DB::rollback();

return "Not done";

}

}

public function chunkSize(): int

{

return 500;

}

public function retryUntil()

{

return now()->addSeconds(5);

}

// public function batchSize(): int

// {

// return 50;

// }

}

s