Laravel-excel: [Question]How to Import relational data?

[atapatel]

Prerequisites

Versions

• PHP version: 7.2
• Laravel version: 5.6
• Package version: 3.1

Description

How can i import relational data with validation using laravel-excel? i have single csv file which contains relational data like user with shop.

[GlennM]

If you have a CSV with multiple sheets and relations between the sheets, you should first import one sheet into your database. After that you'll have the ID that can be referred to by the data from the other sheet.

[maganius]

@GlennM how you get the referred id? there is not example how using relation data in different sheet I was try using the model example without luck

[SeoRoman]

The data should already be in the dataset as he stated...

IE: If Sheet 1 = Employees

ID, FNAME, LNAME
1, John, Smith
2, Jane, Doe

and Sheet 2 has Clients

ID, EMPLOYEE_ID, FNAME, LNAME
1, 1, Client to John, Smith
2, 1, Client to John, Smith
3, 2, Client to Jane, Doe

You should already have the referrer id?

[maganius]

@SeoRoman No, if the data is new then you dont have the referred id, you need insert the primary model and get the id to referrer the another sheets.

[SeoRoman]

That doesn't make any sense... He's asking about Importing (How would that data not exist in the sheets)... How would you tie the sheets to the models then if the data didn't pre-exist? You wouldn't know in his case, which "shop" goes to which "user" without that data existing already...

[maganius]

Im not mean the data dont exist in the sheet, what happen if the shops in the excel doesnt exists in the database? then its a new shop and need be added, after that you need asign the users of the shop.
how the shop is new then you don't have the shop_id to asign to user.
We are asking (i think atapatel too) is how you can assign that users to the new shop.

[SeoRoman]

so would this be a M:M table ?

Because, if it's a 1:M then the shops should have the user_id in the shops table already ?

[maganius]

shop -> one to many -> users.
how can use this library to import an excel with these two sheets? one with shops and others with users.
example:

shop sheet
id, name, description
null, Maatwebsite, a shop for clothes
1, apple, mac & iphones

users sheet
id, shop_id, name, lastname
null, shop_id?, Maganius, Test
1, 1, Seo, Roman

with that example how you get the shop_id for the user Maganius importing an excel with this library.

its a basic example one-to-many but can be escenario where you need use many-to-many also.

[SeoRoman]

Once again, the key exists right there on the table? I don't see where you are getting confused... you import the shops, and you have the ID, there's no reason to associate it with the User Model Import, since it's listed right there on your USERS sheet...

Why would you have data like that at all, you wouldn't know that Maganius is assigned to Maatwebsite shop without referencing hte ID... so you would import those without any direct connections and then simply use your client gui to do the association later... if you don't know the relationship prior to importing, then it shouldn't exist on the import (because there is no way of knowing) with the example you supplied, there's no way to know that Maatwebsite shop should be associated to Maganius user...

[maganius]

you are right, my example is wrong because I write the example base on atapatel model.
sorry for misunderstanding and confuse it.

its my example

courses sheet

N#, id, name
1, null, Charla como iniciar mi negocio
2, 20, Another course

users sheet
n_course, name, lastname
1, Maganius, Test
2, Seo, Roman

N# is the relation with the course->user not with the database, then I need insert Charla como iniciar mi negocio and associate it with Maganius user with n_course

[atapatel]

Can I have a full example?

[SeoRoman]

The issue is, no matter which way you go, whether users have courses or courses have users, you have an association setup SOMEWHERE...

N# = what? id = course_id ?

n_course = what? ...

What I am saying is that if you are importing relational data, there should be somewhere that specifies what that relation is... else it's not relational "Yet" ...

It's just 2 separate sets of entities that don't have any relational mapping...

IE: If you did something like...

Courses:
1, Whatever
2, Other

Users:
1, Z3R0
2, Atapatel

// All you have are 2 sheets of independent entities with no relational mapping...

So either you have relational mapping and you can decide what to do with it, or you don't have relational mapping, and you can't import them directly with relationships and thus, you need to have the key nullable and map the relations at a later point "AFTER" the import... Because if the sheets do not know the relationships, how is the import supposed to know the relationships?

[maganius]

you are no understanding I know my english is bad sorry for that.

in these excel you have an internal relationship, its N# and n_course in respectives sheets.
the user who is importing the excel write what user correspond to what course before importing then I need associate however if its new or an update.
if its new I need first insert the course and based on the n# and n_course I need fill the users of the other sheet.

its a full example with php without excel.

