时间戳

首页 > 解决方案 > Laravel：从 CSV 文件上传导入数据库表中的空值

excel - Laravel：从 CSV 文件上传导入数据库表中的空值

问题描述

我正在尝试从 csv 文件上传数据。将数据导入数据库表时，值为 NULL。我的 CSV 文件有价值。

我通过安装 Maatwebsite\Excel 完成了这个过程。

我的代码

class Template extends Model
{
    protected   $fillable = ['Origin', 'OriginState', 'Destination' , 'DestinationState' , 'Carrier', 'Car' , 'Buy', 
                              'Carbon', 'DepotFrom', 'DepotTo', 'OriginType', 'DestinationType'
                            ,'GoodsAllowed', 'NonRunners', 'ETA',  'Comments', 'Disabled' ];
}



  public function upload(Request $request)
    {
        if ($request->hasFile('uploadRates')) {
            $path = $request->file('uploadRates')->getRealPath();
            $data = \Excel::load($path)->get();

            if (!empty($data) && $data->count()) {
                foreach ($data as   $value) {
                    $arr[] = ['Origin' => $value->Origin,
                              'OriginState' => $value->OriginState,
                              'Destination' => $value->Destination,
                              'DestinationState' => $value->DestinationState,
                              'Carrier' => $value->Carrier,
                              'Car' => $value->Car,
                              'Buy' => $value->Buy,
                              'Carbon' => $value->Carbon,
                              'DepotFrom' => $value->DepotFrom,
                              'DepotTo' => $value->DepotTo,
                              'OriginType' => $value->OriginType,
                              'DestinationType' => $value->DestinationType,
                              'GoodsAllowed' => $value->GoodsAllowed,
                              'NonRunners' => $value->NonRunners,
                              'ETA' => $value->ETA,
                              'Comments' => $value->Comments,
                              'Disabled' => $value->Disabled,

                             ];
                }
                if (!empty($arr)) {
                    DB::table('template')->insert($arr);
                    return "Success";
                }
            }
        }
        dd('Requested file has no Data inside.');
    }

和我的 CSV 数据：

Origin,OriginState,Destination,DestinationState,Carrier,Car,Buy,Carbon,DepotFrom,DepotTo,OriginType,DestinationType,GoodsAllowed,NonRunners,ETA,Comments,Disabled

Adelaide,SA,Melbourne,VIC,A1 Auto Transporter,Car,250,0,0,0,Depot,Depot,0,0,5,,0 Adelaide,SA,Canberra,ACT,A1 Auto Transporter,Car,609.0909091, 0,0,0,Depot,Depot,0,0,7,,0 Adelaide,SA,Sydney,NSW,A1 Auto Transporter,Car,409.0909091,0,0,0,Depot,Depot,0,0,7, ,0 Adelaide,SA,Brisbane,QLD,A1 Auto Transporter,Car,727.2727273,0,0,0,Depot,Depot,0,0,8,,0 Adelaide,SA,Townsville,QLD,A1 Auto Transporter,Car, 1428.181818,0,0,0,Depot,Depot,0,0,12,,0 Adelaide,SA,Cairns,QLD,A1 Auto Transporter,Car,1491.818182,0,0,0,Depot,Depot,0,0, 14,,0

我的数据库截图： 在此处输入图像描述

dd($data) 输出：

RowCollection {#787 ▼
  #heading: array:17 [▼
    0 => "origin"
    1 => "originstate"
    2 => "destination"
    3 => "destinationstate"
    4 => "carrier"
    5 => "car"
    6 => "buy"
    7 => "carbon"
    8 => "depotfrom"
    9 => "depotto"
    10 => "origintype"
    11 => "destinationtype"
    12 => "goodsallowed"
    13 => "nonrunners"
    14 => "eta"
    15 => "comments"
    16 => "disabled"
  ]
  #title: "Worksheet"
  #items: array:18 [▼
    0 => CellCollection {#790 ▼
      #title: null
      #items: array:17 [▼
        "origin" => "Adelaide"
        "originstate" => "SA"
        "destination" => "Melbourne"
        "destinationstate" => "VIC"
        "carrier" => "A1 Auto Transporter"
        "car" => "Car"
        "buy" => 250.0
        "carbon" => 0.0
        "depotfrom" => 0.0
        "depotto" => 0.0
        "origintype" => "Depot"
        "destinationtype" => "Depot"
        "goodsallowed" => 0.0
        "nonrunners" => 0.0
        "eta" => 5.0
        "comments" => null
        "disabled" => 0.0
      ]
    }
    1 => CellCollection {#792 ▶}
    2 => CellCollection {#794 ▶}
    3 => CellCollection {#796 ▶}
    4 => CellCollection {#798 ▶}
    5 => CellCollection {#800 ▶}
    6 => CellCollection {#802 ▶}
    7 => CellCollection {#804 ▶}
    8 => CellCollection {#806 ▶}
    9 => CellCollection {#808 ▶}
    10 => CellCollection {#810 ▶}
    11 => CellCollection {#812 ▶}
    12 => CellCollection {#814 ▶}
    13 => CellCollection {#816 ▶}
    14 => CellCollection {#818 ▶}
    15 => CellCollection {#820 ▶}
    16 => CellCollection {#822 ▶}
    17 => CellCollection {#824 ▶}
  ]
}

标签： excellaravelcsvlaravel-5maatwebsite-excel

解决方案

这很可能是因为当您使用第一行标题作为属性时，它们会受到影响。

这意味着Origin将是$value->origin。

如果要使用原始属性，则可以将 excel.php 配置中的设置更改import.heading为orginal.

编辑：我刚刚从您的评论中意识到您的配置中的 startRow 设置为 2。将其更改为 1。

推荐阅读