[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

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

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 64

Expert Comment

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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

649 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