Solved

Help with deleting records in file1 in data in found in file2 using VB.NET

Posted on 2016-11-27
5
52 Views
Last Modified: 2016-11-30
Hi,

 I am using the code to compare two files (file1.xml, file2.xml) and copy data from fields in file2 to file1 where the same fields in file1 are empty. How do I modify the code to delete records in file1 where the NSN values in file2 don't exist in file1?

  If ComboBox3.SelectedIndex = 0 Then
             zz = 0
             Dim xDocF1 As XDocument = XDocument.Load(System.Windows.Forms.Application.StartupPath + "\file1.xml")
             Dim xDocF2 As XDocument = XDocument.Load(System.Windows.Forms.Application.StartupPath + "\file2.xml")
             Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
                            Group By Key = New GroupKey With {.CTRY = node.Element("COUNTRY").Value, .SN = node.Element("NSN").Value} Into grouping = Group
                            Select grouping).ToList()

             Dim nodesf2 = (From node In xDocF2.Descendants("Table1")
                            Group By Key = New GroupKey With {.CTRY = node.Element("COUNTRY").Value, .SN = node.Element("NSN").Value} Into grouping = Group
                            Select grouping).ToList()

             Dim matchf1f2 = (From group1 In nodesf1
                              Join group2 In nodesf2 On New GroupKey With {.CTRY = group1(0).Element("COUNTRY").Value, .SN = group1(0).Element("NSN").Value} Equals New GroupKey With {.CTRY = group2(0).Element("COUNTRY").Value, .SN = group2(0).Element("NSN").Value}
                              Where group2 IsNot Nothing
                              Select group1, group2).ToList()

             For Each grouping In matchf1f2
                 Dim g1 As XElement = CType(grouping.group1(0), XElement)
                 Dim g2 As XElement = CType(grouping.group2(0), XElement)

                 For Each node As XElement In g2.Descendants()
                     If node.Name.ToString().Equals("COUNTRY") Or node.Name.ToString().Equals("NSN") Then
                         Continue For
                     End If
                     Dim g1Element As XElement = g1.Element(node.Name.ToString())
                     If g1Element = "" Then
                         If g1Element IsNot Nothing Then
                             g1Element.Value = node.Value


                         Else
                             g1.Add(node)
                         End If
                     End If
                 Next
             Next

Open in new window


 Thanks,

 Victor
0
Comment
Question by:vcharles
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 

Author Comment

by:vcharles
ID: 41905007
Hi,

 Below is an example of the xml files, If I have file1 and  file2, I want to compare data in NSN from file2 with data in NSN from file1, if NSN values in file2 are not found in file1, I want to delete the record in file1 and save all the changes in file1 as file3.   For example:


 file1.xml:

 <Root>
 <Table1>
 <ID>1</ID>
 <Receiver>BEL</Receiver>
 <Donor>USA</Donor>
 <NSN>111</NSN>
 </Table1>
 <Table1>
 <ID>2</ID>
 <Receiver>FRA</Receiver>
 <Donor>DNK</Donor>
 <NSN>112</NSN>
 </Table1>
 <Table1>
 <ID>3</ID>
 <Receiver>ITA</Receiver>
 <Donor>GBR</Donor>
 <NSN>113</NSN>
 </Table1>
 </Root>

 file2.xml
 <Root>
 <Table1>
 <ID>1</ID>
 <Receiver>BEL</Receiver>
 <Donor>USA</Donor>
 <NSN>111</NSN>
 </Table1>
 <Table1>
 <ID>2</ID>
 <Receiver>FRA</Receiver>
 <Donor>DNK</Donor>
 <NSN>112</NSN>
 </Table1>
 <Table1>
 <ID>3</ID>
 <Receiver>ITA</Receiver>
 <Donor>GBR</Donor>
 <NSN>115</NSN>
 </Table1>
 <Table1>
 <ID>4</ID>
 <Receiver>BEL</Receiver>
 <Donor>USA</Donor>
 <NSN>116</NSN>
 </Table1>
 <Table1>
 <ID>5</ID>
 <Receiver>FRA</Receiver>
 <Donor>DNK</Donor>
 <NSN>118</NSN>
 </Table1>
 <Table1>
 <ID>6</ID>
 <Receiver>ITA</Receiver>
 <Donor>GBR</Donor>
 <NSN>120</NSN>
 </Table1>
 </Root>


 file3.xml:
 <Root>
 <Table1>
 <ID>1</ID>
 <Receiver>BEL</Receiver>
 <Donor>USA</Donor>
 <NSN>111</NSN>
 </Table1>
 <Table1>
 <ID>2</ID>
 <Receiver>FRA</Receiver>
 <Donor>DNK</Donor>
 <NSN>112</NSN>
 </Table1>
 </Root>

 Only those two records are available in file3.xml because NSN = 113 is not found in file2.xml. The actual xml files contain more data elements.

 Thanks,
0
 

Author Comment

by:vcharles
ID: 41907032
Hi,

If not possible to delete the records in file1, is it possible to create a another xml file with records in file1 not found in file2?  than I create a new column in the new file and enter the word delete to compare with original file1 to delete those records.

Thanks,

Victor
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41907211
Hi Victor;

The below code snippet should do what you need.
'' Load the XML documents into memory
Dim xDocF1 = XDocument.Load("C:\Working Directory\VictorFile-1-1.xml")                                          
Dim xDocF2 = XDocument.Load("C:\Working Directory\VictorFile-2-1.xml")                                          

'' Find all matching nodes                                                                                                             
Dim results = (From n1 In XDocF1.Descendants("Table1")                                                        
               Join n2 In XDocF2.Descendants("Table1") On n1.Element("NSN").Value Equals n2.Element("NSN").Value
               Select n1).ToList()                                                                                    

'' Copy XDocF1 to the new XDocF3                                                                                                                
Dim XDocF3 = xDocF1.Document()  

'' Replace all nodes with the results of the query                                                                                
XDocF3.Root.ReplaceAll(results)

'' Save to the file system
XDocF3.Save("Save to new file name and location") 

Open in new window

0
 

Author Comment

by:vcharles
ID: 41907296
Hi Fernando,

It works!

Thank You.

Victor
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41907333
Not a problem Victor, glad to help.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question