Comparing XML file to Text File with OpenTextFile Working Slowly

[Will A]

I'm trying to compare an XML file to a Text File and Write the results to the file if they exist. This process is extremely slow and takes 15 minutes. The file I am comparing against in 100MB. The XML file is 1 MB. Please help!



Dim parentNode, objFolder, objFolder2, objPath, objShell, objFile, objXML, objNode, m_MfgPartNumber , m_CageCode

Set objShell = CreateObject("Shell.Application")
Set objFolder = objShell.BrowseForFolder(0, "Select EEL Data Extraction Folder", 1, "C:\Scripts\Temp")

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder2 = objFSO.GetFolder(objFolder.Self.Path)

Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
txt = "C:\Scripts\Results\Validation.txt"

Set oOut = objFSO.CreateTextFile(txt, True)

For each File In objFolder2.Files
objXML.load File.Path


Set oFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = oFSO.GetFile(File.Path)


Set m_MfgPartNumber = objXML.documentElement.selectNodes("//ReferenceNumber")
Set m_CageCode = objXML.documentElement.selectNodes("//CageCode")

If Not(m_MfgPartNumber is Nothing) Then

For each i in m_MfgPartNumber


Call Validation()

Next
End if


Next

MsgBox "Process Complete"
'-------------------------------------------------------------------------------------------------------------------------
Sub Validation

Set objFSO = CreateObject("Scripting.FileSystemObject")

sText = "C:\TexFiletoRead.txt"




Set objFile = objFSO.OpenTextFile(sText, 1, True)


Do While Not objFile.AtEndOfStream

sText = objFile.ReadLine

Table Name'
tField1 = Trim(Left(sText,4))

'Cage Code section
tCageCode = trim(mid(sText,5, 5))

'PartNumber location
tPartNumber = trim(mid(sText,10, 10))

'If part number value exists
If tField1 = "HA" then

If i.text = tPartNumber Then

oOut.WriteLine i.text & " " & tPartNumber


End if

End if

Loop

objFile.Close

End Sub

[Mayayana]

It's hard to know without seeing the text file,
but it would certainly help if you could use InStr.
You're reading thousands of lines (hundreds of
thousands?) and with each line you're creating
several new strings and doing several string
comparisons. If it's possible I think you'd
do better with something like:

Pt1 = Instr(1, sWholeFile, "HA", 0)
When you find HA then extract the next section.
InStr is extremely fast.
Also, it should help a little if you send the i.text value:

Validation i.Text

Sub Validation(sText)

That saves you from referring back to the XML
object twice for each run.

