将CSV文件导入Laravel控制器并将数据插入到两个表中 [英] Import CSV file to Laravel controller and insert data to two tables

问题描述

因此，我是Laravel的完全菜鸟，正在这里尝试一些东西.我想将CSV文件导入两个表，我有一个名为列表的表，该表将获取列表名称和client_id.

So I am a complete noob to Laravel and am trying something here. I want to import a CSV file into two tables, I have a table called lists that will get the list name and a client_id.

然后我有一个名为客户的表，该表将获得姓氏联系电话以及client_id和list_id.

Then I have a table called customers that will get name surname contact number as well as client_id and a list_id.

我要实现的是导入一个CSV文件，该文件将使用文件名并将其存储在列表中，然后通过CSV文件创建一个数组，并将数据与列表和客户ID一起导入到客户表中

What I want to achieve is to import a CSV file that will take the file name and store it in the list table, then create an array through the CSV file and import the data into the customers table with the list and client id's.

我已完成第一部分，并将其正确插入到列表表中，现在如何从存储/文档中的CSV创建一个数组，然后将其插入到客户表中?

I have the first part done, and it inserts into the lists table correctly, How do I now create an array from the CSV that is located in storage/documents and then insert that into the customers table?

namespace App\Http\Controllers;

use Input;
use DB;
use Illuminate\Http\Request;
use App\Http\Requests\ListsRequest;
use App\Lists;
use App\Clients;
use App\Http\Requests;
use App\Http\Controllers\Controller;

class ListsController extends Controller {

    public function index()
    {
        // $list_items = Lists::all();
        $clients = Clients::all();

        return view('lists.show', compact('clients'));
    }

    public function store(Requests\ListsRequest $request)
    {
        $input = $request->input();
        Lists::create($input);

        if (Input::hasFile('name'))
        {

            $file = Input::file('name');
            $name = time() . '-' . $file->getClientOriginalName();

            $path = storage_path('documents');

            $file->move($path, $name);

            // All works up to here
            // All I need now is to create an array
            // from the CSV and insert into the customers database
        }
    }
}

我选择使用我已经接受的答案，但我也使用其他答案并使它像这样工作.

I chose to use the answer that I had accepted but I also played with the other answer and got it to work like this.

public function store(Requests\ListsRequest $request)
{
    $input = $request->input();
    $client_id = $request->input('client_id');

    if (Input::hasFile('name'))
    {
        $file = Input::file('name');
        $name = time() . '-' . $file->getClientOriginalName();
        $path = storage_path('documents');

        Lists::create(['client_id' => $client_id, 'name' => $name]);

        $reader = Reader::createFromPath($file->getRealPath());
        // Create a customer from each row in the CSV file
        $headers = array();

        foreach ($reader as $index => $row)
        {
            if ($index === 0)
            {
                $headers = $row;
            } else
            {
                $data = array_combine($headers, $row);
                Customers::create($data);
            }
        }

        $file->move($path, $name);

        return view('clients');
    }
}

推荐答案

有3个步骤来读取CSV文件并将其导入Laravel中的数据库中.

There are 3 steps to read CSV file and import it in database in Laravel.

1. 读取CSV文件
2. 将其转换为数组
3. 最后在我们的数据库中创建记录.

在开始之前，我已经创建了一个示例test.csv文件，并将其放在文件夹下的公共文件夹中:

Before we start, I have created a sample test.csv file and put it on my public folder under file folder:

name,email,password
user1,[e-mail],pasxxxxxxxxxword
user2,[e-mail],pasxxxxxxxxxword
user3,[e-mail],pasxxxxxxxxxword

步骤1和2；我创建了一个名为csvToArray的辅助函数，我现在才将其放在控制器中(此函数的灵感来自

Step 1 and 2; I created a helper function called csvToArray, I just put it in my controller for now (this function is inspired from this link) it simply reads the CSV file and convert it to array:

function csvToArray($filename = '', $delimiter = ',')
{
    if (!file_exists($filename) || !is_readable($filename))
        return false;

    $header = null;
    $data = array();
    if (($handle = fopen($filename, 'r')) !== false)
    {
        while (($row = fgetcsv($handle, 1000, $delimiter)) !== false)
        {
            if (!$header)
                $header = $row;
            else
                $data[] = array_combine($header, $row);
        }
        fclose($handle);
    }

    return $data;
}

步骤3；这是我的最后一步，读取数组并将其插入我们的数据库中:

Step 3; And here is my final step, read array and insert it in our database:

public function importCsv()
{
    $file = public_path('file/test.csv');

    $customerArr = $this->csvToArray($file);

    for ($i = 0; $i < count($customerArr); $i ++)
    {
        User::firstOrCreate($customerArr[$i]);
    }

    return 'Jobi done or what ever';    
}

注意:该解决方案假定您的Laravel项目中有一个模型，并且数据库中有正确的表.

Note: this solution assume that you have a model in your Laravel project and has the proper table in your database.

如果使用dd($customerArr)，您将得到此

if you use dd($customerArr) you will get this

这篇关于将CSV文件导入Laravel控制器并将数据插入到两个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！