extract and compare

[swingy swing]

Hi !!
Need to extract and compare data from two text files. Data in these files is separated with pipes. I only need to extract relevant data and while comparing; I need to take out the difference between two data points. The problem that I am facing is that the pipes are not separated uniformly. And I am finding it difficult to extract the relevant data.

I need to do the following steps:

1. Extract relevant data from both text files.
2. Transfer the data to another output file in text format (since the record exceed 100K).
3. Take out difference of values between two data points
4. Prepare a summary.

Data in File 1.

14|FN|AAA|4|3.14|2||1||+1|1800|S|A|1-1974||NA|NA|BSSM|MANAGER

10|VIN|PPP|4|3.0|46||||162|22|AD|D|1-1970||NA|NA|AEBS|BRAN



Need “14” ,“AAA” ,“4”,”3.14”, “BSSM” data points from line 1 in file 1.

Need “10”,“PPP”, “4”, “3.0” , “AEBS” data points from line 2 in file 1.

Data in File 2
Would have similar records as file 1.

Extract : BSSM and AEBS data points are employee names , 3.14 and 3.0 are amounts. 14 and 10 are unique identifiers.

BSSM and AEBS is hypothec data and can vary, but the character length is fixed.
Unique identifier is hypothec data and can vary, by the length is fixed.

I need the difference of amounts in file 1 and 2 for respective employees against their unique identifiers in another text file.

This I want to do a subtotal in excel. The text files have more that 100,000 records in each.

Pls assist.

Thanks

 

[Joel]

Try this code. I extracted the BSSM and AEBS lines and put the results into
a CSV file.

Sub Extractdata()

Dim OutputArray(5) As Variant

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

fileSaveName = Application.GetSaveAsFilename( _
fileFilter:="Text Files (*.csv), *.csv", _
Title:="Open Output file")
If fileSaveName = False Then
MsgBox ("Cannot open File - Exiting Macro")
Exit Sub
End If

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CreateTextFile fileSaveName

Set fwrite = fs.GetFile(fileSaveName)
Set tswrite = fwrite.OpenAsTextStream(ForWriting, TristateUseDefault)


fileToOpen = Application _
.GetOpenFilename( _
fileFilter:="Text Files (*.txt), *.txt", _
Title:="Open Input file")
If fileToOpen = False Then
MsgBox ("Cannot open File - Exiting Macro")
Exit Sub
End If

Set fread = fs.GetFile(fileToOpen)
Set tsread = fread.OpenAsTextStream(ForReading, TristateUseDefault)

Do While tsread.atendofstream = False

InputLine = Trim(tsread.ReadLine)
If Len(InputLine) > 0 Then
InputArray = Split(InputLine, "|")

Select Case InputArray(17)

Case "BSSM", "AEBS":
OutputArray(0) = Trim(InputArray(0))
OutputArray(1) = Trim(InputArray(2))
OutputArray(2) = Trim(InputArray(3))
OutputArray(3) = Trim(InputArray(4))
OutputArray(4) = Trim(InputArray(17))

OutPutLine = Join(OutputArray, ",")
If Right(OutPutLine, 1) = "," Then
OutPutLine = Left(OutPutLine, Len(OutPutLine) - 1)
End If
tswrite.writeline OutPutLine
End Select
End If
Loop


tswrite.Close
tsread.Close

End Sub