none
Import XML with elements that have the structure (table)? RRS feed

  • Question

  • I have managed to do this using the XML Map feature but I had to map every row, even though they all had the same "headers" or field names. Fortunately there were only 10 rows, but I wonder how this can be done better when there are hundreds of rows. I would be interested to hear from anyone who can provide advice.

    The XML looks like this.

    <?xml version="1.0" encoding="UTF-8"?>
    <EDGUtiltiy>
      
      <Virtual_Com_Port_Mapping>
        <Version>1.02</Version>
      </Virtual_Com_Port_Mapping>
      <COM101>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>1</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM101>
      <COM102>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>2</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM102>
      <COM103>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>1</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM103>
      <COM104>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>2</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM104>
      <COM105>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>1</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM105>
      <COM106>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>2</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM106>
      <COM107>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>1</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM107>
      <COM108>
        <Model>1522</Model>
        <Mac1>00:D0:C9:xx:xx:xx</Mac1>
        <Mac2>00:D0:C9:xx:xx:xx</Mac2>
        <Addr1>xx.xx.xx.xx</Addr1>
        <Addr2>xx.xx.xx.xx</Addr2>
        <Port>2</Port>
        <AutoReconn>1</AutoReconn>
        <VComType>0</VComType>
        <TcpRecvTimeout>3000</TcpRecvTimeout>
        <ConnDelay>0</ConnDelay>
        <ConnTimeout>6000</ConnTimeout>
        <InitDelay>2</InitDelay>
        <CloseDelay>0</CloseDelay>
        <BaudRate>9600</BaudRate>
        <Parity>0</Parity>
        <DataBits>8</DataBits>
        <StopBits>0</StopBits>
        <FlowControl>0</FlowControl>
      </COM108>
    </EDGUtiltiy>


    R Campbell



    Tuesday, November 15, 2016 11:50 AM

Answers

  • Hi Dick Campbell,

    you had mentioned that you had try to use XML Mapping.

    did you try to do like I mentioned below?

    Sub CreateXMLList()
       Dim oMyMap As XmlMap
       Dim strXPath As String
       Dim oMyList As ListObject
       Dim oMyNewColumn As ListColumn
    
       ' Add a schema map.
       ' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
       ThisWorkbook.XmlMaps.Add ("C:\BookData.xsd")
    
       ' Identify the target schema map.
       Set oMyMap = ThisWorkbook.XmlMaps("BookInfo_map")
    
       ' Create a new list in A1.
       Range("A1").Select
       Set oMyList = ActiveSheet.ListObjects.Add
    
       ' Find the first element to map.
       strXPath = "/BookInfo/Book/ISBN"
       ' Map the element.
       oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to  map.
       strXPath = "/BookInfo/Book/Title"
       ' Map the element
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to map.
       strXPath = "/BookInfo/Book/Author"
       ' Map the element.
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to map.
       strXPath = "/BookInfo/Book/Quantity"
       ' Map the element.
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Give the columns logical names
       oMyList.ListColumns(1).Name = "ISBN"
       oMyList.ListColumns(2).Name = "Title"
       oMyList.ListColumns(3).Name = "Author"
       oMyList.ListColumns(4).Name = "Quantity"
    
    End Sub
    
    

    then import the XML File using code mentioned below.

    Sub ImportXmlFromFile()
         ThisWorkbook.XmlMaps("BookInfo_Map").Import ("C:\BookData.xml")
    End Sub
    
    

    For detailed steps please visit the link mentioned below.

    Importing XML Data into a Mapped List in Excel

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dick Campbell Thursday, November 17, 2016 3:20 AM
    Thursday, November 17, 2016 2:26 AM
    Moderator

