Totaling figures in .csv files using Excel

Question

I have 12 .csv files produced by another program. The .csv files contain numeric data, separated by commas.

I need an easy way of totaling the values in certain columns in each of the files and comparing the totals across the various files e.g. compare the total from file 1 to the total from file 5.

The format of each file is the same i.e. 5 values in each record, separated by commas. Each of the 12 .csv files is about 50 Mb in size. Each file has a different number of records.

The environment I work in is 'secure' and I cant run any programs other than what I have installed on the PC I use. I have Excel installed and assume I can write VBA code/macros and I have access to the Command line. I can't (for example) load anything from a USB key and can not install any scripting language e.g. Python.

I have thought of doing this manually e.g. open each .csv file in Excel and total the columns using Excel functions i.e. SUM()

My challenge I need to do this many times of the next few weeks as new versions of the .csv files are produced i.e. I now have the first version, there will be many versions of the 12 files produced as I conduct testing on the other system. For each new version I need to sum the data and compare across files.

Last thing to say is, I cant change the system that produces the .csv files e.g. to create a set of totals

I'm looking for a programming solution that I can use, given my limited resources (ability to use any tools other than what is already on the PC)

Answer 1

Powershell or a batch script? A CSV is nothing more than a text file split with commas. Should be fairly easy to knock something up.

Answer 2

You should be able to do this easily using an excel VBA macro but it might take quite some time if it needs to load and convert a 50MB csv file.

JScript (a microsoft form of JavaScript) is generally available on all machines and runs under the windows scripting host. Just create a file with a .js extension and try to run with a double click. Or you can use vbscript with a .vbs extension.

I think your easiest solution would be to write an excel macro (as you will have the IDE for excel vba as limited as it is).

Answer 3

ADO can work on CSV files and you could then use SQL statements to sum the appropriate values - see this MSDN article for full details.

If you go to the Visual Basic Editor in Excel then try to add a reference via the Tools menu you should have several for Microsoft ActiveX Data Objects (2.8 being the most recent one.) Adding that reference lets you use ADO.