Generate data on the fly

[Albvadi]

First of all, thank you for your package and your support. There are any way to inserta data generate on the fly?

Explanation: I want generate slugs on the fly, based on other columns in the excel. For example:

ID	NAME
1	This is the First Name
2	This is the Second Name

In my database, I have three columns: id, name and slug, but I cannot fill the slug If I not create a new column SLUG in the excel.

I tried with two forms:

• with $defaults like:

$set->defaults = [
     'slug' => Str::of($value)->slug('-')
];

but it´s not possible, because $value not exists and $defaults it´s only for fixed data.

• with $parsers like:

$set->parsers = ['slug' => function ($value) {
     return Str::of($value)->slug('-');
}];

but not works, because ther is not a slug column and the parser is not executed...

Do you have any idea how to do what I need? Thank you!

[bfinlay]

Thanks for the kind words.

I ran into a similar issue when working on #21. In that issue, the goal was to populate a UUID automatically, but it only works if you add an empty UUID column to the spreadsheet. In the case of UUID, I wanted it to work more like an auto-increment ID column, and you don't have to add the ID column to a spreadsheet to populate an auto-increment ID column. Nor would you need to in an SQL query.

In this situation, if you were writing an equivalent SQL query you would have to specify the column. So it could make sense that you need to create a column in the spreadsheet. But on the other hand if the spreadsheets are the output of some other process it could be tedious to add a column to every sheet.

Another possibility that I considered earlier was that I could check the table to see if the column exists (ie 'slug') and if it does run the parser to generate the value. I cannot run the parser on every table indiscriminately because most tables will not have the column. But I could check for the column and run it if it exists. But then it will run it for every table. For #21 that was an issue because ID columns would not always be UUID, sometimes they would be auto-increment. Looking at my local HEAD I have this solution in-progress/complete, with the caveat that it runs for every table.

What I am working on in local HEAD is a solution that allows you to configure settings based on the filename or sheet name. For example in your settings() function you would be able to check

if (in_array($this->sheetName,  ["Blog Posts 1", "Blog Posts 2"])) {
  $set->parsers = ['slug' => function ($value) {
     return Str::of($value)->slug('-');
  }];
}

This would only be needed if you want to limit which tables to apply a parser to. If you want to apply the slug to all tables you wouldn't need to check the sheet name or file name.

Would that work for you?

Let me know if you have any other opinions on how you would like to see it work, considering the edge cases above.

[Shujee]

My task will also benefit from a generic "bring your own value" feature. Best if it could take both fixed values as well as a callback. For one, this could be handy when inserting child rows in a table that need to reference a parent row that was generated on-the-fly and therefore the parent Id could not be known beforehand.

[bfinlay]

@Shujee Do you have an example of what you want to do? What do the children rows look like? What do the parent rows look like? What is an example of what you are trying to generate on-the-fly?

[Albvadi]

Thank you for your explanation @bfinlay. Unfortunately, your suggestion wouldn't work for my particular case, because my problem is that slug depends from other column and have and empty column in excel not solve the problem. A workaround is duplicate the column wich have the desired value and apply the parser, but it´s a bit ugly.

Thinking on it, in the same way that @Shujee suggest, I think the best way in this case is improve the $defaults options to allow callbacks and/or access to other columns value and with this solve all problems.

For example:

• for fixed data as is:

$set->defaults = [
     'slug' => "my fixed data"
];

• for values from others columns or values on the fly, solves my case and #21 (I think):

$set->defaults = [
     'slug' => Str::of($row["name"])->slug('-') // produces "this-is-the-first-name"
     'slug' => Str::of($row["id"] . $row["name"])->slug('-') // produces "1-this-is-the-first-name"
     'uuid' => (string) Str::uuid() 
];

• for callbacks:

$set->defaults = [
    'slug' => function ($row) {
        $group_desc = Group::find($row["id"])->pluck("group_desc");
        return Str::of($group_desc)->slug('-');
    },
    'uuid' => function ($row) {
        return (string) Str::uuid();
    }
];

I don't know how much work it is and it also has a drawback and that is that you have to have an empty column in Excel, but I can't think of any other solution other than creating a new configuration option that accepts the name of the database column and accept a callback, to allow columns to be filled in on the fly without the need for them to exist in Excel

[Shujee]

@bfinlay Thanks for asking. I later found that my scenario is already supported by the package. I'll still explain it:

The parent row in my case is generated by (PHP) code, while the child rows are imported from the Excel workbook. During the import, I need to supply this parent row's id to establish the one-to-many relationship of these child rows with the parent row. For example, say we have a Posts table and a Comments table. Each Post can have one or more Comments. The Post row is generated by the code, while the Comments are imported from the Excel file. These Comment rows need to fill their post_id field with the value 1 (or whatever id was generated by the PHP code for the Post row).

I found that this can be done by this code:

$post = Post::create([
        'title' => 'Some title',
     ]);

$set->file = '/database/seeders/comments.xlsm';
$set->defaults = ['post_id' => $post->id];