I’m sitting here with yet another thing to learn. When does it end? It never does. I’ve faced that reality years ago. Just when you think you’ve learned everything that you need for your job, a new system comes around, you find a new job with entirely new skillsets to master, etc.

I work in the IT sector and the pace is outrageous. It may sound like a complaint but I personally love it. I love it because I don’t get overwhelmed anymore with anything that’s thrown at me. I see it as a learning opportunity and I know how to tackle the learning process.

How I Used to Do it

While I was in software development, I would be assigned tasks. Towards the later stages, they were usually tasks that nobody else could figure out. You sit there and you realize that this is another new programming language, library, framework, package, whatever. It’s something that you were never in contact with before.

Slight panic starts to creep in. The shoulders tense up. You ask yourself why you’re even doing this anymore. Then you remember that you have Google. All you need to know is which questions to ask. After a couple of hours, you’ve solved it and you move on hoping to never have to encounter something like this again. Tomorrow, the process repeats itself.

My New Secret Formula

It took me some time to understand the speed at which I needed to solve certain issues. It wasn’t life and death as it seemed. Sure there were production issues where you’ve never touched the code and now it’s your task to solve, but even then, if you don’t have any experience with the code that you’re facing, it’s nice to step back slightly and take a breath. Let’s go through an example and see how the process materializes.

You’re given a problem that you’ve never tackled before. The current process of updating vendor data is a highly manual task. You receive a vendor file in .xlsx format (if you’re lucky) and you must import run it through the import program that the previous developer created. Only thing is that the import program expects a specific format. Everyone that’s looked at it prior to you takes the vendor file and transforms it through Excel itself and then runs it through the importer. Some excel sheets are massive and destroy your computer when you open it up. How are you going to reformat it when you can’t even get it to open with crashing Excel? On top of everything, there are 200 of these files and each of them takes anywhere between 1 and 8 hours to transform into the standard format manually.

It’s time to take a step back and figure out a new approach to this problem. It’s overwhelming that you have 2 months to update all of this. It’s equally overwhelming when you hear other state that it used to take them 6 months to complete this. But you’re not one of them. You’re going to tackle this thing reasonably.

Even if you’ve never imported anything programmatically before, the steps are going to be similar if not identical to those that have done some sort of importing. If you’re a master importer, you may need to translate this to another example yourself.

Round of Questions

Does an importer exist already and nobody is using it? For this scenario we’re going to say it’s a partial thing. There’s an importer that grabs a CSV file with predefined columns and can import it as long as the data is valid. That means that you just have to create a transformer that transforms the data from one format to the desired format and then connect the two. Even if you don’t fully connect them, it’s still going to cut down the amount of hours drastically.

How do you crawl/read an excel sheet? This is what you don’t know. Time to start doing some research. If you’re working for someone, you’re going to most likely conform to the programming language that’s been presented to you. If you’re doing things on your own, you have a choice to do it in whatever programming language you like. Let’s say that everything is built in Laravel (PHP Framework). How do you crawl/read an excel sheet with Laravel?

After some research, you notice that there’s a package called Laravel Excel that everyone seems to be using. Might be something to look into. This is where most people will download the package into their work-environment and start experimenting with the excel sheets. This is where I take a step back. There are too many configurations that I would need to worry about and there’s already a ton of code that may make it slightly overwhelming. Install a new instance of Laravel (takes less than 5 minutes) and then follow the prompts to install Laravel Excel. Hopefully you’ve found a great tutorial that guides you through this process.

https://blog.devgenius.io/laravel-excel-704e70aa6c8

After a successful install, it’s time to start with the most basic example. If the tutorial that you’re watching/reading is going too far too quick, it’s time to abandon it and start with something simpler. How about the docs themselves? Are they easy to follow for a basic import? Whatever the case may be, all you want to do is read from an excel file and echo out a row.

The tutorial is pretty good and you’re starting to understand effortlessly how that’s done. You create a new excel document and add two rows of data to it: first name and last name. You just want to play around with it and see how data is returned and what you can do with that data.

The first test is a success. You know that there will need to be a new document that’s generated after a successful transformation. How do you create an excel document? Does this package offer that too? It does? Great!

Time for another quick test. Create a simple excel file with one row of data. You got it. At this point the pressure is already low. You know that you just need to keep building on.

Now it’s time to start looking at your production excel documents. Pick an easy one and start working through it. What are you trying to do? You’re going to read each row, extract the data that you need, and write it to a new document. You start going and it works!

You pick your next document and you start going through it. It’s a little different. You realize that you’ll need to create a new transformer for each document, but there are specific methods that each one will use. So you start optimizing slightly. You run the second one through the import and it works. You rerun the first one and it still works.

It’s time to pick a third one. This one is a mess and it has over 500,000 rows. You create your importer and you click run and it starts. After some time, PHP times out. Just as expected. How does Laravel Excel deal with large documents? It uses a queue and chunks the rows. Will that work? You’re still on your test environment. You spin everything up and boom! It works.

Is there anything else that you’re missing? Doesn’t look like it at this point. Only now is it time to create a new branch on your dev environment and start installing Laravel Excel. Sure enough there are a few errors that pop up, but you’re more relaxed at handling them now. It turns out that it’s just a configuration issue with your environmental variables. If you would have started here, you would have automatically told yourself that this will never work. But you know it works. You tried it with your test environment.

After a successful install, you move your code to your branch and test it. It works. You handle one file at a time and within a couple of weeks, you have the crawlers created for all of the transformers. The best part: not only did you finish all of the imports in record time, you’ve created an optimized approach for the next time that you have to do and it’s going to be even faster. And finally, you learned how to use a new package without getting stressed out. Victory all around.

Summary

With a little bit of research, you were able to keep a clear mind and work through the problem successfully. The one part that I did not mention is that during this process, I always use a white-board. Even if it’s not code, I draw out the steps. What happens the first, second, third, nth step? Even though this process is related to coding, you can apply it to anything. I’ve done this with installing hardwood floors, rebuilding car engines, you name it. Here’s to a stressless future.