Question 254394 in PHP Question posted in
Official manual can be found here.

PHP – Reading a CSV row by row and adding values to different variables based on the contents of the row

Good afternoon. I have a CSV file and reading it row by row (skipping the header row). The CSV contents look like this:

AccountNumber,CallStartDateTime,CallType,Duration,PerMinRate,Amount,SubAccountID
5004017,9/24/2023 15:29,WVO,6,0.003,0.0003,18499
5004017,9/24/2023 21:37,WVO,54,0.003,0.0027,18499
5004017,9/24/2023 21:38,INTERSTATE,60,0.00211,0.00211,18499
5004017,9/25/2023 11:30,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:34,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:43,INTERSTATE,54,0.001,0.0009,19203
5004017,9/25/2023 13:52,WVO,6,0.003,0.0003,18499
5004017,9/25/2023 13:43,WVO,54,0.003,0.0027,19203
5004017,9/25/2023 14:00,WVO,48,0.003,0.0024,19203

In summary, I want to calculate the sum total of "Amount" for each SubAccountID and CallType.

In this example, row 2 – call type is WVO for subaccount 18499. The amount is 0.0003. Currently, a variable for this subaccount and call type doesn’t exist, so it creates the variable and the current amount sum is 0.0003.
Row 3 – call type is WVO for subaccount 18499. the amount is 0.0027. I then take the current variable for this call type and amount and add 0.0027 to the current 0.0003. The variable is now 0.0030.
Row 4 – call type is INTERSTATE for subaccount 18499. The amount is 0.00211. Currently, a variable for this subaccount and call type doesn’t exist, so it creates the variable and the current amount sum is 0.00211.

Does anyone have some pointers on how to solve this? The file read-by-row function is very straightforward, but I’m struggling on the logic to dynamically create and sum these variables. The tricky part is that I won’t necessarily know the SubAccountIDs

I haven’t been able to wrap my head around how to structure the code to perform the required functions

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Thanks Rob! That's exactly what I was looking for. I couldn't wrap my head around using the nested arrays but your example got me exactly where I needed to be!


  2. 0

    You can iterate through the file and build up an array of sums – for example by call type and sub account:

    $fh = fopen('myfile.csv', 'rb');

// initialise our output array    
$sums = [];

// retrieve the CSV header
$header = fgetcsv($fh);

while ($row = fgetcsv($fh)) {

    // for convenience, use the header values to refer to each cell
    $indexedRow = array_combine($header, $row);

    // these are the values for the current row
    $callType = $indexedRow['CallType'];
    $subAccountID = $indexedRow['SubAccountID'];
    $amount = $indexedRow['Amount'];

    // ensure our $sums array has an entry for the sum
    // we want to add
    if (!array_key_exists($callType, $sums)) {
        $sums[$callType] = [$subAccountID => 0];
    } else {
        if (!array_key_exists($subAccountID, $sums[$callType])) {
            $sums[$callType][$subAccountID] = 0;
        }
    }

    // add the amount to the relevant running total
    $sums[$callType][$subAccountID] += $amount;
}

// tidy up
fclose($fh);

print_r($sums);

    Gives this output:

    Array
(
    [WVO] => Array
        (
            [18499] => 0.0039
            [19203] => 0.0051
        )

    [INTERSTATE] => Array
        (
            [18499] => 0.00211
            [19203] => 0.0009
        )
)
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search