Solved

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

Posted on 2016-11-26
8
33 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 62

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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 62

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 62

Expert Comment

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

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes relatively difficult and non-obvious issues that are likely to arise when creating COM class in Visual Studio and deploying it by professional MSI-authoring tools. It is assumed that the reader is already familiar with the cla…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

706 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

20 Experts available now in Live!

Get 1:1 Help Now