Solved

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

Posted on 2016-11-27
5
51 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

730 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