How to import CSV in Laravel and Select Matching Fields – Quick Admin Panel

There are quite a lot of articles on how to import data from a CSV file to a Laravel database. But none of them touched the case of matching fields from the header to actual database columns, and sometimes we weren’t sure what columns were passed in the user’s CSV. So this tutorial will provide a broader overview of CSV import, with an example project available on Github. Let’s go!

What we built here

Let’s start by thinking about the end result – I’ll show you two screenshots.

This one represents a CSV upload form:

Next – we parse that CSV and give the user a choice of which columns represent which database fields:

And only then the message screen was imported successfully.

Now, let’s build it step by step.

If you prefer a video version of this tutorial, I have it ready here:

---

CSV files and database structure

Let’s take a simple example of a CSV file with a few people’s contact details – just first name, last name and email. Here’s our CSV:

Next – we create a migration for the table:

public function up()

    Schema::create('contacts', function (Blueprint $table) 
        $table->increments('id');
        $table->string('first_name');
        $table->string('last_name');
        $table->string('email');
        $table->timestamps();
    );

And the model app/Contact.php with content:

class Contact extends Model

   public $fillable = ['first_name', 'last_name', 'email'];

---

Import forms

This part is pretty easy. I’ve picked up a new Laravel 5.5 project, ran it php craftsman create:auth and copied login.blade.php look into the new resources/views/import.blade.php which looks like this:

@extends('layouts.app')

@section('content')
    <div class="container">
        <div class="row">
            <div class="col-md-8 col-md-offset-2">
                <div class="panel panel-default">
                    <div class="panel-heading">CSV Import</div>

                    <div class="panel-body">
                        <form class="form-horizontal" method="POST" action=" route('import_parse') " enctype="multipart/form-data">
                             csrf_field() 

                            <div class="form-group $errors->has('csv_file') ? ' has-error' : '' ">
                                <label for="csv_file" class="col-md-4 control-label">CSV file to import</label>

                                <div class="col-md-6">
                                    <input id="csv_file" type="file" class="form-control" name="csv_file" required>

                                    @if ($errors->has('csv_file'))
                                        <span class="help-block">
                                        <strong> $errors->first('csv_file') </strong>
                                    </span>
                                    @endif
                                </div>
                            </div>

                            <div class="form-group">
                                <div class="col-md-6 col-md-offset-4">
                                    <div class="checkbox">
                                        <label>
                                            <input type="checkbox" name="header" checked> File contains header row?
                                        </label>
                                    </div>
                                </div>
                            </div>

                            <div class="form-group">
                                <div class="col-md-8 col-md-offset-4">
                                    <button type="submit" class="btn btn-primary">
                                        Parse CSV
                                    </button>
                                </div>
                            </div>
                        </form>
                    </div>
                </div>
            </div>
        </div>
    </div>
@endsection

Nothing too fancy here – csv_file fields for uploading files and header checkbox, users will tell us if their CSV contains header rows.

---

Routes and Controllers

We routes/web.php looks like this:

Route::get('/', 'ImportController@getImport')->name('import');
Route::post('/import_parse', 'ImportController@parseImport')->name('import_parse');
Route::post('/import_process', 'ImportController@processImport')->name('import_process');

So three actions: import the form, parse the fields screen, and process successfully (or not).

The first one looks very simple:

class ImportController extends Controller
{

    public function getImport()
    
        return view('import');
    

That’s it, no data to pass to the view, no more logic here. For now.

One step further – let’s parse our CSV.
I’ll show you how step by step.

public function parseImport(CsvImportRequest $request)

