CSV to Associative Array (One column as key and another as value)

Question

I need to convert a CSV file into an associative array, using one column as key and another as value. For example, I have this:

Application,Warehouse,Item,UPC,MFUSA Item No.,"PRODUCT DESCR1","PRODUCT DESCR2",Warehouse ID,Qty
INVENTORY:,Muscle Foods USA,1st STEP B12 LIQUID 16oz,673131100064,8890004,1st STEP B12 LIQUID 16oz,CHERRY CHARGE,11,29
INVENTORY:,Muscle Foods USA,1st STEP B12 LIQUID 16oz,673131100316,8890007,1st STEP B12 LIQUID 16oz,TROPICAL BLAST,11,26
INVENTORY:,Muscle Foods USA,1st STEP B12 LIQUID 16oz,673131100064,8890004,1st STEP B12 LIQUID 16oz,CHERRY CHARGE,12,6

I have to use the column UPC as the key and Qty as the value, so that the array looks like..

array(
    '673131100064' => '29',
    '673131100316 => '26',
    '673131100064' => '6',
);

I have tried several solutions found in Google as well as here, but none of them are close to what I need to achieve. This Question was something similar, but it was written in Python.

Sorry for my poor knowledge in PHP. Can you please guide me to the right direction?

If you read the file as a CSV, then you can create the desired array as you read each line. https://stackoverflow.com/questions/9139202/how-to-parse-a-csv-file-using-php should be a good start. — Nigel Ren, Jun 09 '20 at 12:30
This is not possible with your data. The keys of an array must be unique. The UPC '673131100064' exists twice. — jspit, Jun 09 '20 at 12:46

David Gomez · Accepted Answer · 2020-06-09T13:09:53.477

With array_map you can parse the csv data and have an array to do as you wish. Example:

// Parsing the data in csv file
$csv = array_map('str_getcsv', file('path/file.csv'));

/*At this point you already have an array with the data but
 * the first row is the header row in your csv file */

//remove header row
array_shift($csv);

$data = [];

//Walk the array and add the needed data into some another array
array_walk($csv, function($row) use (&$data) {
    $data[$row[3]] = $row[8];
});

And that's it.

However the data you show as an example has duplicate UPCs. You will overwrite some data if you want an array with the structure 'UPC' => 'Qty' . You can't have duplicate keys in an array.

If what you are looking for is to get the total Qty for each UPCs then you just need to add the already existing Qty with the new one if the UPC key already exists.

// Parsing the data in csv file
$csv = array_map('str_getcsv', file('file.csv'));

//remove header row
array_shift($csv);

$data = [];

//Walk the array and add the needed data into another array
array_walk($csv, function($row) use (&$data) {

    $data[$row[3]] = ($data[$row[3]] ? $data[$row[3]] + (int) $row[8] : (int) $row[8]);
});

Or longer but clearer.

//Walk the array and add the needed data into another array
array_walk($csv, function($row) use (&$data) {

    if(!empty($data[$row[3]]))
    {
        $data[$row[3]] += (int) $row[8];
    }
    else {
        $data[$row[3]] = (int) $row[8];
    }
});

Thanks mate, now that I have a headstart, I can manage to manipulate the data. Thanks a bunch again. — Abhik, Jun 09 '20 at 13:01

score 1 · Answer 2 · answered Jun 09 '20 at 12:37

Explanation in the comments of the code below

$array = [];
// Open file "$file" and checking that it is not empty
if (($handle = fopen($file, "r")) !== false) {
    // loop on each csv line, stopping at end of file
    while (($data = fgetcsv($handle)) !== false) {
        // Excluding header row & checking data is not empty
        if ($data[0] !== 'Application' && !empty($data[0])) {
               // fgetcsv returns an array, on your example UPC is on key 3 and Qty on key 9
               $array[] = [$data[3] => $data[9]];
        }
     }
     fclose($handle);
 }

 return $array;

Here the keys are hard-coded but maybe you have a way to put it dynamically, it depends of your code & workflow. This is just a simple (I hope) demonstration.

score 0 · Answer 3 · answered Jun 09 '20 at 13:02

I use the SplfileObject for reading. Then the first line is used as the key for all values. array_column with the column names can now be used for the desired result.

$csv = new SplFileObject('datei.csv');

$csv->setFlags(SplFileObject::READ_CSV 
  | SplFileObject::SKIP_EMPTY 
  | SplFileObject::READ_AHEAD 
  | SplFileObject::DROP_NEW_LINE
);

//Combine first row as key with values
$csvArr = [];
foreach($csv as $key => $row){
  if($key === 0) $firstRow = $row;
  else $csvArr[] = array_combine($firstRow,$row);
}

$arrQty = array_column($csvArr,'Qty','UPC');

CSV to Associative Array (One column as key and another as value)

3 Answers3