VBS - Text File

[NickC111]

Good Afternoon

I have an delimiter text file using the '|' and I want to sum the numbers in the fifth column. Save the results to a new text file.

Example

0|21212131|TEST|19/12/2010|56.89|17/12/2010
0|21212131|TEST|12/12/2010|75.00|17/12/2010
0|21212131|TEST|11/01/2010|156.89|17/12/2010
0|21212131|TEST|12/04/2010|5.99|17/12/2010
0|21212131|TEST|17/10/2010|56.89|17/12/2010

Add up the fifth column - 56.89+75.00+156.89+5.99+56.89

Any ideas

 

[Geates]

There are 2 ways to do this. 1) Iterate through the file line by line, split the line and add the 5th element. or 2) open the file as a "database" and use ADO to add them the 5th elements.

1)

set objFSO = CreateObject("Scripting.FileSystemObject")
set objFile = objFSO.OpenTextFile("file.txt", 2, true, 0)

intSum = 0
do while not (objFile.AtEndOfFile)
   strLine = objFile.ReadLine
   arrTokens = split(strLine, "|")
   intSum = intSum + arrTokens(4) 
loop

msgbox "The 5th Element is: " & intSum

or

2) taupirho provided an elegant ADO solution. Probably not the recommended method for this situation but one that should be studied and used for more complicated situations.

http://www.tek-tips.com/viewthread.cfm?qid=1650457&page=1

-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

 

[SkipVought]

Save the results to a new text file.

So you want to save ONLY the value 351.66 to a new file?

Skip,

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

 

[NickC111]

Yes - I would like to save the value to a new file - Thanks

 

[Geates]

Forgot that part.

set objOutputFile = objFSO.OpenTextFile("output.txt", 2, true, 0)
objOutputFile.WriteLine intSum

Also, I "misspoke" in my code before. When opening a text file, there are 3 modes, namely, read (1), write (2), and append (8).

Open for Reading
set objFile = objFSO.OpenTextFile("file.txt", [red]1[/red], true, 0)

Open for Writing from the beginning of the file
set objFile = objFSO.OpenTextFile("file.txt", [red]2[/red], true, 0)

Open for Writing from the end of the file (append)
set objFile = objFSO.OpenTextFile("file.txt", [red]8[/red], true, 0)


-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

 

[NickC111]

I have a Object doesn't support this property or method: 'objFile.AtEndOfFile' error message

 

[Geates]

oops. Change 'AtEndOfFile' to 'AtEndOfStream' and make sure you are opening the file for reading.

-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

 

[NickC111]

Thanks - I think we are getting there but another subscript out of range'[number: 4]'

 

[Geates]

That suggests that split could not break up the strLine into tokens. There is probably a blank line at the end of your text file. Account for this by adding an if..then statement.

do while not (objFile.AtEndOfStream)
   strLine = objFile.ReadLine
   [green]if (strLine <> "") then
      arrTokens = split(strLine, "|")
      intSum = intSum + arrTokens(4)
   end if[/green]
loop

-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