Solved

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

Posted on 2016-11-26
8
70 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
  • 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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

In my previous two articles we discussed Binary Serialization (http://www.experts-exchange.com/A_4362.html) and XML Serialization (http://www.experts-exchange.com/A_4425.html). In this article we will try to know more about SOAP (Simple Object Acces…
Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

830 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