"Will A" <***@gmail.com> wrote in message news:20681874-a9e0-4401-9924-***@googlegroups.com...
| I'm trying to compare an XML file to a Text File and Write the results to
the file if they exist. This process is extremely slow and takes 15
minutes. The file I am comparing against in 100MB. The XML file is 1 MB.
Please help!
|
|
|
| Dim parentNode, objFolder, objFolder2, objPath, objShell, objFile, objXML,
objNode, m_MfgPartNumber , m_CageCode
|
| Set objShell = CreateObject("Shell.Application")
| Set objFolder = objShell.BrowseForFolder(0, "Select EEL Data
Extraction Folder", 1, "C:\Scripts\Temp")
|
| Set objFSO = CreateObject("Scripting.FileSystemObject")
| Set objFolder2 = objFSO.GetFolder(objFolder.Self.Path)
|
| Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
| txt = "C:\Scripts\Results\Validation.txt"
|
| Set oOut = objFSO.CreateTextFile(txt, True)
|
| For each File In objFolder2.Files
| objXML.load File.Path
|
|
| Set oFSO =
CreateObject("Scripting.FileSystemObject")
| Set objFile = oFSO.GetFile(File.Path)
|
|
| Set m_MfgPartNumber =
objXML.documentElement.selectNodes("//ReferenceNumber")
| Set m_CageCode =
objXML.documentElement.selectNodes("//CageCode")
|
| If Not(m_MfgPartNumber is Nothing) Then
|
| For each i in m_MfgPartNumber
|
|
| Call Validation()
|
| Next
| End if
|
|
| Next
|
| MsgBox "Process Complete"
|
'-------------------------------------------------------------------------------------------------------------------------
| Sub Validation
|
| Set objFSO = CreateObject("Scripting.FileSystemObject")
|
| sText = "C:\TexFiletoRead.txt"
|
|
|
|
| Set objFile = objFSO.OpenTextFile(sText, 1, True)
|
|
| Do While Not objFile.AtEndOfStream
|
| sText = objFile.ReadLine
|
| Table Name'
| tField1 = Trim(Left(sText,4))
|
| 'Cage Code section
| tCageCode = trim(mid(sText,5, 5))
|
| 'PartNumber location
| tPartNumber = trim(mid(sText,10, 10))
|
| 'If part number value exists
| If tField1 = "HA" then
|
| If i.text = tPartNumber Then
|
| oOut.WriteLine i.text & " " & tPartNumber
|
|
| End if
|
| End if
|
| Loop
|
| objFile.Close
|
| End Sub

[Will A]

Thanks but unfortunately I cannot use instr becaue there are words in other fields.

It's hard to know without seeing the text file, but it would certainly help if you could use InStr. You're reading thousands of lines (hundreds of thousands?) and with each line you're creating several new strings and doing several string comparisons. If it's possible I think you'd do better with something like: Pt1 = Instr(1, sWholeFile, "HA", 0) When you find HA then extract the next section. InStr is extremely fast. Also, it should help a little if you send the i.text value: Validation i.Text Sub Validation(sText) That saves you from referring back to the XML object twice for each run. | I'm trying to compare an XML file to a Text File and Write the results to the file if they exist. This process is extremely slow and takes 15 minutes. The file I am comparing against in 100MB. The XML file is 1 MB. Please help! | | | | Dim parentNode, objFolder, objFolder2, objPath, objShell, objFile, objXML, objNode, m_MfgPartNumber , m_CageCode | | Set objShell = CreateObject("Shell.Application") | Set objFolder = objShell.BrowseForFolder(0, "Select EEL Data Extraction Folder", 1, "C:\Scripts\Temp") | | Set objFSO = CreateObject("Scripting.FileSystemObject") | Set objFolder2 = objFSO.GetFolder(objFolder.Self.Path) | | Set objXML = CreateObject("MSXML2.DOMDocument.6.0") | txt = "C:\Scripts\Results\Validation.txt" | | Set oOut = objFSO.CreateTextFile(txt, True) | | For each File In objFolder2.Files | objXML.load File.Path | | | Set oFSO = CreateObject("Scripting.FileSystemObject") | Set objFile = oFSO.GetFile(File.Path) | | | Set m_MfgPartNumber = objXML.documentElement.selectNodes("//ReferenceNumber") | Set m_CageCode = objXML.documentElement.selectNodes("//CageCode") | | If Not(m_MfgPartNumber is Nothing) Then | | For each i in m_MfgPartNumber | | | Call Validation() | | Next | End if | | | Next | | MsgBox "Process Complete" | '------------------------------------------------------------------------------------------------------------------------- | Sub Validation | | Set objFSO = CreateObject("Scripting.FileSystemObject") | | sText = "C:\TexFiletoRead.txt" | | | | | Set objFile = objFSO.OpenTextFile(sText, 1, True) | | | Do While Not objFile.AtEndOfStream | | sText = objFile.ReadLine | | Table Name' | tField1 = Trim(Left(sText,4)) | | 'Cage Code section | tCageCode = trim(mid(sText,5, 5)) | | 'PartNumber location | tPartNumber = trim(mid(sText,10, 10)) | | 'If part number value exists | If tField1 = "HA" then | | If i.text = tPartNumber Then | | oOut.WriteLine i.text & " " & tPartNumber | | | End if | | End if | | Loop | | objFile.Close | | End Sub

[GS]

Not sure why you need to do this with VBS but I use TextPad because it
results any number of file comparisons almost instantaneously, and the
findings can be copied (or put into the clipboard)!

[Will A]

Post by GS
Not sure why you need to do this with VBS but I use TextPad because it
results any number of file comparisons almost instantaneously, and the
findings can be copied (or put into the clipboard)!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'd prefer not to pay for text pad

[GS]

Post by Will A

Post by GS
Not sure why you need to do this with VBS but I use TextPad because it
results any number of file comparisons almost instantaneously, and the
findings can be copied (or put into the clipboard)!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

I'd prefer not to pay for text pad

It's free, but you get the nags. A licensed version is fairly cheap for
all that it does, though!