Victor Charles
asked on
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.Wind ows.Forms. Applicatio n.StartupP ath + "\file1.xml")
Dim xDocF2 As XDocument = XDocument.Load(System.Wind ows.Forms. Applicatio n.StartupP ath + "\file2.xml")
Dim nodesf1 = (From node In xDocF1.Descendants("Table1 ")
Group By Key = New GroupKey With {.CTRY = node.Element("COUNTRY").Va lue, .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").Va lue, .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").V alue} Equals New GroupKey With {.CTRY = group2(0).Element("COUNTRY ").Value, .SN = group2(0).Element("NSN").V alue}
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().Equal s("COUNTRY ") Or node.Name.ToString().Equal s("NSN") Then
Continue For
End If
Dim g1Element As XElement = g1.Element(node.Name.ToStr ing())
If g1Element = "" Then
If g1Element IsNot Nothing Then
g1Element.Value = node.Value
Else
g1.Add(node)
End If
End If
Next
Next
Thanks,
Victor
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.Wind
Dim xDocF2 As XDocument = XDocument.Load(System.Wind
Dim nodesf1 = (From node In xDocF1.Descendants("Table1
Group By Key = New GroupKey With {.CTRY = node.Element("COUNTRY").Va
Select grouping).ToList()
Dim nodesf2 = (From node In xDocF2.Descendants("Table1
Group By Key = New GroupKey With {.CTRY = node.Element("COUNTRY").Va
Select grouping).ToList()
Dim matchf1f2 = (From group1 In nodesf1
Join group2 In nodesf2 On New GroupKey With {.CTRY = group1(0).Element("COUNTRY
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().Equal
Continue For
End If
Dim g1Element As XElement = g1.Element(node.Name.ToStr
If g1Element = "" Then
If g1Element IsNot Nothing Then
g1Element.Value = node.Value
Else
g1.Add(node)
End If
End If
Next
Next
Thanks,
Victor
Please post the XML file that this code is working with.
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.
ASKER
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
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
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Fernando,
I will try it and get back to you.
Thank You.
Victor
I will try it and get back to you.
Thank You.
Victor
ASKER
Hi Fernando,
It works.
Thank You.
Victor
It works.
Thank You.
Victor
Not a problem Victor, glad to help.