Write results from 2 files

[dreamscapeuk]

Hi,

I'm really new to vbscript need some help in writing the results from a validation of 2 txt files.

Both files are in the same format

5DigitAccountNo Space Value

e.g

40000 -190000.00
40100 2000.00
40200 50000.00
etc

I can do the basics like read each line in file one and match it with all the lines in file 2. If it exists move onto line 2 otherwise write in the resultsfile.txt. The problem with this is.. what if there is a line in file 2 that isn't in file one? that will not get reported unless i do another check the other way round. I'd like to tidy this process up.

What I want to do is compare both files and write any values that are different or the account doesn't exist in the each file.

So the results file is only produced if there are any differences and can look like this:

Account File 1 File 2
40000 -190000.00 In this case file 2 doesn't have an account 40000
40100 2000.00 In this case file 1 doesn't have an account 40100
40200 50000.00 42000.00 In this case values in both files are different.

I'm sure this has to be possible. I'm no vb programmer and taking examples around I have attempted to do this for hours but to no avail. If someone could help with a sample code I would really appreciate it.

 

[dreamscapeuk]

Here's the original file I have which only lists the differences I have in file 1:

Const ForReading = 1, ForWriting = 2
Dim fso, OracleFile, HyperionFile, strLine1, strLine2, strMatch
Set fso = CreateObject("Scripting.FileSystemObject")
Set OracleFile = fspenTextFile("OracleFile.txt", ForReading)
Set f = fspenTextFile("ResultsFile.txt", ForWriting, True)

Do Until OracleFile.AtEndOfStream
strMatch = False
strLine1 = OracleFile.Readline
Set HyperionFile = fspenTextFile("HyperionFile.txt", ForReading)
Do Until HyperionFile.AtEndOfStream
strLine2 = HyperionFile.Readline
If Trim(UCase(strLine2)) = Trim(UCase(strLine1)) Then
strMatch = True
Else
End If
Loop
HyperionFile.Close
If strMatch <> True then
f.writeline strLine1
End If
Loop
f.Close
Set f = nothing
Set OracleFile = nothing
Set HyperionFile = Nothing
Set ResultsFile = nothing

Wscript.Echo "Done

 

[dreamscapeuk]

I really need this quickly and will post out 3 Xbox 360 games to the first person who can give me the solution I need, as a big thank you!

 

[jges]

If the account numbers are all unique (only found 1 time in each file) you could try this: read each file into a dictionary object (the account number would be the key value); compare line by line as you are now, but when a match is found - remove the line from both dictionary objects. The dictionary objects will only contain the unique lines from each file. This way you only have to loop through the check once.

 

[strongm]

Yep, that's the approach I have taken in the past for 'simple' reconciliations

 

[dreamscapeuk]

Any help with this as it doesn't seem to work..

Const ForReading = 1, ForWriting = 2

Set objDictionary = CreateObject("Scripting.Dictionary")
' Declare the dictionaries.
Dim Dict1
Dim Dict2

'Set txt files
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objTextFile = objFSO.OpenTextFile("C:\VBScript\ReadFiles\OracleFile.txt", ForReading)
Set objTextFile2 = objFSO.OpenTextFile("C:\VBScript\ReadFiles\HyperionFile.txt", ForReading)
Set f = objFSO.OpenTextFile("C:\VBScript\ReadFiles\ResultsFile.txt", ForWriting, True)

Set Dict1 = objDictionary
With Dict1
'set compare mode
.CompareMode = BinaryCompare
i = 0
Do Until objTextFile.AtEndOfStream
strNextLine = objTextFile.Readline
.Add i, strNextLine
i = i + 1
Loop
End With

Set Dict2 = objDictionary
With Dict2
'set compare mode
.CompareMode = BinaryCompare
x = 0
Do Until objTextFile2.AtEndOfStream
strNextLine2 = objTextFile2.Readline
.Add x, strNextLine2
x = x + 1
Loop
End With

' Compare the two dictionaries.
For Each objItem In Dict1
If Dict2.Exists(objItem) Then
Dict2.Remove(objItem)
Dict1.Remove(objItem)
Else
f.writeline Dict1(objItem) & vbCrLf
f.writeline Dict2(objItem) &vbCrLf
End if
Next

f.Close
WScript.Echo "Done

 

[jges]

Try adding a few wscript.echo commands to see what you are currently comparing. You may be surprised.

If you want to compare account numbers, you will need to use the account number as the key value. Assigning 1, 2, 3, 4... as the key values and comparing them to the other dictionary will not get what you want.

 

[tsuji]

[0] The script is conceptually fundamentally flawed. There is only one objDictionary; there are only three refereneces pointing to the same dictionary object. Hence, add items etc are adding to the same (a:0,1,2,...; x:0,1,2,... and it is surprising that you did not mention the .add should already error out.) There is no comparison of 2 dictionaries at all. It did not happen.

[0.1] The account number is the key/index. You have to make good use of it. It is not the line number 0,1,2,...

[1] This is how you can do to produce a consolidation csv. This is a complete rewrite of the operation on dictioary object onward.
[tt]
'... etc...
[red]'[/red]Set Dict1 = objDictionary
dim a
With objDictionary
.CompareMode = 0 'or you define BinaryCompare=0
Do Until objTextFile.AtEndOfStream
strNextLine = trim(objTextFile.Readline)
if strNextLine<>"" then
a=split(strNextLine," ") 'rigorously spec: 1 space separator and no empty 2nd column
if ubound(a)>=1 then
if not .exists(a(0)) then
.add a(0),a(1) & ", n/a"
end if
end if
Loop
End With
objTextFile.close
set objTextFile=nothing

[red]'[/red]Set Dict2 = objDictionary
With objDictionary
Do Until objTextFile2.AtEndOfStream
strNextLine2 = trim(objTextFile2.Readline)
a=split(strNextLine2," ") 'rigorously spec: 1 space separator and no empty 2nd column
if ubound(a)>=1 then
if not .exists(a(0)) then
.add a(0),", n/a, " & a(1)
else
.item(a(0))=replace(.item(a(0)),"n/a",a(1))
end if
end if
Loop
End With
objTextFile2.close
set objTextFile2=nothing

for each k in objDictionary
f.writeline k & ", " & odic(k)
next
f.close
set f=nothing

set objDictionary=nothing

WScript.Echo "Done" [/tt]

 

[tsuji]

amendment

The following line
> .add a(0),", n/a, " & a(1)
should be read
[tt] .add a(0),[highlight]"n[/highlight]/a, " & a(1)[/tt]

 

[tsuji]

amendment2
This line is another obvious typo:
> f.writeline k & ", " & odic(k)
too used to my own style. It should be read like this.
[tt] f.writeline k & ", " & objDictionary(k)[/tt]