calculating values from a text file

BlackWisdom · Post by **BlackWisdom** » Tue Jul 12, 2005 6:08 pm

I have searched several categories of forums and cant find this entry - but I know someone had to ask this before...

How do you calculate the sum of several numeric strings in a text file

example lets say the text file has the following numbers on each line

3
3
3
1

keep in mind there are no non-numeric values in the file - just numbers
How do you add these together and write the total (which would be 10) to a new text file.

I know your going to be using the let> / Counter> / WLN> and /Add> commands but I cant piece it together in my head - "even though my head is pretty big"..???

JRL · Post by **JRL** » Tue Jul 12, 2005 8:02 pm

Try something like this:

Readfile>c:\test.txt,file
separate>file,%CRLF%,num
Let>k=1

repeat>k
Let>k=k+1
add>num_1,num_%k%
until>k,num_count

MDL>Total=%num_1%

This assumes each number is on a line of its own, I didn't test to see what would happen if there are blank lines though I think it will pass over them uneventfully.

good luck,
Dick

BlackWisdom · Post by **BlackWisdom** » Wed Jul 13, 2005 12:01 am

Hi Dick,

when I run it I get this error - but its intermittant meanng sometimes I get the error and other times I dont:

---------------------------
Macro Scheduler
---------------------------
Invalid Numeric or Date Format In "Add>num_1,num_%k%"
---------------------------
OK
---------------------------

Bob Hansen · Post by **Bob Hansen** » Wed Jul 13, 2005 4:51 am

See http://www.mjtnet.com/forum/viewtopic.php?p=8412#8412 for reply to question about this error message.

JRL · Post by **JRL** » Wed Jul 13, 2005 4:36 pm

B.W.
This is also in response to the thread found here:
http://www.mjtnet.com/forum/viewtopic.php?p=8412#8412

I can duplicate your error. Running the script exactly as posted within MS and as an EXE I can cause the error but it seems to be consistent.

this is what I found.

If C:\test.txt contains one number on each line, the script runs fine.

If C:\test.txt contains lines that are blank, meaning they are completely empty, the script runs fine.

If C:\test.txt contains any non numeric character including spaces at the end of numbers or on otherwise blank lines, the script fails with the Macro Scheduler message:

Invalid Numeric or Date Format In "Add>num_1,num_%k%"

If your results are inconsistent, is it possible that the text file(s) are the inconsistency?

Let us know,
Dick

Bob Hansen · Post by **Bob Hansen** » Wed Jul 13, 2005 8:47 pm

You cannot use a string with the Add> command. Can only use numbers.

So when using Separate> any characters between return codes which are not true numbers will fail. Any spaces included will fail.

BlackWisdom · Post by **BlackWisdom** » Wed Jul 13, 2005 9:27 pm

Hey guys I tried all the suggestion and still could not get it to work so I used VBScript

VBStart
Dim FSO
Dim f
Dim num
Const ForReading = 1
Const ForWriting = 2

Set FSO = CreateObject("Scripting.FileSystemObject")
On Error Resume Next
Set f = FSO.OpenTextFile("FolderTotal.txt", ForReading, False)
Do While f.AtEndOfStream = False
num = num + CInt(f.ReadLine)
Loop
FSO.CreateTextFile "sum.txt", True, False
Set f = FSO.OpenTextFile("Sum.txt", ForWriting, True)
f.WriteLine (num)

Set FSO = Nothing
Set f = Nothing
VBEnd