Laravel import data from Excel

Question

The question has been asked more than once in the community, but I’m interested in the details rather than the solution itself. So, the situation is ordinary: there are tables

Users (ID, name)
Cards (ID, number)
User_cards (user_id, card_id)

There is a form in which an excel file is selected, in which there are two columns: the full name, the number of the card and such records, say, 10,000 in this file.

And now the actual question: how best to implement checks:

A user with the same name exists in the Users table.
A card with this number exists in the Cards table.

I understand that this can be done through ordinary Eloquent

// ищем пользователя по ФИО User::where('name', $userName)->first(); // ищем карту по номеру Card::where('number', $cardNumber)->first();

But. If we were talking about pure PHP, then I would use the Prepared Statement to reduce the load on the database server, and this results in a lot of queries of the same type without saving.

To parse the excel itself, I use Laravel Excel. https://laravel-excel.com/ I put such work in the background, in Jobs, in the queue, but I still don’t understand how to work with prepared expressions in Laravel. I use MySQL 5.0.11 as a DBMS.

how best to implement checks Well, since three tables are formally replenished, then there will be three queries, where to go.
And if so, the most reasonable thing: create a temporary table, load all the data from the exclusion file into it (which is more reasonable to convert to CSV for this), add all existing users to the users table, ignoring duplicates, do the same with the cards, and then same with couples user card.
And on one record to humry - it's a bullshit, don't even bother.
My mistake, I did not indicate directly: users and maps already exist in the tables of the corresponding.
The task of "bind" cards to users on the basis of the Excel file
Moreover, the last comment (the words that users and maps are in the database) contradicts the question (the need to implement checks indicates that there are users and / or maps missing in the database).
And, I believe, it is reasonable to specify the used DBMS, including its version.
@Akina in the excel file can be anything, because I can not give a guarantee that users will load.

Akina Akina 15.1k one ten 29 · Answer 1 · 2019-05-17T10:07:25

Convert XLS / XLSX to CSV and put in secure_file_priv

Create a temporary table (the type and size of data is adjusted according to the data)

 DROP TEMPORARY TABLE IF EXISTS temp; CREATE TEMPORARY TABLE temp ( user VARCHAR(255), card VARCHAR(16) ) Engine = Memory;

Download data from CSV

 LOAD DATA INFILE 'data.csv' INTO TABLE temp;

Add the missing users, ignore the existing ones (it is assumed that there is a unique index users(name) )
```
 INSERT IGNORE INTO users (name) SELECT DISTINCT user FROM temp; 
```
We do the same with cards (it is assumed that there is a unique index of cards(number) )
```
 INSERT IGNORE INTO cards (number) SELECT DISTINCT card FROM temp; 
```

We fill up the correspondence table

 INSERT IGNORE INTO user_cards (user_id, card_id) SELECT u.id, c.id FROM users u, temp t, cards c WHERE u.name = t.user AND c.number = t.card

If necessary, delete the temporary table.
```
 DROP TEMPORARY TABLE temp; 
```

All requests can be collected in one stored procedure - then from Laravel you will only need to perform the conversion and then perform this procedure.

But the question is: let's say you need to make additional checks (the map is in the database and it is not occupied by anyone), does this happen at the sampling stage from the temporary table?

Laravel import data from Excel

1 answer 1

More articles: