Solved

sum up value from files header using .vbscript

Posted on 2016-10-12
5
17 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
  • 2
5 Comments
 
LVL 76

Expert Comment

by:GrahamSkan
Comment Utility
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 500 total points
Comment Utility
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now