Solved

Help with deleting records by comparing two files using VB.NET

Posted on 2016-11-26
8
81 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 and COUNTRY fields in both files are identical and the "Del" field in file2 contains the word Delete?

 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

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
  • 4
  • 3
8 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41904654
Please post the XML file that this code is working with.
0
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 41904675
You can take a different approach and then when the records match then add to a new temp xml file and then at the end of the process delete file1 and 2 and then rename the temp xml to file1 and you will have your result. The advantage of this is that you can compare the 3 of them wile debugging and you will find what you need to adjust without touching the original files.
0
 

Author Comment

by:vcharles
ID: 41904994
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,

Victor
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:vcharles
ID: 41905004
Hi,

Just realized the example is for the other post.

Below is another version for this post.

 Below is an example of the xml files, If I have file1 and  file2, I want to compare data in COUNTRT and NSN from file2 with data in NSN and COUNTRY from file1, if those fields have identical data and the Del field in file 2 contains the word delete, 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>
<Del></Del>
 </Table1>
 </Root>

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


 file3.xml:
 <Root>
 <Table1>
 <ID>3</ID>
 <Receiver>ITA</Receiver>
 <Donor>GBR</Donor>
 <NSN>113</NSN>
 </Table1>
 </Root>

 Only one record is available in file3.xml because "Delete" is not found in file2.xml even though COUNTRY and NSN matched with file2. The actual xml files contain more data elements.
0
 
LVL 63

Accepted Solution

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

This code snippet should do what you need.
'' Load the documents into memory
Dim xDocF1 As XDocument = XDocument.Load("C:\Working Directory\VictorFile1.xml")
Dim xDocF2 As XDocument = XDocument.Load("C:\Working Directory\Victorfile2.xml")

'' Find the nodes that need to be deleted
Dim results = (From f1 In xDocF1.Descendants("Table1")
               Join f2 In xDocF2.Descendants("Table1") On f1.Element("Donor").Value Equals f2.Element("Donor").Value And 
                    f1.Element("NSN").Value Equals f2.Element("NSN").Value
               Where f2.Element("Del").Value.Trim().ToUpper() = "DELETE" 
               Select f1).ToList()

'' Remove the nodes to be deleted from XDocF1
For Each node In results
	node.Remove()
Next

'' Copy the XDocument from XDocF1 to the new XDocF3
Dim XDocF3 = xDocF1.Document()
'' Save XDocF3 to the file system
XDocF3.Save("The file name and location to save to")

Open in new window

0
 

Author Comment

by:vcharles
ID: 41907024
Hi Fernando,

I will try it and get back to you.

Thank You.

Victor
0
 

Author Comment

by:vcharles
ID: 41907456
Hi  Fernando,

It works.

Thank You.

Victor
0
 
LVL 63

Expert Comment

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

Featured Post

More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

Question has a verified solution.

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

This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
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…

726 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