Level 73
In your import, you need to check what do you have in your $row does it includes headers? You cannot just use it like json_encode
Maybe this will be better of:
return new Customer($row);
Jan 21, 2020
9
Level 11
Dynamic Data
Importing excel sheet with dynamic data:
I have this issue
SQLSTATE[HY000]: General error: 1364 Field 'CustomerName' doesn't have a default value (SQL: insert into `customers` (`updated_at`, `created_at`) values (2020-01-21 14:27:59, 2020-01-21 14:27:59))
I want to importing data dynamically take the fields from excel sheet(title+data) columns
this is the code
this is the model:
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Customer extends Model
{
protected $fillable = [
'CustomerName', 'Gender', 'Address', 'City', 'PostalCode', 'Country',
];
}
this is the controller code:
<?php
namespace App\Http\Controllers;
use App\Customer;
use App\Exports\CustomerExport;
use App\Imports\CustomerImport;
use Illuminate\Http\Request;
use Maatwebsite\Excel\Facades\Excel;
class CustomerController extends Controller
{
/**
* Display a listing of the resource.
*/
public function index()
{
$customers = Customer::all();
return view('customer')->with('customers', $customers);
}
/**
* Import function
*/
public function import(Request $request)
{
if ($request->file('imported_file')) {
Excel::import(new CustomerImport(), request()->file('imported_file'));
return back();
}
}
/**
* Export function
*/
public function export()
{
return Excel::download(new CustomerExport(), 'customers.xlsx');
}
}
this is the import code :
<?php
namespace App\Imports;
use App\Customer;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\WithHeadingRow;
class CustomerImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Customer([
'data' => json_encode($row),
]);
}
}
this is the export:
<?php
namespace App\Exports;
use App\Customer;
use Maatwebsite\Excel\Concerns\FromCollection;
use Maatwebsite\Excel\Concerns\WithHeadings;
class CustomerExport implements FromCollection, WithHeadings
{
/**
* @return \Illuminate\Support\Collection
*/
public function collection()
{
return Customer::all();
}
public function headings(array $row): array
{
return[
'data' => json_encode($row),
];
}
}
I don't know how to fix it
Level 11
Level 73
There is no data column in the Customer table, so that's wrong.
What does the $row returns for you, is it an array of items?
You will need to create an instance of the customer, for example:
return new Customer([
'CustomerName' => $row['customer_name'],
// add all other fields here
]);
or something like that, or if the headers in the Excel file match the columns in your database, just use what I've shown you in the first reply.
DEBUG what you have in the row, using dd($row);.
Level 11
I want to importing the data from excel sheet to mysql database and dynamically create the title for the column with the data
Level 73
@mehmetanbaki you can stop refreshing the page, you will get notification by email and here on Laracasts when someone replies to your post.
As I said, you need this:
return new Customer([
'CustomerName' => $row['customer_name'],
// add all other fields here
]);
This
return new Customer([
'data' => json_encode($row),
]);
will never work!
Level 11
firstable how can I give it that row name the title will be definitely not the same in the excel sheet??
how could it create them dynamically if I making it like that ???
I think you didn't understand me ???
Level 73
No I think that you don't understand me :)
In your CustomerImport, try this:
class CustomerImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
dd($row);
return new Customer([
'data' => json_encode($row),
]);
}
}
This will tell you what data you have for each row in the Excel file.
Then you will replace each of those with the columns that exist in your DATABASE:
class CustomerImport implements ToModel, WithHeadingRow
{
/**
* @param array $row
*
* @return \Illuminate\Database\Eloquent\Model|null
*/
public function model(array $row)
{
return new Customer([
'CustomerName' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
'Gender' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
'Address' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
'City' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
'PostalCode' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
'Country' => $row[INDEX_OR_NAME_OF_THE_COLUMN],
]);
}
}
Just replace in the brackets what you see above with the index value for example $row[0] will return CustomerName, or if there is a named key, use the name..
I hope you understand now :)
Level 11
actually you didn't understand my idea and what I'm trying to do:
First when it found the column does not exist and it's field does not exist in my database, It will create it automatically and I don't have to create it manually every time ??
Second what you gave it to me didn't create any fields automatically from excel to database
that's my idea ???
Level 73
You cannot create columns in a database dynamically with an Excel import.
Please or to participate in this conversation.