Import data with maatwebsite laravel excel in Laravel 8

Question

This is the first time that I tried to work with the Maatwebsite/Laravel-Excel package using Laravel 8.

I'm trying to import data from excel file to database table.

My table:

But I have some difficulties or questions:

In my database table I have a foreign keys, that means when I will import data I should store the foreign key instead of the value, for example: I have id_type_facturation column in my datatable so I should store the id and not the value like "Conrat".
I have another constraint that from my excel file, the column date_of_birth, I should calculate the age, and if the age is less than 18 I should store on anothe table the information of the parents and if the age is grather than 18 It shouldn't store the information in the table parents.
I have also an image column, and I should insert it in the database table.

I did all the process, but I get data from a form by using the following code:

public function store(Request $request)

{
       $representants = new Representant();
       $castings = new Casting();


       $id_filiale= Auth::user()->id_filiale;
  
       $datee = $request['date_naissance'];

       $time = strtotime($datee);

       $date = date('Y-m-d',$time);
     
      

     $age = Carbon::parse($date)->diff(Carbon::now())->y;

    
       $rules = array(

         'nom' => 'required',
         'prenom' => 'required',
         'cine' => 'required|unique:castings',
         'date_naissance' => 'required',
         'lieu_naissance' => 'required',
         'id_type_facturation' => 'required',
         'artiste' => 'required',
         'fonction' => 'required',
         'id_type_casting' => 'required',
         'tel1' => 'required',
         'tel2' => 'required',
         'email' => 'required',
         'qualification' => 'required',
         'adresse' => 'required',
         'ville' => 'required',
         'pays' => 'required'
      
            
             );


       $rules_message = array(

         'nom.required' => 'Le nom est requis',
         'prenom.required' => 'Le prénom est requis',
         'cine.required' => 'Le CINE est requis',
         'cine.unique' => 'Le CINE a déjà été pris',
         'date_naissance.required' => 'La date de naissance est requise',
         'lieu_naissance.required' => 'Le lieu de naissance est requis',
         'id_type_facturation.required' => 'Le type de facturation est requis',
         'artiste.required' => 'Est ce que c\'est un artiste ? ',
         'fonction.required' => 'La fonction est requise',
         'id_type_casting.required' => 'Le type de facturation est requis',
         'tel1.required' => 'Le numéro de téléphone 1 est requis',
         'tel2.required' => 'Le numéro de téléphone 2 est requis',
         'email.required' => 'L\'adresse email est requise',
         'qualification.required' => 'La qualification est requise',
         'adresse.required' => 'L\'adresse est requise',
         'ville.required' => 'La ville est requise',
         'pays.required' => 'La pays est requis',
      
            
             );


          $error = Validator::make($request->all(), $rules,$rules_message);

                    if($error->fails())
                    {
                        return response()->json(['errors' => $error->errors()->all()]);
                    }

      if ($age < 18) {

            

       $image = $request->file('photo');

       $new_name = rand() . '.' . $image->getClientOriginalExtension();

       $image->move(public_path('castingimages'), $new_name);

             $representants->nom_prenom = $request['nom_prenom'];
             $representants->cine = $request['cine_representant'];
             $representants->lien_casting = $request['lien_casting'];
             $representants->save();

             
             $castings->nom = $request['nom'];
             $castings->prenom = $request['prenom'];
             $castings->cine = $request['cine'];
             $castings->date_naissance = $date  ;
             $castings->lieu_naissance = $request['lieu_naissance'];
             $castings->mineur = 1;
             $castings->id_representant = $representants->id;

             $castings->id_type_facturation = $request['id_type_facturation'];
             $castings->artiste = $request['artiste'];
             $castings->fonction = $request['fonction'];
             $castings->id_type_casting = $request['id_type_casting'];
             $castings->tel1 = $request['tel1'];
             $castings->tel2 = $request['tel2'];
             $castings->email = $request['email'];
             $castings->photo = $new_name;

             $castings->qualification = $request['qualification'];
             $castings->adresse = $request['adresse'];
             $castings->ville = $request['ville'];
             $castings->pays =  $request['pays'];

             $castings->id_filiale =  $id_filiale;

             $castings->save();

            return response()->json(['success' => 'Casting/Influenceur ajouté avec succès.']);  
 
  }else

  {
     
            $image = $request->file('photo');

            $new_name = rand() . '.' . $image->getClientOriginalExtension();

            $image->move(public_path('castingimages'), $new_name);

           

             $castings->nom = $request['nom'];
             $castings->prenom = $request['prenom'];
             $castings->cine = $request['cine'];
             $castings->date_naissance = $date  ;
             $castings->lieu_naissance = $request['lieu_naissance'];
             $castings->mineur = 0;
           

             $castings->id_type_facturation = $request['id_type_facturation'];
             $castings->artiste = $request['artiste'];
             $castings->fonction = $request['fonction'];
             $castings->id_type_casting = $request['id_type_casting'];
             $castings->tel1 = $request['tel1'];
             $castings->tel2 = $request['tel2'];
             $castings->email = $request['email'];
             $castings->photo = $new_name;

             $castings->qualification = $request['qualification'];
             $castings->adresse = $request['adresse'];
             $castings->ville = $request['ville'];
             $castings->pays =  $request['pays'];
             $castings->id_filiale =  $id_filiale;


             $castings->save();

                 return response()->json(['success' => 'Casting/Influenceur ajouté avec succès.']); 
  }
    
    }

How can I do the same thing but getting the data from a excel file? If you have any ideas about that, or if you know if I can do thoes things with this package let me know.

To answer your first question, there are multiple possible solutions depending on what works best for you: a) The Excel column value is unique and can be queried using an indexed database column b) Let Excel store the ID in a hidden (using VLOOKUP or something the like) — shaedrich
– shaedrich, Commented Aug 3, 2021 at 8:11
@shaedrich , thank you for your comment , if you can explain more how can I do that — saadia
– saadia, Commented Aug 3, 2021 at 10:16

shaedrich · Accepted Answer · 2021-08-03 10:23:46Z

0

If you only have the value but not the ID but the value is unique in your database table, you can do this:

$model = new YourModel;
$model->some_id = OtherModel::where('unique_column', $request->input('some_value'))->sole()->id;
$model->save();

answered Aug 3, 2021 at 10:23

shaedrich

5,7633 gold badges31 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

saadia Over a year ago

I tried that , $casting->id_type_facturation = Type_Facturation::where('type_facturation',$row['type_facturation'] )->sole()->id ; But I get NULL value in database

shaedrich Over a year ago

Hard to say, why this happens. You know your database schema and contents better than I do. Maybe TypeFacturation::dd()->where... helps?

Collectives™ on Stack Overflow

Import data with maatwebsite laravel excel in Laravel 8

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related