?
Solved

sum up value from files header using .vbscript

Posted on 2016-10-12
5
Medium Priority
?
50 Views
Last Modified: 2016-12-01
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
Comment
Question by:Vikx One
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
5 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
ID: 41842139
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
 
LVL 76

Accepted Solution

by:
GrahamSkan earned 2000 total points
ID: 41842173
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses
Course of the Month12 days, 12 hours left to enroll

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question