1

Need help I have 2 csv files

january.csv with 3 columns:

02Jan2020   Marilyn 31570.29    
02Jan2020   Nancy   30000.00
06Jan2020   John    1570.29 
06Jan2020   Nancy   5000.00
10Jan2020   Marilyn 570.29  
10Jan2020   Nancy   10000.00 
.... etc

suppliers.csv

Marilyn
John
Nancy
..etc

Now I want to sum all values in amount (january.csv) that match from suppliers.csv 

Nancy 30000.00
Nancy 5000.00
Nancy 10000.00
echo sum   45000.00

I got to start of with this after researching but not sure if the compare is right and how to sum return values

$filename="january.csv";
$base="suppliers.csv"; 
$NOWcodes = array();

$file = fopen($base, 'r'); //registred opened 
while (($line = fgetcsv($file)) !== FALSE) { array_push($NOWcodes, $line[0]);  }
fclose($file);

$file = fopen($filename, 'r'); //all nomes 
while (($line = fgetcsv($file)) !== FALSE) {

if(!in_array($line[0],$NOWcodes)){ } //Not sure how to do it here

echo array_sum($sum)."\n";

fclose($file);
Vasyl Zhuryk
  • 1,228
  • 10
  • 23
Johan Venables
  • 57
  • 6

1 Answers1

0

Data in CSV files are separated by exactly one separator. The data displayed must be in this format so that it can be read correctly. I used a single space for january.csv.

The suppliers are stored in an array.

$filename = "january.csv";
$base = "suppliers.csv"; 

$suppliers = [];
$file = fopen($base, 'r');
while (($line = fgetcsv($file)) !== FALSE) {
  $suppliers[] = $line[0];
}
fclose($file);

Checking the suppliers and grouping / summing can then be carried out in a further for loop.

$file = fopen($filename, 'r');
$sum = [];
while (($row = fgetcsv($file,0," ")) !== FALSE) {
  $name = $row[1];
  if(in_array($name,$suppliers)){
    if(!array_key_exists($name,$sum)) $sum[$name] = 0.0;
    $sum[$name] += $row[2];
  }
}
fclose($file);

//Output Result
var_export($sum);

The result:

array (
  'Marilyn' => 32140.58,
  'Nancy' => 45000.0,
  'John' => 1570.29,
)

array_sum() can be used for the total of all suppliers:

$total = array_sum($groups);
jspit
  • 7,276
  • 1
  • 9
  • 17
  • My output is as follow with lots of decimal places 0 array ( 'S- Marilyn Salary' => -31570.2900000000008731149137020111083984375, 'S- Nancy Skosana' => -30000.0, 'S- Johan Consulting Fees' => -30000.0, 'S- Carol Consulting Fees' => -30000.0, 'S- Juanic Salary' => -24239.22000000000116415321826934814453125, 'S- Shirley Salary' => -14382.670000000000072759576141834259033203125, ) I don't need it group I want one total of all suppliers eg.Juanic+Shirley+Carol+Johan+Marilyn+Nancy= echo Total – Johan Venables Mar 26 '20 at 04:45
  • Everything you post here is not reproducible for us. The large number of decimal places is a problem with the accuracy of float numbers: https://stackoverflow.com/questions/21895756/why-are-floating-point-numbers-inaccurate – jspit Mar 26 '20 at 13:41
  • What I don't understand that the numbers in january.csv is a fixed number with 2 decimal but it looks like the sum function create this weird -31570.2900000000008731149137020111083984375 how can I fix this? – Johan Venables Mar 26 '20 at 18:53
  • You can format the results with sprintf () / printf () [https://www.php.net/manual/de/function.sprintf.php] to two decimal places for the output. – jspit Mar 27 '20 at 16:37