Script - Calculations

[NickC111]

Good Morning

I would like some help with a procedure I would to do with a CSV File.

The CSV file has 4 Columns with rows of data - Example Below

Reference Cell Value Type
100007284 94 542.8 AN
100017475 94 393.6 RS
100046684 94 657.44 AN
100066577 94 601.96 AN
100086984 94 370.28 LH
100106571 94 754.25 AN
100116982 95 812.32 LH
100176885 95 616 AN
100176982 95 676.16 AN
100196778 96 902.48 AN
100217268 96 493.86 AN
100217268 94 205.38 LH
100267567 99 271.2 RS
100357566 99 647.7 AN
100367169 101 930.86 AN
100387284 101 807.68 MX
100396866 102 480.65 AN
100476868 103 587.84 AN
100497078 103 264.56 LH

I want the script to total up the values against the relevant cell number and create a new csv with the totals

Example

Cell 94 Total 3320.33
Cell 95 Total 2104.48

I hope you can help

 

[SkipVought]

hi,

So what code do you have so far and where are you stuck?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[jges]

Do the 'reference' and 'type' columns have any bearing on the output?

 

[Geates]

Not gonna lie, I read your post an see "homework assignment." The following is pseudo code. Once actual code is presented (as SkipVought requests), responders may be more inclined to help syntatically.

This is one of several ways to accomplish you goal...


Create a dictionary
Open CSV File

Do while not end of file.
read line
split into tokens
if the 2nd token (cell) doesn't exist in the dictionary then
.add 2nd token (cell) to dictionary and assign 3rd token (value) to it
else
add 3rd token (value) to the existing value held by the 2nd token (cell) in the dictionary
end if
loop

open new output file

do while not end of dictionary
output "Cell " & .key & " total " & .item
loop

http://raqxtr.sstar.com/caspdoc/html/vbscript_language_reference.htm

-Geates

"I hope I can feel and see the change - stop the bleed inside a feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer

 

[taupirho]

Another way to go is to treat the CSV file like a database table and use SQL.

C:\WINDOWS\system32>type tom.csv
Reference,Cell,Value,Code
100007284,94,542.8,AN
100017475,94,393.6,RS
100046684,94,657.44,AN
100066577,94,601.96,AN
100086984,94,370.28,LH
100106571,94,754.25,AN
100116982,95,812.32,LH
100176885,95,616,AN
100176982,95,676.16,AN
100196778,96,902.48,AN
100217268,96,493.86,AN
100217268,94,205.38,LH
100267567,99,271.2,RS
100357566,99,647.7,AN
100367169,101,930.86,AN
100387284,101,807.68,MX
100396866,102,480.65,AN
100476868,103,587.84,AN
100497078,103,264.56,LH

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\WINDOWS\system32>cd c:\scripts

C:\scripts>type tom7.vbs
On Error Resume Next

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H0001

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
Set objStdOut = WScript.StdOut

strPathtoTextFile = "C:\windows\system32\"


objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPathtoTextFile & ";" & _
"Extended Properties=""text;HDR=YES;FMT=Delimited"""

objRecordset.Open "SELECT SUM(value) ,
cell FROM tom.csv group by cell ", objConnection, adOpenStatic, adLockOptimistic, adCmdText

Do Until objRecordset.EOF

objStdout.writeline "SUMVAL: " & objRecordset.Fields.Item(0) & " " & objRecordset.Fields.Item(1)
objRecordset.MoveNext

Loop


C:\scripts>cscript tom7.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.


SUMVAL: 3525.71 94
SUMVAL: 2104.48 95
SUMVAL: 1396.34 96
SUMVAL: 918.9 99
SUMVAL: 1738.54 101
SUMVAL: 480.65 102
SUMVAL: 852.4 103

C:\scripts>


In order to understand recursion, you must first understand recursion.

 

[Geates]

I like taupirho's suggestion better.

-Geates

"I hope I can feel and see the change - stop the bleed inside a feel again. Cut the chain of lies you've been feeding my veins; I've got nothing to say to you!"
-Infected Mushroom

"I do not offer answers, only considerations."
- Geates's Disclaimer