散装过滤器在Excel中解析ONIX数据(XML) [英] Bulk filter & parse ONIX data (XML) in Excel

问题描述

我有3000个带有书籍元数据的ONIX样式的xml URL.我想从这些文件中过滤出几个节点，然后将它们解析为单个工作表中的Excel行.

I have 3000 ONIX-style xml URLs with book metadata. I would like to filter a few nodes out of these files and parse them into Excel rows in a single worksheet.

这是XML路径的示例: http://btsoep.appspot.com/rest/book/9789082516425

This is an example of a XML-path: http://btsoep.appspot.com/rest/book/9789082516425

我想将此XML数据过滤为

I would like to filter this XML data on things as

<Product>
 <Title>
  <TitleText>

和

<Product>
 <Contributor>
  <PersonName>

我需要Excel工作表中检索到的数据，其中每个URL都有自己的行.因此，URL在A行中，TitleText在B行中，PersonName在C行中.

I need the retrieved Data in an Excel worksheet where every URL has it's own row. So the URLs are in row A, TitleText is in row B and PersonName is in row C.

我该怎么做?

到目前为止，我尝试过的是:-使用wget首先下载所有xml数据，然后尝试批量解析这是excel.这可能有效，但这不是必需的.-在Excel中使用默认的XMLimport函数.我似乎无法批量运行此程序.

What I've tried so far is: -using wget to download all the xml data first and then try to bulk parse this is excel. This could work but is unnecessary. -using the default XMLimport function in Excel. I can't seem to run this in a batch.

我没有过滤XML文件并在Excel工作表中进行解析的经验.我并没有要求任何人为我解决此问题或为我编写代码，但我想朝着正确的方向迈出一步.哪种工具最适合这种情况?再次感谢.

I don't have any experience with filtering XML files and parsing this in an Excel worksheet. I'm not asking anyone to fix this for me or write code for me, but I would like a step in the good direction. Which tools would be the best to use for this case? Thanks again.

推荐答案

这应该可以帮助您入门

Option Explicit

Sub parseONIX()

    Dim URL As String

    URL = "http://btsoep.appspot.com/rest/book/9789082516425"
 '  URL = "https://www.w3schools.com/xml/plant_catalog.xml"

    Dim XMLPage As New MSXML2.XMLHTTP60
    XMLPage.Open "GET", URL, False
    XMLPage.send

    Dim XMLDoc As New MSXML2.DOMDocument
    XMLDoc.LoadXML XMLPage.responseText

    Debug.Print XMLDoc.ChildNodes(0).BaseName
    Debug.Print XMLDoc.ChildNodes(1).BaseName
    Debug.Print XMLDoc.ChildNodes(1).ChildNodes(0).BaseName
    Debug.Print XMLDoc.ChildNodes(1).ChildNodes(1).BaseName
    Debug.Print XMLDoc.getElementsByTagName("Product").Item(0).BaseName

    Dim i As Integer

    For i = 0 To XMLDoc.getElementsByTagName("Measure").Length - 1

        Debug.Print "type: "; XMLDoc.getElementsByTagName("Measure")(i).ChildNodes(0).Text,
        Debug.Print XMLDoc.getElementsByTagName("Measure")(i).ChildNodes(1).Text,
        Debug.Print XMLDoc.getElementsByTagName("Measure")(i).ChildNodes(2).Text

    Next i



End Sub

这篇关于散装过滤器在Excel中解析ONIX数据(XML)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！