Simplest way to total up columns?

Question

I have a PHP script that imports up to 10 or so different CSV files. Each row of each file contains bank account info, including balance. After I've imported all the CSV data into my database, I'd like to make sure the data got in there correctly by comparing the total account balance in the database to the total account balance of the CSV files.

I see at least a few options:

Manually total up all the account balances in Excel - yuck.
Write a PHP script to read each CSV file and total up the account balances - also yuck.
Some third option that I hope exists. It would be amazing if I could do something like:

excel --file="cd.csv" | sum --column="E"

That's obviously not a real thing but hopefully you get the idea. Using some combination of PHP, MySQL, Linux commands, Excel and/or any other tools, is there a simple way to do this?

u can change php to accept $argv (cli mode) or a simple bash `total=0; col=9;for i in `cut -d ' ' cd.csv -f $col`; do let total=$i+$total; done; echo $total`; — ajreal, Nov 09 '10 at 20:07

score 0 · Answer 1 · answered Nov 09 '10 at 20:02

You can iterate over the CSV file using fgetcsv() which converts each line to an array of values. You can accumulate the value of the array element containing the balance as you move thru each iteration until you get the sum total. Use glob to get the list of CSV files in a folder.

score 0 · Answer 2 · answered Nov 09 '10 at 20:02

0

You may not have to "manually" total up the account balances, if you can use Excel functions from your application, the Excel formula in VBA would be:

Application.Sum(Range("A:A"))

where A:A is for column A.

answered Nov 09 '10 at 20:02

Lance Roberts

22,383
32
112
130

Could use COM from PHP if running on windows, but he is running on Linux it seems – Hannes de Jager Nov 09 '10 at 20:11
I am running Linux. However, that's not to say it wouldn't help to automate some of the process in Windows and then bring the results into Linux. – Jason Swett Nov 09 '10 at 20:44

score 0 · Answer 3 · answered Nov 09 '10 at 20:04

0

Try using CSVFix with it's summary option. It will get you more data than you need, but should be easy to use.

Otherwise, this sounds like a good use for Awk.

answered Nov 09 '10 at 20:04

Alan Geleynse

24,821
5
46
55

score 0 · Answer 4 · answered Nov 09 '10 at 20:05

0

Why can't you just automagically total up the account balances in excel with a formula and export them with the rest of the data?

answered Nov 09 '10 at 20:05

conartist6

417
4
15

I could do that. It wouldn't be ideal (I prefer total automation) but it may turn out to be the simplEST solution. – Jason Swett Nov 09 '10 at 20:45

score 0 · Accepted Answer · edited May 23 '17 at 11:54

0

Don't have to complete answer for you, but AWK should be able to solve your problem: Have a look at these 2 posts:

https://superuser.com/questions/53652/transforming-csv-file-using-sed

Shell command to sum integers, one per line?

I'm not enough of a AWK expert to give the solution, but perhaps someone else can help us here.

Another option (which you may also consider yuck) is to use a library like PHPExcel

edited May 23 '17 at 11:54

Community

1
1

answered Nov 09 '10 at 20:10

Hannes de Jager

2,903
6
37
57

It looks like I'll go with `awk`. – Jason Swett Nov 11 '10 at 20:07
Update: this seems to have worked: `cat file.csv | awk -F "\"*,\"*" '{s+=$2} END {print s}'` (That totals up the second column.) – Jason Swett Nov 11 '10 at 20:24

score 0 · Answer 6 · answered Nov 09 '10 at 20:18

0

A bit of a different angle: Make use of the MySql CSV engine to expose your CSV files to Mysql and then do a normal SQL SUM.

Simplest way to total up columns?

6 Answers6