Solved

sum up value from files header using .vbscript

Posted on 2016-10-12
5
41 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 500 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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
clicking a shape in a frame array vb6 3 55
vbModal 12 74
VB script to continue despite error 2 77
Macro problems with Excel file 6 52
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…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

732 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