asked on

sum up value from files header using .vbscript

I have two files (with same format) with header. in the file header, it contains the filedate, filename,total number of row & total amount of data. (header format sample: 20160912|filename| 312 |4019)
so i need to create a script that will merge these two files & will create new file. my problem is at the header part, i cant seem to get how to add/sum the value of number of row & total amount of data from these two files:
let says:
File 1 :
20160912|filename| 312 |4019
File 2:
20160912|filename| 100 |21)
Output File:
20160912|filename| 412 |4040)

Please help. So far, i have this build up:

Const ForReading   = 1
Const separator    = "|"
Const columnAmount = 3

FixedHeaderPart  = "20160204" & separator & "FILENAME" & separator

strPath          = "D:\Data\script\"
strNameFile1     = "File1.txt"
strNameFile2     = "File2.txt"
strNameOutFile   = "MyFile.txt"

Set objFSO       = CreateObject("Scripting.FileSystemObject")

Set objTextFile  = objFSO.OpenTextFile(strPath & strNameFile1, ForReading)

strTemp = ""
intRowCounter = 0
intSum = 0

' throw away the first line, as it can only created at the end of this merger operation
objTextFile.ReadLine

Do Until objTextFile.AtEndOfStream
    strLine = objTextFile.ReadLine
    IntRowCounter = intRowCounter + 1
		
	' split up the line and add the amount in column 7 to the sum
	intAmount=split(strLine,separator)(columnAmount)
	intSum = intSum + intAmount
	
	strTemp = strTemp & strLine & vbCrLf
Loop

objTextFile.Close

Set objTextFile = objFSO.OpenTextFile(strPath & strNameFile2, ForReading)

' throw away the first line, as it can only created at the end of this merger operation
objTextFile.ReadLine

Do Until objTextFile.AtEndOfStream
    strLine = objTextFile.ReadLine
    intRowCounter = intRowCounter + 1
		
    ' split up the line and add the amount in column 7 to the sum
	intAmount=split(strLine,separator)(columnAmount)
	intSum = intSum + intAmount
	
	strTemp = strTemp & strLine & vbCrLf
Loop

objTextFile.Close

Set objOutputFile = objFSO.CreateTextFile(strNameOutFile)

objOutputFile.WriteLine FixedHeaderPart & intRowCounter & separator & intSum
objOutputFile.Write strTemp

objOutputFile.Close

Open in new window

GrahamSkan

This works in VBA. I will convert it back to VBScript shortly:

Sub TheScript()

Const ForReading = 1
Const separator = "|"
Const columnAmount = 3

Dim FixedHeaderpart As String
Dim strPath As String
Dim strNameFile1 As String
Dim strNameFile2 As String
Dim strNameOutFile As String

Dim strTemp As String
Dim intRowCount As Integer
Dim intAmount As Integer

Dim objFSO As Scripting.FileSystemObject
Dim objTextFile As Scripting.TextStream
Dim objOutputFile As Scripting.TextStream
Dim strLine As String
Dim strLineParts() As String


FixedHeaderpart = "20160204" & separator & "FILENAME" & separator

strPath = "I:\Allwork\ee\28976108\"
strNameFile1 = "File1.txt"
strNameFile2 = "File2.txt"
strNameOutFile = "MyFile.txt"

Set objFSO = CreateObject("Scripting.FileSystemObject")


strTemp = ""
'Read file 1 header ***********
Set objTextFile = objFSO.OpenTextFile(strPath & strNameFile1, ForReading)
    ' Read the first line
    strLine = objTextFile.ReadLine
    strLineParts = Split(strLine, separator)
    intAmount = strLineParts(columnAmount)
    intRowCount = strLineParts(columnAmount - 1)

objTextFile.Close

'Read file 2 header ***********
Set objTextFile = objFSO.OpenTextFile(strPath & strNameFile2, ForReading)
    ' Read the first line
    strLine = objTextFile.ReadLine
    strLineParts = Split(strLine, separator)
    strLineParts(columnAmount) = intAmount + strLineParts(columnAmount)
    strLineParts(columnAmount - 1) = intRowCount + strLineParts(columnAmount - 1)
objTextFile.Close

' Create Output file ***************
Set objOutputFile = objFSO.CreateTextFile(strPath & "\" & strNameOutFile)
    'write first line
    objOutputFile.WriteLine Join(strLineParts, separator)
    
    Set objTextFile = objFSO.OpenTextFile(strPath & strNameFile1, ForReading)
        ' throw away the first line, as it can only created at the end of this merger operation
        objTextFile.ReadLine
        
        'input the rest of file 1
        Do Until objTextFile.AtEndOfStream
            objOutputFile.WriteLine objTextFile.ReadLine
        Loop
    objTextFile.Close
    
    Set objTextFile = objFSO.OpenTextFile(strPath & strNameFile2, ForReading)
        ' throw away the first line, as it can only created at the end of this merger operation
        objTextFile.ReadLine
        
        'input the rest of file 2
        Do Until objTextFile.AtEndOfStream
            objOutputFile.WriteLine objTextFile.ReadLine
        Loop
    objTextFile.Close

objOutputFile.Close

End Sub

Open in new window

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial