php - 使用 Laravel 导入 CSV 数据

Question

我正在尝试以 CSV 文件的形式浏览并提取其所有内容。

导入内容后，我需要将它们插入到我的 mysql 数据库表中，但在执行此操作之前，我需要检查这些记录是否存在。如果记录没有，请导入此寄存器。

我有标题和所有内容，我用这些信息创建了一个数组，但我不知道它是否 100% 正确。这是我的实际代码：

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);

csv 文件的内容（它是一个示例文件）例如（我需要创建一个通用导入器）：

Array
(
    [0] => Array
        (
            [0] => [e-mail]
            [1] => david
            [2] => 005
            [3] => hola
            [4] => eee
            [5] => eee
        )

    [1] => Array
        (
            [0] => [e-mail]
            [1] => david
            [2] => 005
            [3] => hola
            [4] => eee
            [5] => eee
        )

)

还有我的标题：

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

我的问题是：这是一个好的解决方案还是有更好的解决方案来做到这一点？我需要将此数据插入到我的数据库中。怎么可能做到这一点？

更新：

模型

<?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');
    }


}

更新 2

我所有的功能代码

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);        
    }

更新 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);        
    }

标签： phplaravelcsvlaravel-5csv-import

Answer 1

根据文档：

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)