Level 102
Use dd to see your row
public function model(array $row)
{
dd($row);
Aug 28, 2022
17
Level 5
import excel resulted error
Hi, I am uploading excel and want to insert the data in database. I have used https://laravel-excel.com/ package for this Here is my excel image, for header and data :https://ibb.co/KDh2m61
But while uploading I got an error Undefined array key 1
uploadcontroller.php
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\Imports\OrdersImport;
use Maatwebsite\Excel\Facades\Excel;
class UploadController extends Controller
{
public function index()
{
//return a form to upload excel file
return view('index');
}
public function store()
{
Excel::import(new OrdersImport, request()->file('order_file'));
return 'uploaded!';
}
}
Orderimport.php
<?php
namespace App\Imports;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\ToModel;
class OrdersImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Order([
'order' => $row[0],
'order_date' => $row[1],
'order_qty' => $row[2],
'sales' => $row[3],
'ship_model' => $row[4],
'profit' => $row[5],
'unit_price' => $row[6],
'customer_name' => $row[7],
'customer_segment' => $row[8],
'product_category' => $row[9],
]);
}
}
dd($rows) gives this
^ array:10 [▼
0 => "Order ID"
1 => "Order Date"
2 => "Order Quantity"
3 => "Sales"
4 => "Ship Mode"
5 => "Profit"
6 => "Unit Price"
7 => "Customer Name"
8 => "Customer Segment"
9 => "Product Category"
]
Order.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Order extends Model
{
use HasFactory;
protected $guarded = ['id'];
}
orders Migration file
<?php
use Illuminate\Database\Migrations\Migration;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Support\Facades\Schema;
return new class extends Migration
{
/**
* Run the migrations.
*
* @return void
*/
public function up()
{
Schema::create('orders', function (Blueprint $table) {
$table->id();
$table->string('order');
$table->string('order_date');
$table->string('order_qty');
$table->string('sales');
$table->string('ship_model');
$table->string('profit');
$table->string('unit_price');
$table->string('customer_name');
$table->string('customer_segment');
$table->string('product_category');
$table->timestamps();
});
}
/**
* Reverse the migrations.
*
* @return void
*/
public function down()
{
Schema::dropIfExists('orders');
}
};
Thanks
Level 5
@Sinnbeck Updated above ^ array:10 [▼ 0 => "Order ID" 1 => "Order Date" 2 => "Order Quantity" 3 => "Sales" 4 => "Ship Mode" 5 => "Profit" 6 => "Unit Price" 7 => "Customer Name" 8 => "Customer Segment" 9 => "Product Category" ]
Level 102
@david001 ok so first line is fine. Try this instead
public function model(array $row)
{
if (! isset ($row[1]){
dd($row);
}
Level 5
@Sinnbeck i got this
if (! isset ($row[1])) {
dd($row);
}
^ array:10 [▼
0 => null
1 => null
2 => null
3 => null
4 => null
5 => null
6 => null
7 => null
8 => null
9 => null
]
Level 102
It seems that you have a heading row. Why not use it
Level 5
@Sinnbeck dd($row); gives
^ array:10 [▼
0 => "Order ID"
1 => "Order Date"
2 => "Order Quantity"
3 => "Sales"
4 => "Ship Mode"
5 => "Profit"
6 => "Unit Price"
7 => "Customer Name"
8 => "Customer Segment"
9 => "Product Category"
]
I added headers and got error in first line: Undefined array key "Order ID"
public function model(array $row)
{
return new Order([
'order' => $row['Order ID'],
'order_date' => $row['Order Date'],
'order_qty' => $row['Order Quantity'],
'sales' => $row['Sales'],
'ship_model' => $row['Ship Mode'],
'profit' => $row['Profit'],
'unit_price' => $row['Unit Price'],
'customer_name' => $row['Customer Name'],
'customer_segment' => $row['Customer Segment'],
'product_category' => $row['Product Category'],
]);
}
Undefined array key "Order ID"
Level 102
@david001 After adding with headers, do a new dd(). It will normalize your header names.
They will look something like this
public function model(array $row)
{
return new Order([
'order' => $row['order_id'],
...etc
Level 5
@Sinnbeck sorry i didn't understand, but i added new dd($row) as below
public function model(array $row)
{
dd($row);
return new Order([
'order' => $row['Order ID'],
'order_date' => $row['Order Date'],
'order_qty' => $row['Order Quantity'],
'sales' => $row['Sales'],
'ship_model' => $row['Ship Mode'],
'profit' => $row['Profit'],
'unit_price' => $row['Unit Price'],
'customer_name' => $row['Customer Name'],
'customer_segment' => $row['Customer Segment'],
'product_category' => $row['Product Category'],
]);
}
and got this
^ array:10 [▼
0 => "Order ID"
1 => "Order Date"
2 => "Order Quantity"
3 => "Sales"
4 => "Ship Mode"
5 => "Profit"
6 => "Unit Price"
7 => "Customer Name"
8 => "Customer Segment"
9 => "Product Category"
]
Level 102
@david001 That is your header row. Show the complete import class. There must be something that is set up wrong.
Are you sure you have ToModel, WithHeadingRow?
Level 5
@Sinnbeck controller
public function store()
{
Excel::import(new OrdersImport, request()->file('order_file'));
return 'uploaded!';
}
Orderimport.php class
<?php
namespace App\Imports;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\ToModel;
class OrdersImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
dd($row);
return new Order([
'order' => $row['Order ID'],
'order_date' => $row['Order Date'],
'order_qty' => $row['Order Quantity'],
'sales' => $row['Sales'],
'ship_model' => $row['Ship Mode'],
'profit' => $row['Profit'],
'unit_price' => $row['Unit Price'],
'customer_name' => $row['Customer Name'],
'customer_segment' => $row['Customer Segment'],
'product_category' => $row['Product Category'],
]);
}
}
Model
//Order.php
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Order extends Model
{
use HasFactory;
protected $guarded = ['id'];
}
Level 102
@david001 You are missing the WithHeadingRow
<?php
namespace App\Imports;
use App\Models\Order;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class OrdersImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
dd($row);
return new Order([
'order' => $row['Order ID'],
'order_date' => $row['Order Date'],
'order_qty' => $row['Order Quantity'],
'sales' => $row['Sales'],
'ship_model' => $row['Ship Mode'],
'profit' => $row['Profit'],
'unit_price' => $row['Unit Price'],
'customer_name' => $row['Customer Name'],
'customer_segment' => $row['Customer Segment'],
'product_category' => $row['Product Category'],
]);
}
}
Level 5
thanks @Sinnbeck , that fixed the problem
Level 102
@david001 Awesome :)
Level 50
Hi @david001, I faced that issue. Can you please try things Like first Create Import Class using cmd , which looks like this?
<?php
namespace App\Imports;
use App\Models\Model;
use Maatwebsite\Excel\Concerns\ToModel;
use Illuminate\Support\Facades\Auth;
class FileImport implements ToModel
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Model([
'serial' => $row[0],
'barcode' => $row[1],
// 'user_id' => {{Auth::user()->id}},
]);
}
}
Once this is completed, directly call this class in your controller.
public function store(Request $request)
{
$this->validate($request, [
'file' => 'required',
]);
$file = $request->file('file');
Excel::import(new FileImport , $file);
return response()->json(['success','Uploaded successfully!']);
}
And I think this is the simplest way to store each row in the database.
reference
Level 5
@Nihir done same thing before which you can see above but got an error
Level 50
Did you define it as fillable in model?
Please or to participate in this conversation.