I am trying to upload an excell file to the database in my laravel 5.4.36 project using Maaatwebsite but it is not finding some columns and reversing the table column structure. Here is my controler file:
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use App\User;
use Maatwebsite\Excel\Facades\Excel;
class DebitController extends Controller
{
public function __construct()
{
$this->middleware('auth:admin');
}
/**
* Display a listing of the resource.
*
* @return \Illuminate\Http\Response
*/
public function index()
{
return view('debit');
}
/**
* Show the form for creating a new resource.
*
* @return \Illuminate\Http\Response
*/
public function create()
{
// ...
}
/**
* Store a newly created resource in storage.
*
* @param \Illuminate\Http\Request $request
* @return \Illuminate\Http\Response
*/
public function store(Request $request)
{
// ...
}
/**
* Display the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function show($id)
{
$debits = DB::table('debits')->get();
return view('debit')->with(compact('debits'));
}
/**
* Show the form for editing the specified resource.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function edit($id)
{
// ...
}
/**
* Update the specified resource in storage.
*
* @param \Illuminate\Http\Request $request
* @param int $id
* @return \Illuminate\Http\Response
*/
public function update(Request $request, $id)
{
// ...
}
/**
* Remove the specified resource from storage.
*
* @param int $id
* @return \Illuminate\Http\Response
*/
public function destroy($id)
{
// ...
}
public function import(Request $request)
{
if($request->file('imported-file'))
{
$path = $request->file('imported-file')->getRealPath();
$data = Excel::load($path, function($reader) {
})->get();
if(!empty($data) && $data->count())
{
$data = $data->toArray();
for($i=0;$i<count($data);$i++)
{
$dataImported[] = $data[$i];
}
}
\App\Debit::insert($dataImported);
}
return back();
}
}
and this is my model
<?php
namespace App;
use Illuminate\Database\Eloquent\Model;
class Debit extends Model
{
protected $fillable = [
'client_name',
'Current',
'1_30',
'31_60',
'61_90',
'>90',
'Total',
'Total_Pd_chqs',
'By_30th_Nov',
'By_5th_Dec',
'By_15th_Dec',
'By_20th_Dec',
'By_31st_Dec',
'Balance_adjusted',
'December_Collection_tenants',
'Status'
];
}
When I try to upload an excel file that has the same column headers are the table fields, I get the error
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'december_collection_tenants' in 'field list' (SQL: insert into `debits` (`1_30`, `31_60`, `61_90`, `balance_adjusted`, `by_15th_dec`, `by_20th_dec`, `by_30th_nov`, `by_31st_dec`, `by_5th_dec`, `client_name`, `current`, `december_collection_tenants`, `status`, `total`, `total_pd_chqs`, `90`) values (-2500, -4500, 5000, -5500, 4500, 2500, -2500, -3000, 5000, Jane anyango, 30000, 500, Ok, 1000, 6500, 3000))
What could I be doing wrong?
Try updating your table fields to look similar to your fill-able array variables then remove the > form 90 because maatwebsite excel will change it to an _ and MySQL will also treat it as something else probably an empty space if you meant greater than 90 then I would recommend that you write it in full rather that using symbols. Lastly ensure you have no spaces in your table field names