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

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
vcharlesAsked:
Who is Participating?
 
Fernando SotoConnect With a Mentor RetiredCommented:
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
 
Fernando SotoRetiredCommented:
Please post the XML file that this code is working with.
0
 
Jesus RodriguezIT ManagerCommented:
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
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
Hi Fernando,

I will try it and get back to you.

Thank You.

Victor
0
 
vcharlesAuthor Commented:
Hi  Fernando,

It works.

Thank You.

Victor
0
 
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.