Home / Questions / 332751
Accepted
Rafael Monroy Rodriguez
Asked: 2020-02-28 06:23:52 +0800 CST 2020-02-28 06:23:52 +0800 CST

Insert to my database by uploading an Excel file

  • 772

Hello , I am trying to load myself base de datosthrough laravel excel 3.1information that comes from a file excelwhich I load through an inputattachment my tables of thebase de datos

enter image description here

Attached codigowhy I am doing the insert to me base de datoswith the file Excel:

Controller code (import)

namespace App\Imports;

use App\tbl_instrumentos;
use Maatwebsite\Excel\Concerns\ToModel;

class InstrumentosImport implements ToModel
 {

  public function model(array $row)
   {
     return new tbl_instrumentos([
     'ins_codigo'   => $row[0],
     'ins_observacionInicial'   => $row[1],
     'ins_claseOexactitud'    => $row[2],
  ]);
 }
}

Additionally I add the one códigoof my main controller:

class TestController extends Controller
{

 public function importExportView()
  {
  $data=DB::table('tbl_instrumentos')->orderBy('ins_id','DESC')
        ->get();
  return view('importar',compact('data'));
  }

  public function exportExcel($type) 
  {
   return Excel::download(new InstrumentosExport, 'instrumentos.'.$type);
  }

  public function importExcel(Request $request) 
  {
    $this->validate($request, [
    'select_file'  => 'required|mimes:xls,xlsx'
  ]);
  Excel::import(new InstrumentosImport,$request->import_file);

  return back()->with('success', 'Datos Excel Importados 
    Satisfactoriamente.');
  }

The import_fileone I have in my controller (import) comes from my view through a inputwhich is the following:

 <input type="file" name="import_file" />

In that example that I have, I am only collecting 3 data from myself excelbut I want to add more data, therefore, as you can see in base de datosan instrument, it has several calibrations, so when I enter all that data (I plan to enter the data of the instrument and the calibration at once) how do you determine by foraneawhich instrument is calibrated, and also the brand and type of instrument that will also be inserted at once, or enter brand and type first and then the other?

How could I structure that part, Thank you.

mysql

1 Answers

  1. Best Answer

    Look for the idone of the related model by the value of the field that the excel brings you. And in the model to which you are importing, insert the idinstead of the data that the excel brings.

    For example, if in excel you have the brand name in the column 3, you can use firstOrCreate to search your brand model for the idone that corresponds to that name, and if it doesn't exist, create it.

    Something like that:

    public function model(array $row)
{
    $marca = Marca::firstOrCreate([
        'mar_nombre' => $row[3]
    ]);

    return new tbl_instrumentos([
        // ...
        'ins_mar_id' => $marca->mar_id,
    ]);
}

    Then it is the same for the other foreign keys.

    firstOrCreate()

    The method firstOrCreatewill try to locate a database record using the given 'column/value' pairs (for your case I think it would be column: mar_nombreand value: el que tengas en el excel, supongo que el nombre de la marca). If the model cannot be found in the database, a record will be inserted with the attributes of the first parameter ( mar_nombre), along with those of the optional second parameter (I didn't put anything, because in the table tbl_marcasyou only have mar_nombreand mar_id, where I suppose it mar_idis the autoincrementing primary key).

    • 1