Trying to import an excel spreadsheet with custom headers (i.e. human readable) and match those to database fields for the excel import.
Example: Column Header is: Region (ex. North America)
Database Column header is: region_code
Any idea how to do this using maatwebsite/excel package and an Eloquent model?
There are several ways you can approach this. By default, maatwerk-excel package converts the headers to slugs.
Note: by default these attributes will be converted to a slug. You can change the default inside the config excel::import.heading. Available options are: true|false|slugged|ascii|numeric|hashed|trans|original
True and slugged will be converted to ASCII as well when excel::import.to_ascii is set to true. You can change the default separator as well inside the config.
You will be able to address the row by the slug, so Region would become region. You could see if there is a setting that suits you.
Complexity depending on if you have multiple sheets or not, you can loop through everything and save it on the Eloquent model per field by mapping it as Mark Baker mentioned in the comment on your question. I don't know if the file gets uploaded or if you grab it from your local file system, so I took the file system option for my example:
Excel::load(storage_path('app/public/excel-import.xlsx'), function($reader) {
$results = $reader->get();
foreach($results as $result) {
// Your model namespace here
$model = new \App\ImportItem();
$model->region_code = $result->region;
$model->numeric_code = $result->code;
$model->testfield = $result->test_field;
$model->save();
}
});
This isn't really easy to maintain when you have a lot of Excel files with a lot of fields, but if they are few and aren't prone to change, it could be a solution.
Another option is changing all the headers in the excel (If this even is an option) and then Mass Assign your models by replacing the content of the foreach shown above by something like:
$model = App\ImportItem::create($result->toArray());
Be aware though, that all Eloquent models by default are protected against Mass Assignment. Look into the documentation to see whether you want to set attributes fillable or guarded (Take note of the security implication mentioned). I noticed sometimes there is a field 0 => null in the items array of the CellCollection on some files to import (apparently an empty column), so you can also call it like
$reader->ignoreEmpty()->get()
Keep in mind though, that it could give unwanted results on other empty fields, so you could strip it in another way too.
Now, if you don't want to rename all columns in the Excel, you could also define mutators in your model, but I do not think that would be appropriate in this situation either. An example:
class ImportItem extends Model
{
protected $table = 'importitems';
protected $fillable = [
'region_code',
];
/*
* If you have a field region in the Mass Assignment
* and a field region_code in your database.
*/
public function setRegionAttribute($value)
{
$this->attributes['region_code'] = $value;
}
// Same for each attribute! Ugh what a mess!
Maybe I wrote all this for nothing and there is a much easier and quicker way implemented into the package and/or Laravel itself for mapping like this, sorry for wasting your time in that case.