$ccourses = array(
            array('n#' => 1, 'id' => null, 'name' => 'Charla Cómo iniciar mi Negocio'),
            array('n#' => 2, 'id' => 20, 'name' => 'Basics of vue'),
        );

        $users = array(
            array('n_course' => 1, 'name' => 'Maganius'),
            array('n_course' => 2, 'name' => 'SeoRoman')
        );
        $mapping = array();
        foreach($ccourses as $course){
            $mapping[$course['n#']] = $course;
        }

        foreach($users as $user){
            $mapping[$user['n_course']]['users'][] = $user;
        }

        foreach($mapping as $course){
            if($course['id']){
                $Curso = Curso::find($course['id']);
            }else{
                $Curso = new Curso();
            }
            $Curso->name = $course['name'];
            $Curso->save();

            foreach($course['users'] as $user){
                $User = new User();
                $User->course_id = $Curso->id;
                $User->name = $user['name'];
                $User->save();
            }
        }

its the result at $mapping variable

array:2 [▼
  1 => array:4 [▼
    "n#" => 1
    "id" => null
    "name" => "Charla Cómo iniciar mi Negocio"
    "users" => array:1 [▼
      0 => array:2 [▼
        "n_course" => 1
        "name" => "Maganius"
      ]
    ]
  ]
  2 => array:4 [▼
    "n#" => 2
    "id" => 20
    "name" => "Basics of vue"
    "users" => array:1 [▼
      0 => array:2 [▼
        "n_course" => 2
        "name" => "SeoRoman"
      ]
    ]
  ]

I first make the mapping and after it insert or update the database with the information, how you can see in the foreach - users I need the course id = $User->course_id = $Curso->id;

I hope i explain a little more my issue.

[SeoRoman]

What you are doing doesn't make any sense whatsoever... I don't see why you would have users assigned to non-existent courses in the excel file... that seems like whoever is doing that data handling is doing it "VERY WRONG" and you should just have a frontend GUI handling this setup... Not the excel importing over and over...

I'll take a look at it later, when I have more time

[patrickbrouwers]

First imports the courses, have a database column where you save the "N#" (if you don't use it as primary key).

Then import the users and correlate the n_course to acourse id and insert that in the users table.

[mariavilaro]

Hi! I know this has been closed long ago, but I have the same problem. In my case, I'm developing an eshop. I want to be able to import products, categories, properties... all at once from a multisheet excel file. I structured the file like this:

Categories:

code | name
------------ | -------------
tshirts | T-shirts
jackets | Jackets & Coats

Products:

code | name | categories
------------ | ------------- | -------------
tshirt_blue | Blue T-shirt | tshirts, outlet
jacket_red | Red jacket | jackets

I don't save the "code" field anywhere in the db, I don't need it after the import, so I don't think it's useful to have it just for this. What I'm doing right now is this:

class CategoriesImport implements OnEachRow, WithHeadingRow
{
    public $imported_categories;

    public function onRow(Row $row)
    {
        $row = $row->toArray();

        $category = new Category([
            'code' => $row['code'],
            'name' => $row['name'],
        ]);

        $category->save();

        $this->imported_categories[$row['code']] = $category->id;
    }
}

With this, I have a "map" of the categories that were created. But I need to access this property imported_categories and I don't have a clue about how can I do it. I tried to access it importing one sheet at a time (using onlySheets), and firing the afterImport Event, but I can't access CategoriesImport from there as it's inside sheetImports, a protected property of the event reader object. I feel like I'm almost there. Any clue?

[mariavilaro]

I found it!

Using macros I can access the protected property sheetImports:

Reader::macro('getSheetImports', function (Reader $reader) {
    return $reader->sheetImports;
});

And then from the importer I use the afterImport event to save the imported categories in the session:

public static function afterImport(AfterImport $event)
{
    $sheet_imports = $event->reader->getSheetImports();
    if (isset($sheet_imports['categories'])) {
        Session::put('imported_categories', $sheet_imports['categories']->imported_categories); 
    }
}    

And then I can access the imported categories ids when importing the other sheets.

class ProductsImport implements OnEachRow, WithHeadingRow
{
    public $imported_categories;

    public function __construct() 
    {
        $this->imported_categories = Session::get('imported_categories'); 
    }

    public function onRow(Row $row)
    {
        $row = $row->toArray();

        $category_id = $this->imported_categories[$row['category']];

        $product= new Product([
            'name' => $row['name'],
            'category_id' => $category_id
        ]);

        $product->save();
    }
}