Laravel excel import excel with rows as column

Question

I have a different Excel sheet provided by the vendor. I have attached the image as a reference. Here, I want to insert it in the database in the format below. I could not find any documentation that does this straightforward. Any reference would be helpful.

Excel format

Import as below format:

Answer 1

Laravel Excel treats the import as one instantiation that loops over all the rows of the file in that same instantiation. So when you do this:

Excel::import(new CountryImport, $request->country_data); (where the $request->country_data is your file) then you can run conditional logic on it in the import.

For example, if you are wanting to save it into a database, you could use the ToModel implementation:

use Maatwebsite\Excel\Concerns\ToModel;
class CountryImport implements ToModel

then the rest of your code could look something similar to this:

public $countryColumns = [];
public $countryColumnAbbreviations = [];
public $currentRow = 0;

public function model (array $row){
    $rowColumnIndex = 0;
    while($row[$rowColumnIndex] != ""){
        if($this->currentRow == 0){
            if($rowColumnIndex > 0){
                $this->countryColumns[$rowColumnIndex] = $row[$rowColumnIndex];
            }
        }
        else if($this->currentRow == 1){
            if($rowColumnIndex > 0){
                $this->countryColumnAbbreviations[$rowColumnIndex] = $row[$rowColumnIndex];
            }
        } else {
          return new CountryDatabaseEntry([
                'country_name' => $this->countryColumns[$rowColumnIndex],
                'country_code' => $this->countryColumnAbbreviations[$rowColumnIndex],
                'weight' => $row[0],
                'rate' => $row[$rowColumnIndex]
            ]);
        }
        $rowColumnIndex++;
    }
    $this->currentRow++;

}

So what's going on in this snippet? I'm keeping track of the current row using $currentRow. From there, if the current row is equal to zero (the first row), then we loop over the $row columns (provided by lara-excel) until we find an empty string. This would indicate the column headers have ended and you're out of countries. Each header gets its value appended to the countryColumns array. Exact same logic if the current row is equal to 1 except we append it to the countryColumnsAbbreviations array instead. Because we're using the $rowColumnIndex to explicitly assign array keys, everything should always be consistent. Then, if the current row is not 0 or 1, we use $row[0] to get the weight since that's always in the first column and then add the rate based on which column we're iterating over at the moment.

Naturally since I don't have one of your csv's to test out, I can't fully validate the functionality of this code but I think it can get you on your way with no issues. You can probably be more creative than me and use only 1 array for the countries and their codes by keying the name by the short-code but for readability, and time sake, I just offered the solution I chose here. Let me know if this helps out!