How To Import A Csv / Excel File In Laravel
A STEP BY STEP GUIDE TO IMPORTING A CSV / EXCEL FILE IN YOUR LARAVEL APPLICATION
In this series of articles, I am going to show how you can easily read and import data from a csv file or excel into your database and export data into a csv file or excel using Laravel in your Laravel API or web application. There are myriads of scenarios in which you would want to do this in your application. For instance, suppose you need to register a set of students in your school management application or you need to onboard some professionals for a scheme. You can easily do it on a one by one basis. However, if the records to be added are homogenous and in their tens or hundreds or even thousands, performing this task manually can be very tasking, laborious and a waste of time. Not forgetting the fact that it is not so good for the user experience. Ultimately, as a programmer, devising solutions to these sorts of problems should be one of your greatest assets.
Due to ease of setting up, I will be using a REST API service in this tutorial. It works exactly the same way for a web application.
As mentioned above, this is going to be a series. In this first part, I will be showing how to import data from a csv or excel file without any package or plug in. Just pure PHP / Laravel codes. Thereafter I am going to do the same using a Laravel plugin. And in the third part, I am going to show how to export the data back to a csv or excel file.
Prerequisite
- Basic knowledge of PHP / Laravel
Shall we begin!!
1. Get a csv/excel file
Get a csv or excel file you will like to upload its content to the database. The file I intend to use is shown below.
It is just a list of professional football players with their clubs, positions, ages and salaries. Imagine that you have a fashion line and you intend to onboard a set of players into your advertisement program. Now, the task is this: Onboard these players whose details are given in the csv / excel file, send an email to them to inform them, and save their details into the database. Note: You can download the excel and csv files from the project repository here (in the public folder). You can also create your own csv or / and excel files instead. The next step is to create a Laravel application.
2. Create a fresh Laravel application
Run this command composer create-project laravel/laravel content-upload
3. Create / migrate tables
The next step is to create a database, connect it to your application. Then, create a migration file with same columns as the ones you have in the excel /csv file, and migrate the table.
Run php artisan make:migration create_players_table –create=players to create your migration file.
Also, update your migration file’s up function as shown below
Schema::create('players', function (Blueprint $table) {
$table->id();
$table->string('name');
$table->string('club');
$table->string('email');
$table->string('position');
$table->integer('age');
$table->float('salary');
$table->timestamps();
});
Finally, run “php artisan migrate” to create the tables
4. Create model and controller
a. Run php artisan make:model Player to create our Player model. However, we want to be able to create our records through mass assignment, hence we need to add the fields to our fillable array as shown below.
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Factories\HasFactory;
use Illuminate\Database\Eloquent\Model;
class Player extends Model
{
use HasFactory;
protected $fillable = [
'name',
'club',
'email',
'position',
'age',
'salary'
];
}
b. Create the players controller file by running the command php artisan make:controller Player controller.
This is the controller file. Although it is self explanatory, I will explain some lines of code in the controller.
<?php
namespace App\Http\Controllers;
use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;
use App\Models\Player;
use Illuminate\Support\Facades\Mail;
use Illuminate\Http\Response;
class PlayerController extends Controller
{
public function uploadContent(Request $request)
{
$file = $request->file('uploaded_file');
if ($file) {
$filename = $file->getClientOriginalName();
$extension = $file->getClientOriginalExtension(); //Get extension of uploaded file
$tempPath = $file->getRealPath();
$fileSize = $file->getSize(); //Get size of uploaded file in bytes
//Check for file extension and size
$this->checkUploadedFileProperties($extension, $fileSize);
//Where uploaded file will be stored on the server
$location = 'uploads'; //Created an "uploads" folder for that
// Upload file
$file->move($location, $filename);
// In case the uploaded file path is to be stored in the database
$filepath = public_path($location . "/" . $filename);
// Reading file
$file = fopen($filepath, "r");
$importData_arr = array(); // Read through the file and store the contents as an array
$i = 0;
//Read the contents of the uploaded file
while (($filedata = fgetcsv($file, 1000, ",")) !== FALSE) {
$num = count($filedata);
// Skip first row (Remove below comment if you want to skip the first row)
if ($i == 0) {
$i++;
continue;
}
for ($c = 0; $c < $num; $c++) {
$importData_arr[$i][] = $filedata[$c];
}
$i++;
}
fclose($file); //Close after reading
$j = 0;
foreach ($importData_arr as $importData) {
$name = $importData[1]; //Get user names
$email = $importData[3]; //Get the user emails
$j++;
try {
DB::beginTransaction();
Player::create([
'name' => $importData[1],
'club' => $importData[2],
'email' => $importData[3],
'position' => $importData[4],
'age' => $importData[5],
'salary' => $importData[6]
]);
//Send Email
$this->sendEmail($email, $name);
DB::commit();
} catch (\Exception $e) {
//throw $th;
DB::rollBack();
}
}
return response()->json([
'message' => "$j records successfully uploaded"
]);
} else {
//no file was uploaded
throw new \Exception('No file was uploaded', Response::HTTP_BAD_REQUEST);
}
}
public function checkUploadedFileProperties($extension, $fileSize)
{
$valid_extension = array("csv", "xlsx"); //Only want csv and excel files
$maxFileSize = 2097152; // Uploaded file size limit is 2mb
if (in_array(strtolower($extension), $valid_extension)) {
if ($fileSize <= $maxFileSize) {
} else {
throw new \Exception('No file was uploaded', Response::HTTP_REQUEST_ENTITY_TOO_LARGE); //413 error
}
} else {
throw new \Exception('Invalid file extension', Response::HTTP_UNSUPPORTED_MEDIA_TYPE); //415 error
}
}
public function sendEmail($email, $name)
{
$data = array(
'email' => $email,
'name' => $name,
'subject' => 'Welcome Message',
);
Mail::send('welcomeEmail', $data, function ($message) use ($data) {
$message->from('welcome@myapp.com');
$message->to($data['email']);
$message->subject($data['subject']);
});
}
}
First and foremost, I created two extra functions in the controller, checkUploadedFileProperties and sendEmail to reduce our code in the uploadContent function and to separate tasks. The former checks if the extension of the uploaded file is valid and if the size is within the limit we set. The latter on the other hand, helps send emails to each of the onboarded players.
So, basically the uploadContent function does the following in performing it’s task
a. Checks to see if a file was uploaded and check the properties of the file uploaded
b. Secondly, creates an “uploads” folder and move the uploaded file to that folder. The created folder is shown below
c. Opens the uploaded file, reads the contents of the uploaded file, store them in an array () and then close the file after reading.
Note that the fgetcsv function is used to parse a line from an open file, checking for csv fields. It works for csv files (csv and xlsx files in this case).
Also note that the 1000 is the limit of the length of every line you read. In essence, you can only read 1000 byte of data for each line.
d. Loops through the array, and creates a record in the database with the individual item by mass assignment. Afterwards, a mail is sent to each created player. Note that, we are using a database transaction. It is not compulsory actually. However, it helps us to safely perform the bulk creation process. If any error occurs during the creation process, the transaction is rolled back in the catch block.
Note that if the number of records is quite large, it is advisable to use Laravel Queue to dispatch the emails in the background and speed up our web requests.
5. Create email blade file
Like I said, after the creation of each user, we are interested in sending an email to them. As you can see in the sendEmail function of the PlayerController, we have to create a welcomeEmail.blade.php file in our resources/views folder. We create just a basic mail template as shown below
Hello <b>{{ $name }}</b>,
<p>Welcome to our service</p>
6. Register API route.
Register your API route in api.php as shown below
Route::post('/upload-content',[PlayerController::class,'uploadContent'])->name('import.content');
Do not forget to import the PlayerController controller class in your api.php
7. Start Server and test the application on Postman
Start your server. After starting your server, open postman. Input the right url, set the request for the right method and also send your request body as a form data as shown below. On sending, you will get the message below
If you have properly set up your mail service, you should get the emails sent to the users as shown below:
And finally, if we check our database, we’d see that all the records have been successfully uploaded.
In conclusion, you can see that with few lines of code, you can successfully and safely import the contents of a csv or excel file into your database in your Laravel application. Up next, is the second and third parts of this tutorial series. You can follow me here on medium or twitter (link here) to be notified of my next article.
Stay safe.