phpdata-import

PHP how to map importer fields to csv first row fields?


I am importing rims from an csv file to a webshop project. nothing for sell, just a personal project for learning how importers work. I am trying to map my $EANColumn variable to the first row field name in my csv.

So currently i have a csv with the following fields :

EAN;Brand;...and-more-comming


1234-WB;WheelBrand...and-more-comming


5678-BW;BrandWheel...and-more-comming


At the moment in my importer, it works when i map:

$EANColumn     = str_replace('’', '', $importData_arr["EAN"]);

And inserting it to my database through an array :

foreach($importData_arr as $importData){
           // var_dump($importData);
            $insertData = array(
                "EAN" =>$EANColumn);
            RimsUpload::insertData($insertData);

My complete code for this part is here :

if ($request->input('submit') != null ){

      $file = $request->file('file');

      // File Details
      $filename = $file->getClientOriginalName();
      $extension = $file->getClientOriginalExtension();
      $tempPath = $file->getRealPath();
      $fileSize = $file->getSize();
      $mimeType = $file->getMimeType();

      // Valid File Extensions
      $valid_extension = array("csv");

      // 2MB in Bytes
      $maxFileSize = 2097152;

      // Check file extension
      if(in_array(strtolower($extension),$valid_extension)){

        // Check file size
        if($fileSize <= $maxFileSize){

          // File upload location
          $location = 'uploads';

          // Upload file
          $file->move($location,$filename);

          // Import CSV to Database
          $filepath = url($location."/".$filename);

          // Reading file
          $file = fopen($filepath,"r");

          $importData_arr = array();
          $i = 0;

          while (($filedata = fgetcsv($file, 1000, ";")) !== FALSE) {
             $num = count($filedata );

             $EANColumn     = str_replace('’', '', $importData_arr["EAN"]);
             $BrandColumn   = $importData_arr["Brand"];

             // 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);

           dump($importData_arr);
          // Insert to MySQL database
          foreach($importData_arr as $importData){
           // var_dump($importData);
            $insertData = array(
                "EAN" =>$EANColumn,
               "Brand"=>$BrandColumn,
               "Name"=>$importData[2],
               "Size"=>$importData[3],
               "PCD"=>$importData[4],
               "Offset"=>$importData[5],
               "Bore"=>$importData[6],
               "Color"=>$importData[7],
               "Price"=>$importData[8],
               "Stock"=>$importData[9],
               "ImageURL"=>$importData[10]);
            RimsUpload::insertData($insertData);

          }

          Session::flash('message','Import Successful.');
        }else{
          Session::flash('message','File too large. File must be less than 2MB.');
        }

      }else{
         Session::flash('message','Invalid File Extension.');
      }

    }

    // Redirect to index
    // return redirect()->action("RimsUploadController@index", [$request]);
    return response()->redirectToAction('App\Http\Controllers\RimsUploadController@index', [$request]);
  }

But the real problem is that i do not want to map my columns like [0],[1],[2],[3]... I would like to take them from the first row colum name : ["EAN"],["Brand"],["Name"],["Size"]... So if EAN is column 1 or column 7, it wont make a difference, since it will detect it by name and not by row number. So it will then be able to handle files with different column orders.

When trying to do this, i get the error : Undefined index: EAN on $EANColumn = str_replace('’', '', $importData_arr["EAN"]);

The whole point is to make an easy way to import data from suppliers through csv into mysql. then display it to the webshop site.


Solution

  • Before your while loop, extract the field names from the first row of your csv:

    $file = fopen($filepath,"r");
    $keys = fgetcsv($file, 1000, ';');
    

    Then, fetch the data lines and combine the $keys array with the data into an associative array:

    $importData_arr = [];
    while ($line = fgetcsv($file, 1000, ';')) {
        $data = array_combine($keys, $line);
        // Do something with the data
        $data['EAN'] = str_replace('’', '', $data['EAN']);
        $importData_arr[] = $data;
    }
    fclose($file);
    

    You can now access all data fields by their name, independent of the order in the CSV:

    foreach($importData_arr as $importData){
        echo $importData['EAN'];
    }
    

    As long as the field names stay intact, you don't have to change your import code when the field order or count changes.