?
Solved

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

Posted on 2016-11-27
5
Medium Priority
?
54 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 2000 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

764 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