• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 169
  • Last Modified:

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
0
Michael_D
Asked:
Michael_D
  • 4
  • 3
  • 2
1 Solution
 
dsulkarCommented:
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
0
 
Michael_DAuthor Commented:
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
0
 
dsulkarCommented:
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.



0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Shiju SasidharanCommented:
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








0
 
Shiju SasidharanCommented:
hi
place the followng code  in the loop for processing nodes
    'Loop Starts here...        
    '   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

    'Endof ur proecssing Loop

i hope u got the idea

;-)
Shiju
0
 
Michael_DAuthor Commented:
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?

0
 
Michael_DAuthor Commented:
Shiju,

Actually you was right. My fault.

Now its work with recursive function.

Thank you
0
 
Michael_DAuthor Commented:
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

0
 
Shiju SasidharanCommented:
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
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now