All replies

  • Excel 2016 has Import by various types of query which does make things a little easier. The XML query produces a separate query for each element but there is provision to multi-select and to nappend all of them into a single query before placing the data into a worksheet. If there was an option (tick box?) for this to happen automatically, this would be a reasonably acceptable solution. Multi-select would still be unacceptable for large numbers of elements. The import process is also surprisingly slow.

    R Campbell

    Tuesday, November 15, 2016 7:17 PM
  • Hi Dick Campbell,

    From the description of the thread it's looks like you are using as manual way to get the data from XML using Query.

    if yes, then you posted the question in incorrect forum.

    this forum handles the issue regarding Excel Object Model.

    it looks like when you try to fetch data using query at that time you want to select the multiple files but Excel Application only allow you to select a single file at a time.

    So here you need to do that operation multiple times if you want to import data from multiple files.

    if you think that this feature should be added in future then please submit your feedback in below mentioned link.

    Excel User Voice

    if you want code to do this operation then let me know about that I will provide you a code to import data from XML file in Excel.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, November 16, 2016 7:57 AM
    Moderator
  • Thanks Deepak, While I write lots of Excel VBA code, I am attempting to do this through the the standard Excel UI in this case. I have written T SQL code in the past that handles this quite well. You supply an XPath to the level of the "table" elements (rows) and can then import the multiple XML elements into a single record set with minimal effort. If you can point me to some VBA code that does something similar, I certainly would be interested to see it. To the orignal question. It is just a single file with 10 "row" elements with identical "field names", as per the XML text in the original post. I have tried a couple of import methods, with XLM mapping, including the Query Manager" in Excel 2016. They all produce 10 single row tables that have to be manually appended into a single table. The Excel 2016 Query Manager is an improvement, and acceptable for 10 rows, but not for hundreds of rows. Can you suggest a more suitable forum.

    R Campbell


    Wednesday, November 16, 2016 12:19 PM
  • Hi Dick Campbell,

    you had mentioned that you had try to use XML Mapping.

    did you try to do like I mentioned below?

    Sub CreateXMLList()
       Dim oMyMap As XmlMap
       Dim strXPath As String
       Dim oMyList As ListObject
       Dim oMyNewColumn As ListColumn
    
       ' Add a schema map.
       ' ThisWorkbook.XmlMaps.Add (ThisWorkbook.Path & "\Myschema.xsd")
       ThisWorkbook.XmlMaps.Add ("C:\BookData.xsd")
    
       ' Identify the target schema map.
       Set oMyMap = ThisWorkbook.XmlMaps("BookInfo_map")
    
       ' Create a new list in A1.
       Range("A1").Select
       Set oMyList = ActiveSheet.ListObjects.Add
    
       ' Find the first element to map.
       strXPath = "/BookInfo/Book/ISBN"
       ' Map the element.
       oMyList.ListColumns(1).XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to  map.
       strXPath = "/BookInfo/Book/Title"
       ' Map the element
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to map.
       strXPath = "/BookInfo/Book/Author"
       ' Map the element.
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Add a column to the list.
       Set oMyNewColumn = oMyList.ListColumns.Add
       ' Find the next element to map.
       strXPath = "/BookInfo/Book/Quantity"
       ' Map the element.
       oMyNewColumn.XPath.SetValue oMyMap, strXPath
    
       ' Give the columns logical names
       oMyList.ListColumns(1).Name = "ISBN"
       oMyList.ListColumns(2).Name = "Title"
       oMyList.ListColumns(3).Name = "Author"
       oMyList.ListColumns(4).Name = "Quantity"
    
    End Sub
    
    

    then import the XML File using code mentioned below.

    Sub ImportXmlFromFile()
         ThisWorkbook.XmlMaps("BookInfo_Map").Import ("C:\BookData.xml")
    End Sub
    
    

    For detailed steps please visit the link mentioned below.

    Importing XML Data into a Mapped List in Excel

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by Dick Campbell Thursday, November 17, 2016 3:20 AM
    Thursday, November 17, 2016 2:26 AM
    Moderator
  • Thanks Deepak,

    I am interested to see how xPath is used in Excel VBA. I don't have time to work on this right now but I will do so when I get a chance. I would hope to develop fairly generic code that works just the way I would like it to. Thanks again.


    R Campbell

    Thursday, November 17, 2016 3:17 AM