如何使用VBA将大型XML文件拆分为较小的文件 [英] How to split large XML files into smaller ones using VBA

问题描述

下面是我正在使用的XML文件格式示例，我想将文件拆分为较小的部分.问题是有2个子节点"Header"和"Records"，我无法使用"Header"和&页脚"节点

Hi Below is a sample XML file format which I am working and I would like to split the file in the smaller parts. the problem is that there are 2 child node "Header" and "Records" and I am unable to save the XML file using the "Header" & "Footer" Node

<PortfolioBulk2_0_RES>
  <Header>
    <ProviderCode>123</ProviderCode>
    <FileID>20170817</FileID>
    <NumInputSubjects>23123</NumInputSubjects>
    <ChunkID>1</ChunkID>
    <RecordMin>1</RecordMin>
    <RecordMax>23123</RecordMax>
  </Header>
  <Record>
    <RecordId>1</RecordId>
    <ProviderSubjectNo>123456789</ProviderSubjectNo>
    <PackageLabel>GOLD</PackageLabel>
    <Error>
      <No>811</No>
      <Description>Subject not found</Description>
    </Error>
  </Record>
  <Record>
    <RecordId>2</RecordId>
    <ProviderSubjectNo>654789321</ProviderSubjectNo>
    <PackageLabel>GOLD</PackageLabel>
    <Error>
      <No>811</No>
      <Description>Subject not found</Description>
    </Error>
  </Record>
  <Footer>
    <StartDateTime>2008201712:18:06</StartDateTime>
    <StopDateTime>2008201717:19:00</StopDateTime>
    <NoIndividualsOK>13185</NoIndividualsOK>
    <NoCompaniesOK>546</NoCompaniesOK>
    <NoIndividualsError>282</NoIndividualsError>
    <NoCompaniesError>20</NoCompaniesError>
    <NoUnknownsError>9090</NoUnknownsError>
  </Footer>
</PortfolioBulk2_0_RES>

这是我在VBA中的代码.我希望有人能建议如何在每个以XML格式保存的文件中循环页眉和页脚节点.谢谢

Here is my code in VBA. I am hoping if anyone can suggest how to loop Header and Footer Nodes in Each file which is saved in the XML format. Thanks

Sub SPLIT()
numFiles = 2  'number of output files

Set src = CreateObject("Msxml2.DOMDocument.6.0")
src.async = False
src.Load "C:\Users\104704\Documents\Office 1\04_Raw Data\09_AECB Bulk\CLI working\01.xml"

Set Nodes = src.SelectNodes("//Record")
numnodes = Nodes.Length \ numFiles  'number of nodes per output file
'MsgBox (numnodes)

Set XML = Nothing
For i = 0 To Nodes.Length - 1
  'create a new XML object on the first iteration and every time numNodes
  'nodes have been added to the current object
  If i Mod numnodes = 0 Then
    If Not XML Is Nothing Then
      'if we already have an XML object: save it to a file
      Set prolog = XML.createProcessingInstruction("xml", "version='1.0'")
      XML.InsertBefore prolog, XML.ChildNodes(0)
      XML.Save "C:\Users\104704\Documents\Office 1\04_Raw Data\09_AECB Bulk\CLI working\" & (i \ numnodes - 1) & ".xml"
    End If
  Set XML = CreateObject("Msxml2.DOMDocument.6.0")
  Set root = XML.createElement("PortfolioBulk2_0_RES")

    XML.appendChild root

  End If

  root.appendChild Nodes.Item(i)
Next
'save unsaved XML object
If Not XML Is Nothing Then
  Set prolog = XML.createProcessingInstruction("xml", "version='1.0'")
  XML.InsertBefore prolog, XML.ChildNodes(0)
  XML.Save "C:\Users\104704\Documents\Office 1\04_Raw Data\09_AECB Bulk\CLI working\" & (i \ numnodes - 1) & ".xml"
End If
End Sub

推荐答案

@ O.Cheema，据我了解，您想将现有的 record 节点导出到新的xml中.下面的代码是纯VBA并使用xmldom:

@O.Cheema, as far as I understood you want to export the existing record nodes into a new xml. The code below is pure VBA and uses xmldom:

Option Explicit
Dim src As Object
Dim XML As Object

Sub Split2()
  Dim myNodes  As Object                ' node list
  Dim nd       As Object                ' single node
  Dim myClone  As Object                ' copy of source node
  Dim i        As Integer
  Dim numNodes As Integer
  Dim numFiles As Integer: numFiles = 2  ' Output files
' wanted as root in target
  Dim tag      As String: tag = "PortfolioBulk2_0_RES"
' xml object can be defined and loaded via expression
  Dim sTarget  As String
  sTarget = "<?xml version=""1.0"" encoding=""utf-8""?><" & tag & ">" & vbCrLf & "</" & tag & ">"
' 1. SOURCE
' set socure file to memory
  Set src = CreateObject("Msxml2.DOMDocument.6.0")
  src.aSync = False
' Load source file (physically)
  src.Load ThisWorkbook.Path & "\data\unsplitted.xml"
' 2. TARGET
' set target to memory
  Set XML = CreateObject("Msxml2.DOMDocument.6.0")
  XML.aSync = False
' Load target STRING via LoadXML method
  XML.LoadXML sTarget
' -------------------------------
' define node list in source file
' -------------------------------
  Set myNodes = src.DocumentElement.SelectNodes("Record")
  numNodes = myNodes.Length \ numFiles  'number of nodes per output file
  MsgBox (numNodes)

  For Each nd In myNodes
      i = i + 1
    ' -----------------
    ' clone source node
    ' -----------------
      Set myClone = nd
    ' add to target as sub node under root tag
      XML.DocumentElement.appendchild myClone

  Next nd
  ' -----------------
  ' Save target
  ' -----------------
  XML.Save ThisWorkbook.Path & "\data\splitted(" & (i \ numNodes - 1) & ").xml"

End Sub

这篇关于如何使用VBA将大型XML文件拆分为较小的文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！