• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 60
  • Last Modified:

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

0
Vikx One
Asked:
Vikx One
  • 2
1 Solution
 
GrahamSkanCommented:
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

0
 
GrahamSkanCommented:
Here is the VBScript version (no types in the Dim statements)
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 = "D:\Data\script\"
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

Open in new window

0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now