How many times have you been working on a project which comes with existing data sets? And, how many times have these data sets been sent to you in excel?

Well, in this article, I will go through one of my quick, time-saving tricks that generates Seeder Code from excel data sets. I will use a sample data set and include the excel file to download so you can follow along. Alternatively, you can use your own data set, just ensure you change all field names to match your data set.

First of all, let’s set the scene with the data set.

The given data set

Ok, let’s imagine you’ve been given a data set with three columns, it has a first_name, last_name and an email column, all populated with fifty records. Please download it here.

Let’s go ahead and prepare the migration to create the table which we will call employees.

To do this, we need to execute the following command on the cli.

php artisan make:migration create_employees_table

Next, we locate the migration file that was created in database/migrations/

Open the file and edit the up() function to match the following.

public function up()
{
        Schema::create('employees', function (Blueprint $table) {
            $table->id();
            $table->string('first_name');
            $table->string('last_name');
            $table->string('email');
            $table->timestamps();
        });
}

Now to execute the migration to set up our new table. Run the following command in the cli.

Lastly, let’s create the model for the Employee table that we have just created.

php artisan make:model Employee

Once this is complete, ensure the table is visible in your database and we can move on to the next part, generating the seeder.

Using Excel To Generate Seeder Code

Now we can get into the good stuff, let’s save some time with the power of excel. Before we start this part, here’s a sample screenshot of the excel file we are going to use.

With both the knowledge of what the data looks like and the migration/table structure we now know what the seeder is going to look like.

Let’s first create a new seeder file.

php artisan make:seeder employee_table_seeder

Next, find it and open it up, it will be located under database/seeders

OK, so we now need to create the base code structure, we will work with the data from the first record in the data set.

In your seeders run() function, add the following code.

$employee = new Employee(["first_name"=> "Emmett", "last_name" => "Packington", "email" => "[email protected]"]);
$employee->save();

This code is now all we need to create the entire seeder from the excel data set. What we are now going to do is put this code into excel, cut it up, and use some formulas. This next part will be divided into steps.

Step 1 –

Open up the excel file and locate cell F1, paste in the following

$employee = new Employee(["first_name"=> "Emmett", "last_name" => "Packington", "email" => "[email protected]"]);

Next, locate cell F2 and paste in the following

$employee->save();

Your excel file should now look something like the following

Step 2 –

Next, we are going to change the way we look at this data. Each row in the data set is now going to be split into ‘variables’. A2 is the first name, B2 is the last name and C2 is the email. Keep this in mind.

We need to therefore prepare the PHP syntax to have space for these variables to be filled in when using our formula.

Go into cell F1 and delete the words Emmett, Packington, and the email [email protected].

This should leave something like the following

$employee = new Employee(["first_name"=> "", "last_name" => "", "email" => ""]);

As you can see, there are now places to fill data with our variables.

Step 3 –

Now is the time to split up this cell into substrings, make the following edits to the excel file –

1. Copy $employee = new Employee(["first_name"=> " into the cell G1
2. Copy ", "last_name" => " into cell H1
3. Copy ", "email" => " into cell I1
4. Copy "]); into J1
5. Lastly, remove any contents of F1.

This should now look something like the following. Ensure you check cells H1, I1, and J1 for the starting double quotes as sometimes excel will remove them.

Step 4 –

Now we have our split string across different cells, we can move onto the fun part, creating the formula to generate a massive load of code with our data set in prepped for seeding.

Find cell D2 and enter in the following formula (note when you see $G$1, this means the formula is locking to that specific cell).

=$G$1&A2&$H$1&B2&$I$1&C2&$J$1&" "&$F$2

Of course the above may look daunting, but all it’s really doing is concatenating all the strings together.

Let’s view it another way (But don’t use this)

=$employee = new Employee([“first_name”=> “&A2(first_name_record)&”, “last_name” => “&B2(last_name_record)&”, “email” => “&C2(email_record)&”]);& ” ” & $employee->save();

Hopefully that gives a bit more clarity to what the formula is doing.

Step 5 –

And the finale, what we are going to do now is drag and copy that formula down, parallel to each of the employee records. In cell D2, click on it to highlight it and see the little small square in the bottom right corner. Click and hold and drag this down all the way to the end of the data set.

Then once that is done, copy all the rows in the D column and paste them into the seeder file.

See the small video below

Step 6 –

Now all is left to do is run the seeder.

Add the following to your database/DatabaseSeeder.php class

$this->call(employee_table_seeder::class);

Then, execute the following command

php artisan db:seed

And boom the employees table will be seeded. Check out my fully seeded table below

And that is it, you can download the finished excel file with formulas if you want to keep it as a reference.

Download the finished version here

Summary

As you can see, this method could save hours of hard coding migrations and it isn’t restricted to just Laravel either. I’m pretty sure other coding languages have similar syntax and could benefit from this method of generating seed code from excel.

Let me know how you get on and if you find it useful, share it with your friends or co-workers!

If you have any questions, let me know in the comments or find me on Twitter – @DanEnglishby

The post Generating Laravel Seeder Code With Excel appeared first on Code Wall.