Simplest way to total up columns?

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开发者_JAVA百科 of the CSV files.

I see at least a few options:

1. Manually total up all the account balances in Excel - yuck.

2. Write a PHP script to read each CSV file and total up the account balances - also yuck.

3. 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?

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

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.

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.

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.

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?

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.

See also: The CSV Storage Engine