phplaravelcsvlaravel-5csv-import

Import CSV data using Laravel


I'm trying to walk through as CSV file and extract all its contents.

After the contents are imported I need to insert them into my mysql database table but before I can do that I need to check if these records exist. If a record does not, import this register.

I've got headers and all content and I had created a arrays with this information, but I don't know if it's 100% correct. This is my actual code:

function csv_content_parser($content) {
        foreach (explode("\n", $content) as $line) {
          // Generator saves state and can be resumed when the next value is required.
          yield str_getcsv($line);
        }
    }

$archivo = fopen($route, "r");
        
        // Get content from csv file.
        $content = file_get_contents($adjunto);
        // Create arrays from csv file's lines.
        $data = array();
        $headers = array();
        $rows = array();
        //get all content
        foreach ($this->csv_content_parser($content) as $fields) {
            array_push($data, $fields);
        }
        //get headers
        foreach($data[0] as $dat){
            array_push($headers, $dat);
        }
        //get all content of csv without headers
        for($i=1; $i<count($data); $i++){
            array_push($rows, $data[$i]);
        }
        
        //close the file
        fclose($archivo);

The content of the csv file (it's an example file) is for example (I need to create a generic importer):

Array
(
    [0] => Array
        (
            [0] => ggggg@gmail.com
            [1] => david
            [2] => 005
            [3] => hola
            [4] => eee
            [5] => eee
        )

    [1] => Array
        (
            [0] => ggggg@gmail.com
            [1] => david
            [2] => 005
            [3] => hola
            [4] => eee
            [5] => eee
        )

)

And my headers:

Array
(
    [0] => Email
    [1] => Name
    [2] => Identification
    [3] => Note
    [4] => Field Label 1
    [5] => Field Label 2
)

My question is: Is this a good solution or is there any better solution to do this? I need to insert this data into my database. How would can to do this?

updated:

model

<?php

namespace App;

use Illuminate\Database\Eloquent\Model;

class Listado extends Model
{
    protected $table = 'listado';

    protected $fillable = [
        'nomape', 'direccion', 'provincia', 'ciudad', 'cp', 'telefono', 'movil', 'id_teleoperadora'
    ];

    /**
     * The attributes that should be hidden for arrays.
     *
     * @var array
     */
    protected $hidden = [];

    public function scopeTodos( $query )
    {
        return $query->orderBy('nomape',   'ASC')->orderBy('telefono', 'ASC');
    }

    public function scopeSinAsignar( $query, $id_llamadas )
    {
        $query->whereIn('id', $id_llamadas)->whereNull('id_teleoperadora');
    }

    public static function Filtrado($request)
    {
        $query = self::query();

        if($request['direccion']) {
            $query->where('direccion', 'like', '%' .$request['direccion']. '%');
        }

        if($request['ciudad']) {
            $query->where('ciudad', $request['ciudad']);
        }

        if($request['cp']) {
            $query->where('cp', $request['cp']);
        }

        /*if($request['teleoperadora']) {
            $query->where('id_teleoperadora', $request['teleoperadora']);
        }*/

        return $query;
    }

    public function scopeConEstado( $query, $nombreEstado )
    {
        return $query->whereHas('llamada.estado', function ($query) use ($nombreEstado) {
            $query->whereNombre($nombreEstado);
        })->orWhereDoesntHave('llamada');
    }

    public function scopeConEstados( $query, $nombresEstado )
    {
        return $query->whereHas('llamada.estado', function ($query) use ($nombresEstado) {
            $query->whereIn('nombre', $nombresEstado);
        })->orWhereDoesntHave('llamada');
    }

    public function llamada()
    {
        return $this->hasOne('App\Llamada', 'id_listado', 'id')->latest();
    }

    public function llamada_test()
    {
        return $this->hasOne('App\Llamada', 'id', 'id_listado');
    }


    /**
     * RETURN OPERATOR
     */
    public function teleoperadora()
    {
        return $this->hasOne('App\User', 'id', 'id_teleoperadora')->withDefault(['nombre' => 'NINGUNA']);
    }

    /**
     * RETURN CALL DATA
     */
    public function callStatusName()
    {
        return $this->hasOne('App\llamada', 'id_listado', 'id');
    }


}

update 2

all my function code

function csv_content_parser($content) {
        foreach (explode("\n", $content) as $line) {
          // Generator saves state and can be resumed when the next value is required.
          yield str_getcsv($line);
        }
    }

    public function uploadListing(Request $request)
    {
        $adjunto = $request->file('attached');
        $route = "";

        if(isset($adjunto)){
            $name = $adjunto->getClientOriginalName();
            $result = $adjunto->storeAs('importaciones', $name, 's4');

            $route =  public_path('storage/importaciones/'.$name);
        }else{
            return "Error al adjuntar recibo de envío, consulte al administrador del sistema";
        }


        //Abrimos nuestro archivo
        $archivo = fopen($route, "r");
        
        // Get content from csv file.
        $content = file_get_contents($adjunto);
        // Create arrays from csv file's lines.
        $data = array();
        $headers = array();
        $rows = array();
        //get all content
        foreach ($this->csv_content_parser($content) as $fields) {
            array_push($data, $fields);
        }
        //get headers
        foreach($data[0] as $dat){
            array_push($headers, $dat);
        }
        //get all content of csv without headers
        for($i=1; $i<count($data); $i++){
            array_push($rows, $data[$i]);
        }

        Listado::insertOrIgnore(array_map(function($row) {
            return array_combine($headers, $row);
        }, $rows));

        
        //Cerramos el archivo
        fclose($archivo);        
    }

updated 3

function csv_content_parser($content) {
        foreach (explode("\n", $content) as $line) {
          // Generator saves state and can be resumed when the next value is required.
          yield str_getcsv($line);
        }
    }

    public function uploadListing(Request $request)
    {
        $adjunto = $request->file('attached');
        $route = "";

        if(isset($adjunto)){
            $name = $adjunto->getClientOriginalName();
            $result = $adjunto->storeAs('importaciones', $name, 's4');

            $route =  public_path('storage/importaciones/'.$name);
        }else{
            return "Error al adjuntar recibo de envío, consulte al administrador del sistema";
        }


        //Abrimos nuestro archivo
        $archivo = fopen($route, "r");
        
        // Get content from csv file.
        $content = file_get_contents($adjunto);
        // Create arrays from csv file's lines.
        $data = array();
        $headers = array();
        $rows = array();
        //get all content
        foreach ($this->csv_content_parser($content) as $fields) {
            array_push($data, $fields);
        }
        //get headers
        foreach($data[0] as $dat){
            array_push($headers, $dat);
        }
        //get all content of csv without headers
        for($i=1; $i<count($data); $i++){
            array_push($rows, $data[$i]);
        }

        Listado::insertOrIgnore(array_map(function($row) use($headers) {
            return array_combine($headers, $row);
        }, $rows));

        
        //Cerramos el archivo
        fclose($archivo);        
    }

Solution

  • As per docs:

    DB::table('listado')->insertOrIgnore(array_map(fn($row) => array_combine($headers, $row), $rows));
    

    Since you have Eloquent models, you can use Eloquent's insert() method:

    Listado::insertOrIgnore(array_map(fn($row) => array_combine($headers, $row), $rows));
    

    Since you said the following

    I need to check if these records exist. If a record does not, import this register.

    I chose the method insertOrIgnore() but maybe you want something else. Luckily, Laravel comes with a bunch of similar methods for slightly different use cases.

    Prior to PHP 7.4, array_map(fn($row) => array_combine($headers, $row), $rows) has to be:

    array_map(function($row) use($headers) {
        return array_combine($headers, $row);
    }, $rows)