    $path = $request->file('csv_file')->getRealPath();
    // To be continued...

The first part – actually getting the CSV file, that’s one line in Laravel. With that comes the Validation request file which is also quite simple, we just need to make sure the CSV is uploaded:

class CsvImportRequest extends public function FormRequest authorize() returns true; public function rule() returns [
            'csv_file' => 'required

Next, how to parse the data. We will have two scenarios here – with or without header row.

---

Parsing CSV without header row

Let’s take care of without header first. Imagine we have this CSV:

Then parsing CSV into array is also a one-liner.

public function parseImport(CsvImportRequest $request)

    $path = $request->file('csv_file')->getRealPath();
    $data = array_map('str_getcsv', file($path));
    // To be continued...

So we will have $data array of rows, where each row will have array of columns. Now, we can represent is as a table and give user a choice of fields.

public function parseImport(CsvImportRequest $request)

    $path = $request->file('csv_file')->getRealPath();
    $data = array_map('str_getcsv', file($path));
    $csv_data = array_slice($data, 0, 2);
    return view('import_fields', compact('csv_data'));

I’m doing the slicing of only first two lines cause they are enough to show, so user would understand which value is in which column. No need to show whole CSV here.

Now, this is how our import_fields.blade.php looks like:

<form class="form-horizontal" method="POST" action=" route('import_process') ">
     csrf_field() 

    <table class="table">
        @foreach ($csv_data as $row)
            <tr>
            @foreach ($row as $key => $value)
                <td> $value </td>
            @endforeach
            </tr>
        @endforeach
        <tr>
            @foreach ($csv_data[0] as $key => $value)    @foreach (config('app.db_fields') as $db_field)  $db_field  @endforeach    @endforeach     Import Data  

And here we have something like this – the same screenshot from above:

Some comments here. As you can see, we have another form that shows the table first two rows CSV and then one row with dropdowns for each column.

To make things easier, we store the options for columns as an array config/app.php:

'db_fields' => [
    'first_name',
    'last_name',
    'email'
]

So we called and then like config(‘application.db_fields’) and go through it.

Also, we use $loop->index structure for setting field values. Read more about Loop Variables here.

---

Save data temporarily

Now, to process the data, we need to store it somewhere – remember, in the table we only display the first two rows.

There are different ways to do that, I chose to use a separate database table for the CSV file:

Schema::create('csv_data', function (Blueprint $table) 
    $table->increments('id');
    $table->string('csv_filename');
    $table->boolean('csv_header')->default(0);
    $table->longText('csv_data');
    $table->timestamps();
);

And the model is simple app/CsvData.php:

class CsvData extends Model

    protected $table="csv_data";
    protected $fillable = ['csv_filename', 'csv_header', 'csv_data'];

So if we go back to the controller, we need to store the complete data in this table – here’s what it looks like now:

public function parseImport(CsvImportRequest $request)

    $path = $request->file('csv_file')->getRealPath();
    $data = array_map('str_getcsv', file($path));

    $csv_data_file = CsvData::create([
        'csv_filename' => $request->file('csv_file')->getClientOriginalName(),
        'csv_header' => $request->has('header'),
        'csv_data' => json_encode($data)
    ]);

    $csv_data = array_slice($data, 0, 2);
    return view('import_fields', compact('csv_data', 'csv_data_file'));

We store the file data in the database with json_encode()and passes the results to the display. Then, in our form import_fields.blade.phpwe display which file we want to process, by specifying its ID as a hidden field.

<form class="form-horizontal" method="POST" action=" route('import_process') ">
     csrf_field() 
    <input type="hidden" name="csv_data_file_id" value=" $csv_data_file->id " />

Now it’s time to actually process the data.

---

Saving data into DB

Actually, this part is also quite easy – we just need to match the dropdown values ​​with the actual column values. This is what it looks like:

public function processImport(Request $request)

    $data = CsvData::find($request->csv_data_file_id);
    $csv_data = json_decode($data->csv_data, true);
    foreach ($csv_data as $row) 
        $contact = new Contact();
        foreach (config('app.db_fields') as $index => $field) 
            $contact->$field = $row[$request->fields[$index]];
        
        $contact->save();
    

    return view('import_success');

We get data from DB, do it json_decode() (second parameter CORRECT gives us the resulting array), then repeats it.

The hardest part is probably this line:

$contact->$field = $row[$request->fields[$index]];

Here we assign values ​​to the properties, according to the dropdown values ​​from the field table.

And that’s it – look at the success!

---

Import data with header rows

Now, if we have a header row, we can do a lot more with the parsing – we can guess which field that column represents!

Not only that, we can actually take a column and assign an array key to it, so instead of this:

$row[0] = 'Taylor';
$row[1] = 'Otwell';
$row[2] = 'taylor@laravel.com';

We will have this structure:

$row['first_name'] = 'Taylor';
$row['last_name'] = 'Otwell';
$row['email'] = 'taylor@laravel.com';

We can do this by parsing the first row of the CSV manually, but there is a package that can help us do it faster: maatwebsite/excel. It’s actually used not only to import, but also export and manipulate Excel data, but we’ll just take a small part of it – loading and parsing a CSV.

So we do:

composer require "maatwebsite/excel:~2.1.0"

And then we can use it in ours Import Controller.
See: from Laravel 5.5 no need to add providers/aliases to config/app.php. Extraordinary!

So we added this:

use Maatwebsite\Excel\Facades\Excel;

And here is our “improved” parse method:

public function parseImport(CsvImportRequest $request)


    $path = $request->file('csv_file')->getRealPath();

    if ($request->has('header')) 
        $data = Excel::load($path, function($reader) )->get()->toArray();
     else 
        $data = array_map('str_getcsv', file($path));
    

    if (count($data) > 0) 
        if ($request->has('header')) 
            $csv_header_fields = [];
            foreach ($data[0] as $key => $value) 
                $csv_header_fields[] = $key;
            
        
        $csv_data = array_slice($data, 0, 2);

        $csv_data_file = CsvData::create([
            'csv_filename' => $request->file('csv_file')->getClientOriginalName(),
            'csv_header' => $request->has('header'),
            'csv_data' => json_encode($data)
        ]);
     else 
        return redirect()->back();
    

    return view('import_fields', compact( 'csv_header_fields', 'csv_data', 'csv_data_file'));

I’ve highlighted the new sections added:

• Parse data with Excel::load
• Gets the header columns into a separate array $csv_header_fields

The most awesome part of this Excel package is that it automatically converts header values ​​to sluggable. So, instead $row[‘First name’] = ‘Taylor’; we will have it $data[‘first_name’] = ‘Taylor’;. Maybe that’s the main advantage of using it – this way we can try to guess the column.

And that’s what we’re going to do import_fields.blade.php:

<form class="form-horizontal" method="POST" action=" route('import_process') ">
     csrf_field() 
    <input type="hidden" name="csv_data_file_id" value=" $csv_data_file->id " />

    <table class="table">
        @if (isset($csv_header_fields))
        <tr>
            @foreach ($csv_header_fields as $csv_header_field)
                <th> $csv_header_field </th>
            @endforeach
        </tr>
        @endif
        @foreach ($csv_data as $row)
            <tr>
            @foreach ($row as $key => $value)
                <td> $value </td>
            @endforeach
            </tr>
        @endforeach
        <tr>
            @foreach ($csv_data[0] as $key => $value)
                <td>
                    <select name="fields[ $key ]">
                        @foreach (config('app.db_fields') as $db_field)
                            <option value=" (\Request::has('header')) ? $db_field : $loop->index "
                                @if ($key === $db_field) selected @endif> $db_field </option>
                        @endforeach
                    </select>
                </td>
            @endforeach
        </tr>
    </table>

    <button type="submit" class="btn btn-primary">
        Import Data
    </button>
</form>

Again, I’ve highlighted the new code here – basically, we’re using the column names as dropdown values ​​now, and also trying to guess whether the CSV column names are the same as our database column names – then it automatically suggests dropdown values.

Finally, the processing of those imports doesn’t look much different – ​​another if statement to check if we have header rows.

public function processImport(Request $request)

    $data = CsvData::find($request->csv_data_file_id);
    $csv_data = json_decode($data->csv_data, true);
    foreach ($csv_data as $row) 
        $contact = new Contact();
        foreach (config('app.db_fields') as $index => $field) 
            if ($data->csv_header) 
                $contact->$field = $row[$request->fields[$field]];
             else 
                $contact->$field = $row[$request->fields[$index]];
            
        
        $contact->save();
    

    return view('import_success');

So, that’s it, the project is complete, feel free to use and develop it as you like.
Here’s a link to the GitHub repository:

As a final notice, you might want to play around with more validation rules, bigger CSV files and see how much faster it is to parse them with native PHP rather than Excel packages, or maybe you’ll find a more efficient way? Let me know in the comments.