asked on

Show progress information while importimg from XML file

Experts,

I have an application that import data from xml file into SQL Server.
Process involve complex transformations so I cannot use bulk insert or XMLSQL
Instead I open file as MSXML2.DOMDocument40 and loop through all elements.

Now I need to display progress bar while importing.

As Max value I'll take Len(xmlDoc.xml)
Then I tried to compute already processed part as

mlBytesProcessed = mlBytesProcessed + Len(objNodeList.Item(i).xml)

But this approach works only on highest level of elements.
I want to get this info from each child element.

Any ideas?

Michael

dsulkar

find out how big the child record is compared to the master and use a percentage to move the bar while processing the child record. I am assuming you can read to the next master record and count how many bytes the child record is compared to the current master record.

Hope that helps a little

Diven

Michael_D

ASKER

I think I have to explain more:

<root> ( or whole doc is 100k)
<element1> (20K incuding all child elements)
<element11>(10K)
<element12>(10K)
<element2> (25K incuding all child elements)
<element21>(15K including childs )
<element211>(5K)
<element212>(10K)
<element22>(10K)
<element3> (35K incuding all child elements)
....
<element4> (20K incuding all child elements)
....

So when I use Len(some_current_element.xml) I get like this

<root> ( or whole doc is 100k) ---->0%
<element1> (20K incuding all child elements) ---->20%
<element11>(10K) ---->30% ' this part is already counted in parent element
<element12>(10K) ---->40%
<element2> (25K incuding all child elements) ---->65%
<element21>(15K including childs ) ---->80%
<element211>(5K) ---->85%
<element212>(10K) ---->95%
<element22>(10K) ---->105% :)
<element3> (35K incuding all child elements)
....
<element4> (20K incuding all child elements)
....

So I need to know size of xml portion of element Excluding children

Thanks

dsulkar

I am assuming that you are just looping through this xml file
couldent you just use the Len statement in the parent loop outside the child loop and then upgrade the progress percentage in the child loop as a percentage of just the parent. That way you still have the parent length as a percentage of the root file and the individual child lengths as a percentage of the parent loop.

Shiju S

hi
r u processing nodes one by one or as a collection of nodes?
if ur r processing node by node u can use this technique to trace the progress in processing

'-------------------------------------------------
' Place a Progressbar and change its name to pgrStatus
' and set Max Value to 100
' and use the following code
'-----------------------------------------------------------
Private Sub ProcessNodes()

Dim lTotal as Long
Dim objXmlDoc As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr as Double
' Place here the code to open ur Xml document
' <-------------->

Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//root")
lTotal = objXmlNdList.Length
Set objXmlNdList = Nothing
dblPgrBarIncr = 100 / lTotal
pgrStatus.Value=pgrStatus.Min

' Place here the code to Process a Single Xml Node
' <-------------->

If pgrStatus.Value + dblPgrBarIncr <= pgrStatus.Max Then
pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
Else
pgrStatus.Value = pgrStatus.Max
End If

End Sub

;-)
Shiju

ASKER CERTIFIED SOLUTION

Shiju S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Michael_D

ASKER

shijusn,

I got the idea, but again it works only for one top level
using your code here is how my app is working:

Private Sub ProcessNodes()

Dim lTotal As Long
Dim objXmlDoc As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr As Double
' Place here the code to open ur Xml document
' <-------------->
objXmlDoc.Load "C:\test.xml"
Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//root/*")
lTotal = objXmlNdList.length
'Set objXmlNdList = Nothing
dblPgrBarIncr = 100 / lTotal
pgrStatus.Value = pgrStatus.Min

' Place here the code to Process a Single Xml Node
' <-------------->
Dim i As Long
For i = 0 To objXmlNdList.length - 1

ProcessChild objXmlNdList(i).baseName, objXmlNdList(i).xml '<------- Call the recursive function

If pgrStatus.Value + dblPgrBarIncr <= pgrStatus.Max Then
pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
Else
pgrStatus.Value = pgrStatus.Max
End If
Next i
End Sub

---------------------
ProcessChild is recursive function very similar to ProcessNodes .

Any idea?

Michael_D

ASKER

Shiju,

Actually you was right. My fault.

Now its work with recursive function.

Thank you

Michael_D

ASKER

Here the whole solution:

'-------------------------------------------------
' Place a Progressbar and change its name to pgrStatus
' Set MAXVALUE constant to whatever you want - progress will run based on percentage of this value
' Place a CommandButton
' Press it for start process
'Dont forget to load the right file (I used "C:\test.xml")
'-----------------------------------------------------------

Option Explicit
Dim lParentValue As Double
Const MAXVALUE = 100

Private Sub ProcessNodes()

'Dim lTotal As Long
Dim objXmlDoc As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList

objXmlDoc.Load "C:\test.xml"
pgrStatus.Max = MAXVALUE
lParentValue = MAXVALUE
ProcessChild objXmlDoc.documentElement.baseName, objXmlDoc.xml

End Sub

Private Sub Command1_Click()
ProcessNodes
End Sub

Sub ProcessChild(ParentPath As String, xmlSource As String)

Dim lTotal As Long
Dim objXmlDoc As New MSXML2.DOMDocument40
Dim objXmlNdList As IXMLDOMNodeList
Dim dblPgrBarIncr As Double
Dim i As Long
Dim PrevValue As Double

objXmlDoc.loadXML xmlSource
Set objXmlNdList = objXmlDoc.documentElement.selectNodes("//" & ParentPath & "/*")
lTotal = objXmlNdList.length

If objXmlNdList.length = 0 Then
pgrStatus.Value = pgrStatus.Value + dblPgrBarIncr
Exit Sub
End If
dblPgrBarIncr = lParentValue / lTotal

For i = 0 To objXmlNdList.length - 1
PrevValue = pgrStatus.Value
lParentValue = dblPgrBarIncr

Debug.Print "Child -->" & objXmlNdList(i).baseName & " - " & lParentValue

ProcessChild objXmlNdList(i).baseName, objXmlNdList(i).xml

If PrevValue + dblPgrBarIncr <= pgrStatus.Max Then
pgrStatus.Value = PrevValue + dblPgrBarIncr
Else
pgrStatus.Value = pgrStatus.Max
End If
Debug.Print "Progress -->" & pgrStatus.Value

Next i

End Sub

Shiju S

Hi
Michael_D

i am glad that my comment helped u
if ur XML is huge u can put a DoEvents statement just before Next i
'---------------------
DoEvents
Next i
'---------------------

Thank u for the points
regards

;-)
Shiju