Solved

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

Posted on 2016-11-27
5
42 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
  • 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 62

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 62

Expert Comment

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

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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:…
More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

920 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now