none
excel to nested xml RRS feed

  • Question

  • Hi,

    I am trying to create nested xml from an excel sheet which I get from API.

    Column A is Parent and Column B is Child in the excel sheet. A child will have many sub childs. So I am checking the condition and trying to create a nested xml but couldn't succeed. Any help is highly appreciated.

    Sub xlsToxml()
    
    Dim fso, oExcel, wb, ws
    ' create file system object
    set fso = WScript.CreateObject("Scripting.Filesystemobject")
    ' create Excel application object
    set oExcel = WScript.CreateObject("Excel.Application")
    oExcel.Visible = True
    
    ' create xml file path
    set XmlFile=fso.CreateTextFile("C:\Backup-44069411\C folder\Englobe\gsh.xml", True, True)
    
    ' creating xml file components
    'XmlFile.Write("<?xml version=""1.0"" encoding=""utf-8""?>"&vbNewLine)
    XmlFile.write("<Country>"&vbNewLine)
    
    'reading contents from excel file
    set wb = oExcel.Workbooks.Open("C:\Backup-44069411\C folder\Englobe\GSH\sample_gsh.xlsx")
    set ws = wb.Sheets(1)
    
    'getting the count of rows in the excel
    Row_count = ws.UsedRange.Rows.Count
    MsgBox Row_count
    
    DirectHolder = ws.cells(2,1).value ' Read the 1st column each row
    Subsidiary = refinedVal(ws.cells(2,2).value)' Read the 2nd column each row
    GroupAbbreviation = refinedVal(ws.cells(2,3).value)' Read the 2nd column each row
    VotingPercentage = refinedVal(ws.cells(2,4).value)' Read the 2nd column each row
    GroupInterestPercentage = refinedVal(ws.cells(2,5).value)' Read the 2nd column each row
    Jurisdiction = refinedVal(ws.cells(2,6).value)' Read the 2nd column each row
    RegisteredAddress = refinedVal(ws.cells(2,7).value)' Read the 2nd column each row
    Country = refinedVal(ws.cells(2,8).value)' Read the 2nd column each row
    XmlFile.write("<Parent>"&Parent&"</Parent>"&vbNewLine)
    XmlFile.write("<Child>"&Child&"</Child>"&vbNewLine)
    
    For i=3 To Row_count
    	
    	previous_parent = refinedVal(ws.cells(i-1,1).value)
    	previous_child = refinedVal(ws.cells(i-1,2).value)
    	' checking condition for previous values	
    	If StrComp(ws.cells(i,1).value, ws.cells(i-1,1).value, vbTextCompare) = 0 Then
    		XmlFile.write("<Parent>""</Parent>")
    		XmlFile.write(vbTab)
    		XmlFile.write("<Child>"&Subsidiary&"</Child>")
    		XmlFile.write("<Child>"&GroupAbbreviation&"</Child>")
    		XmlFile.write("<Child>"&VotingPercentage&"</Child>")
    		XmlFile.write("<Child>"&GroupInterestPercentage&"</Child>")
    		XmlFile.write("<Child>"&Jurisdiction&"</Child>")
    		XmlFile.write("<Child>"&RegisteredAddress&"</Child>")
    		XmlFile.write("<Child>"&Country&"</Child>")
    		XmlFile.write(vbCrLf)
    	End If
    		
    	If StrComp(ws.cells(i,1).value, ws.cells(i-1,2).value, vbTextCompare) = 0 Then
    		XmlFile.write("<Parent>""</Parent>")
    		XmlFile.write(vbTab)
    		XmlFile.write("<Child>"&Subsidiary&"</Child>")
    		XmlFile.write("<Child>"&GroupAbbreviation&"</Child>")
    		XmlFile.write("<Child>"&VotingPercentage&"</Child>")
    		XmlFile.write("<Child>"&GroupInterestPercentage&"</Child>")
    		XmlFile.write("<Child>"&Jurisdiction&"</Child>")
    		XmlFile.write("<Child>"&RegisteredAddress&"</Child>")
    		XmlFile.write("<Child>"&Country&"</Child>")
    		XmlFile.write(vbCrLf)
    		       
    	REM Else
    		REM XmlFile.write("<Parent>"&Parent&"</Parent>"&vbNewLine)	
    		REM XmlFile.write("<Child>"&Child&"</Child>"&vbNewLine)
    	
    	End If
    	
    Next
    XmlFile.write("</Country>"&vbNewLine)
    
    wb.close ' closing the excel sheet that was opened
    oExcel.Quit ' deleting excel object resources
    MsgBox("Done")
    
    	
    End Sub
    
    
    
    Function refinedVal(nodeValue)  
       Dim sval
       If IsNull(nodeValue) Then  
         refinedVal = ""  
       Else  
         sval = CStr(nodeValue)  
         sval = Replace(sval, "&", "&amp;") 'do ampersand first !  
         sval = Replace(sval, "'", "&apos;")  
         sval = Replace(sval, """", "&quot;")  
         sval = Replace(sval, "<", "&lt;")  
         sval = Replace(sval, ">", "&gt;")  
         refinedVal = sval  
       End If  
    End Function
    
    
    call xlsToxml
    
    

    I am just getting a flat xml tree instead of nested one.

    Friday, January 10, 2020 6:24 AM

All replies

  • Hi vishy,

    I see only the flat structure:

        <parent>...</parent>
        <child>...</child>
        <child>...</child>

    and never a nested structure like:

        <parent>...
            <child>...</child>
            <child>...</child>
        </parent>

    Imb.

    Friday, January 10, 2020 8:06 AM
  • Hi Imb-mb,

    yes.. that is what I require at nested structure.

    Monday, January 13, 2020 12:45 PM
  • A few things I don't fully understand at this time:

    1. Why did you not post an Excel file with example data, so we can experiment?

    2. Why did you not use XMLHTTP library to work with XML data? Using a home-grown XML parser seems counter-productive, but there may be a good reason.

    3. Why did you not write about your experiences with DoCmd.ExportXML and its cousins?

    4. Is this a Microsoft Access Database issue, the topic of this forum?


    -Tom. Microsoft Access MVP


    Tuesday, January 14, 2020 4:42 AM
  • yes.. that is what I require at nested structure.

    Hi vishy,

    You could modify your code …

    Imb.

    Tuesday, January 14, 2020 7:57 AM