php 将 CSV 文件导入 Laravel 控制器并将数据插入到两个表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35220048/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import CSV file to Laravel controller and insert data to two tables
提问by Devin Gray
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.
所以我完全是 Laravel 的菜鸟，我正在这里尝试一些东西。我想将一个 CSV 文件导入到两个表中，我有一个名为列表的表，它将获取列表名称和 .csv 文件client_id。
Then I have a table called customers that will get name surname contact number as well as client_idand a list_id.
然后我有一个名为 customers 的表，它将获得姓名和姓氏联系电话以及client_id一个list_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 文件，该文件将采用文件名并将其存储在列表中，然后通过 CSV 文件创建一个数组并将数据导入到带有列表和客户 ID 的客户表中。
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?
我已经完成了第一部分，它正确地插入到列表中，我现在如何从位于存储/文档中的 CSV 创建一个数组，然后将其插入到客户表中？
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');
}
}
回答by maytham-???????
There are 3 steps to read CSV file and import it in database in Laravel.
读取 CSV 文件并将其导入 Laravel 中的数据库有 3 个步骤。
- Read CSV file
- Convert it to array
- Finally create records in our database.
- 读取 CSV 文件
- 将其转换为数组
- 最后在我们的数据库中创建记录。
Before we start, I have created a sample test.csvfile and put it on my public folder under file folder:
在开始之前，我已经创建了一个示例test.csv文件并将其放在文件夹下的公共文件夹中：
name,email,password
user1,[email protected],pasxxxxxxxxxword
user2,[email protected],pasxxxxxxxxxword
user3,[email protected],pasxxxxxxxxxword
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:
步骤 1 和 2；我创建了一个名为 的辅助函数csvToArray，我现在只是把它放在我的控制器中（这个函数的灵感来自这个链接）它只是读取 CSV 文件并将其转换为数组：
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;
}
Step 3; And here is my final step, read array and insert it in our database:
第 3 步；这是我的最后一步，读取数组并将其插入到我们的数据库中：
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';
}
Note:this solution assume that you have a model in your Laravel project and has the proper table in your database.
注意：此解决方案假设您的 Laravel 项目中有一个模型，并且您的数据库中有正确的表。
if you use dd($customerArr)you will get this
如果你使用dd($customerArr)你会得到这个
回答by Martin Bean
In your store()method, create the record in your liststable, and then iterate over the contents of the CSV file and insert the data into the customerstable. You should create a relationbetween customers and lists for this purpose. You would also be better off using something like the PHP League's CSV packagefor reading such files:
在您的store()方法中，在lists表中创建记录，然后遍历 CSV 文件的内容并将数据插入customers表中。为此，您应该在客户和列表之间创建关系。您最好使用类似PHP League 的 CSV 包来读取此类文件：
public function store(AddCustomersRequest $request)
{
// Get uploaded CSV file
$file = $request->file('csv');
// Create list name
$name = time().'-'.$file->getClientOriginalName();
// Create a list record in the database
$list = List::create(['name' => $name]);
// Create a CSV reader instance
$reader = Reader::createFromFileObject($file->openFile());
// Create a customer from each row in the CSV file
foreach ($reader as $index => $row) {
$list->customers()->create($row);
}
// Redirect back to where you need with a success message
}
回答by Koushik Das
@maytham The solution by Maytham will work well. However, it will have a huge problem if you're trying to do with large data. Even if you do 1000 rows, it will create a problem, as it will make 1000 insert statement separately. I will just edit the third method by him and add my own input
@maytham Maytham 的解决方案效果很好。但是，如果您尝试处理大数据，则会出现大问题。即使你做 1000 行，它也会产生问题，因为它会单独制作 1000 条插入语句。我只会编辑他的第三种方法并添加我自己的输入
public function importCsv()
{
$file = public_path('file/test.csv');
$customerArr = $this->csvToArray($file);
$date = [];
for ($i = 0; $i < count($customerArr); $i ++)
{
$data[] = [
'column_name1' => 'value',
'column_name2' => 'value2',
.. so..on..and..on
];
//User::firstOrCreate($customerArr[$i]);
}
DB::table('table_name')->insert($data);
return 'Jobi done or what ever';
}
This will call the database once to insert as many rows as you wish. Be it 1000, 100000 or whatever. However, if you have a huge csv, this will be a problem too since you will be required to insert in chunks. Like in PostgreSQL, I have noticed that you can insert up to 65000 something rows in one statement. Maybe I am wrong about the number but there's a limit in every database and you need to look for that.
这将调用数据库一次以插入任意数量的行。无论是 1000、100000 还是其他什么。 但是，如果您有一个巨大的 csv，这也将是一个问题，因为您需要分块插入。就像在 PostgreSQL 中一样，我注意到您可以在一个语句中插入多达 65000 行。也许我对这个数字有误，但每个数据库都有一个限制，你需要寻找它。
