Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Help with copying data from all data elements from file2 to file1 based on matching values including additional records in file2 using VB,NET

Posted on 2016-10-06
10
Medium Priority
?
80 Views
Last Modified: 2016-11-25
Hi,

 How do I copy data from all matching data elements of file2 to file1 if values in 2 data elements (Receiver, NSC)  in both files are identical also include additional records from file2 and save the results in a new file, (i.e. file3.xml), using VBNET?

 For example if file1 contains:

 File1.xml
 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <Table1>
   <Link_ID>1</Link_ID>
   <Receiver>USA</Receiver>
   <AGD>M5</AGD>
 <NSC><M2</NSC>
   <FCT></FCT>
   <FCI></FCI>
 </Table1>

 File2.xml

 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <FCD>
   <ID>1</ID>
   <Receiver>USA</Receiver>
 <NSC><M2</NSC>
   <FCT>M11</FCT>
   <FCI>M14</FCI>
  </FCD>
 <FCD>
   <ID>2</ID>
   <Receiver>BEL</Receiver>
 <NSC><M23</NSC>
   <FCT>M3</FCT>
   <FCI>M77</FCI>
  </FCD>
 <FCD>
   <ID>3</ID>
   <Receiver>BEL</Receiver>
 <NSC><M2</NSC>
   <FCT>M33</FCT>
   <FCI>M35</FCI>
 </FCD>
 </Root>

 How do I  save new values of file1 in file3.xml as shown below since only one record match in both files?

 File3.xml
 <?xml version="1.0" encoding="utf-8"?>
 <Root>
 <Table1>
   <ID>1</ID>
   <Receiver>USA</Receiver>
   <AGD>M5</AGD>
 <NSC><M2</NSC>
   <FCT>M11</FCT>
   <FCI>M14</FCI>
  </Table1>
 <Table1>
   <ID>2</ID>
   <Receiver>BEL</Receiver>
   <AGD>M5</AGD>
 <NSC><M23</NSC>
   <FCT>M3</FCT>
   <FCI>M77</FCI>
  </Table1>
 <Table1>
   <ID>3</ID>
   <Receiver>BEL</Receiver>
   <AGD>M5</AGD>
 <NSC><M2</NSC>
   <FCT>M33</FCT>
   <FCI>M35</FCI>
 </Table1>
 </Root>
0
Comment
Question by:vcharles
  • 6
  • 4
10 Comments
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41832049
Use methods from the XmlDocument to retrieve the values to compare and to compile data for the output. There are many ways to do that. Here's a couple of helper methods I use, for example:
Public Shared Function GetNodeValue(doc As XmlDocument, node As String) As String
	Dim output As String = ""
	Dim nodes As XmlNodeList = doc.DocumentElement.SelectNodes("/MyRootNode/" & node)
	If nodes.Count = 1 Then
		output = nodes(0).InnerText.trim
		If output.Length > 0 Then
			output = Decode(output) ' Decode() is a helper method to remove encoding
		End If
	End If
	Return output
End Function

Public Shared Function GetNodeValue(myXmlNode As XmlNode, node As String) As String
	Dim output As String = ""
	Dim nodes As XmlNodeList = myXmlNode.SelectNodes(node)
	If nodes.Count = 1 Then
		output = nodes(0).InnerText.trim
		If output.Length > 0 Then
			output = Decode(output)
		End If
	End If
	Return output
End Function

Open in new window


You can also use the XmlDocument class to write XML. However, if you can write a string to a file, you can write an XML file, which is just text.

Good luck!
0
 

Author Comment

by:vcharles
ID: 41832930
Hi,

can you please help me understand your code. Also, I'm trying to achieve this from a button click event.

Thanks,

Victor
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41833015
I think I can explain it to you in a way you can understand.  Please let me ask a few questions of you. First, what is your VB.NET skill level? (Beginner, novice, intermediate, etc.)

What is your goal? Do you want to learn how to resolve this issue or do you just want code that helps resolve the issue? I think I can help you out either way.

Please read the following method. Let's call it "Example1":
' Example1
Public Shared Function GetNodeValue(doc As XmlDocument, node As String) As String
	Dim output As String = ""
	Dim nodes As XmlNodeList = doc.DocumentElement.SelectNodes("/MyRootNode/" & node)
	If nodes.Count = 1 Then
		output = nodes(0).InnerText.trim
		If output.Length > 0 Then
			output = Decode(output) ' Decode() is a helper method to remove encoding
		End If
	End If
	Return output
End Function

Open in new window


What is the first thing you don't understand when reading Example1?

PS: If you're not familiar with the XmlDocument class, I recommend that you read the .NET reference page. Even if you don't immediately comprehend what your reading, it will help you later. System.Xml.XmlDocument Reference
0
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:vcharles
ID: 41833378
Hi,

I would say intermediate, don't follow in your code where you are setting the condition for Receiver and NSC to be the same, will takr a look at the link.

Thanks,

Victor
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41834453
Example1 doesn't compare anything. It's a helper method to get the value of a node in an XmlDocument. You have to do that before you have anything to compare.

Example1 accepts an XmlDocument and the name of a node (XML element). It returns a String that represents the value of that node. Do that for the the two desired elements in each file and compare. Then you'll know if there is a match.

' read file1 & file2 into MemoryStreams ...
Dim myStreamReader As New StreamReader(file1Path) ' need to .Close or use Using.
Dim xmlString As String = myStreamReader.ReadToEnd
Dim stream1 As New MemoryStream(Encoding.UTF8.GetBytes(xmlString)) ' need to .Close or use Using.
myStreamReader = New StreamReader(file2Path) ' need to .Close or use Using.
xmlString  = myStreamReader.ReadToEnd
Dim stream2 As New MemoryStream(Encoding.UTF8.GetBytes(xmlString)) ' need to .Close or use Using.

' load MemoryStreams into XmlDocuments ...
Dim myXmlFile1 As New XmlDocument
myXmlFile1.Load(stream1)
Dim myXmlFile2 As New XmlDocument
myXmlFile2.Load(stream2)

Dim file1ReceiverNsc as String = GetNodeValue(myXmlFile1, "Table1/Receiver")
file1ReceiverNsc &= GetNodeValue(myXmlFile1, "Table1/NSC")
Dim file2ReceiverNsc as String = GetNodeValue(myXmlFile2, "???") 
' we don't know which node to fetch because there are multiple Receiver & NSC elements in file2. Needs more specificity.

' Example1
Public Shared Function GetNodeValue(doc As XmlDocument, node As String) As String
	Dim output As String = ""
	Dim nodes As XmlNodeList = doc.DocumentElement.SelectNodes("/Root/" & node)
	If nodes.Count = 1 Then
		output = nodes(0).InnerText.trim
		If output.Length > 0 Then
			output = Decode(output) ' Decode() is a helper method to remove encoding
		End If
	End If
	Return output
End Function

Open in new window

0
 

Author Comment

by:vcharles
ID: 41834576
Hi,

I am trying the code in Part A but still running into some issues.

error message "'Decode' is not declared. It may be inaccessible due to its protection level.", do I need to import a system file?

Regarding your question:

' we don't know which node to fetch because there are multiple Receiver & NSC elements in file2. Needs more specificity.

Does the code need to fetch all the data elements in file2? I would like to avoid hard coding the names of the nodes if at all possible.


The code in Part B is used to copy data in data elements from file2 to matching data elements in file1 where data in       COUNTRY and NSN data elements in both file are identical, unfortunately I can't figure out how to modify it to also copy additional records from file two to file which COUNTRY and NSN values don't match in both files. Any ideas how to modify it to achieve what I'm trying to do?

Part A:

   Private Sub Button8_Click(sender As System.Object, e As System.EventArgs) Handles Button8.Click
        Dim file1path As String
        file1path = System.IO.Path.Combine(Application.StartupPath + "\AOP40.xml")
        Dim file2path As String
        file2path = System.IO.Path.Combine(Application.StartupPath + "\AOP40CTRY.xml")

        ' read file1 & file2 into MemoryStreams ...
        Dim myStreamReader As New StreamReader(file1Path) ' need to .Close or use Using.
        Dim xmlString As String = myStreamReader.ReadToEnd
        Dim stream1 As New MemoryStream(Encoding.UTF8.GetBytes(xmlString)) ' need to .Close or use Using.
        myStreamReader = New StreamReader(file2Path) ' need to .Close or use Using.
        xmlString = myStreamReader.ReadToEnd
        Dim stream2 As New MemoryStream(Encoding.UTF8.GetBytes(xmlString)) ' need to .Close or use Using.

        ' load MemoryStreams into XmlDocuments ...
        Dim myXmlFile1 As New XmlDocument
        myXmlFile1.Load(stream1)
        Dim myXmlFile2 As New XmlDocument
        myXmlFile2.Load(stream2)

        Dim file1ReceiverNsc As String = GetNodeValue(myXmlFile1, "Table1/Receiver")
        file1ReceiverNsc &= GetNodeValue(myXmlFile1, "Table1/NSC")
        Dim file2ReceiverNsc As String = GetNodeValue(myXmlFile2, "Table2/Receiver")
         file2ReceiverNsc &= GetNodeValue(myXmlFile2, "Table2/NSC")
        ' we don't know which node to fetch because there are multiple Receiver & NSC elements in file2. Needs more specificity.

    End Sub

    Public Shared Function GetNodeValue(doc As XmlDocument, node As String) As String
        Dim output As String = ""
        Dim nodes As XmlNodeList = doc.DocumentElement.SelectNodes("/MyRootNode/" & node)
        If nodes.Count = 1 Then
            output = nodes(0).InnerText.Trim
            If output.Length > 0 Then
                output = Decode(output) ' Decode() is a helper method to remove encoding
            End If
        End If



Part B:

 Private Sub Button7_Click(sender As System.Object, e As System.EventArgs) Handles Button7.Click
        If RadioButton1.Checked Then
            zz = 0
            Dim xDocF1 As XDocument = XDocument.Load(Application.StartupPath + "\File1.xml")
            Dim xDocF2 As XDocument = XDocument.Load(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 IsNot Nothing Then

                        g1Element.Value = node.Value

                    Else
                        g1.Add(node)
                    End If
                Next
            Next
            xDocF1.Save(Application.StartupPath + "\File3.xml")
        End If
0
 
LVL 12

Accepted Solution

by:
funwithdotnet earned 2000 total points
ID: 41835484
You can comment the Decode() line if you like. It is a helper method that does an HTML decode on XML data that has been transmitted via HTTP(S). Included below:
Public Shared Function Decode(input As String) As String
	Dim output As String = ""
	If input.Length > 0 Then
		output = System.Net.WebUtility.HtmlDecode(input)
	End If
	Decode = output
End Function

Open in new window


By loading an XmlDocument, you already have all nodes available,. We just need to act on the ones that need comparison.

File2 has three sets of Receiver & NSC elements. Do you want to compare each of the three sets in file 2 to the values in file1 and take appropriate action based on each of three comparison results?

Yes, I'll be happy to help you achieve what you need for part b. Once you're done with part a, most of what is needed for part b will already be done. Don't fret about it.

Here the next tip ... Use models for structures in the XML files. In this case, I'd create a 'Table1Element' class and a 'FcdElement' class and they would look something like:
Public Class Table1Element

	Public Property Link_ID As String 
	Public Property Receiver As String
	Public Property AGD As String 
	Public Property NSC As String
	Public Property FCT As String
	Public Property FCI As String
	Public Property SourceXmlNode As XmlNode
	Public Property IsValid As Boolean = False

	Public Sub New(table1Node As XmlNode)
		Try
			Dim tempValid As Boolean = True
			' Set properties
			If table1Node IsNot Nothing Then
				Me.SourceXmlNode = table1Node
				Me.Link_ID = GetNodeValue(table1Node, "Link_ID")
				Me.Receiver = GetNodeValue(table1Node, "Receiver")
				Me.AGD = GetNodeValue(table1Node, "AGD")
				Me.NSC = GetNodeValue(table1Node, "NSC")
				Me.FCT = GetNodeValue(table1Node, "FCT")
				Me.FCI = GetNodeValue(table1Node, "FCI")
			Else ' source node is Nothing.
				tempValid = False
			End If
			Me.IsValid = tempValid
		Catch ex As Exception
			Me.IsValid = False
		End Try
	End Sub
End Class

Public Class FcdElement
	Public Property ID As String
	Public Property Receiver As String
	Public Property NSC As String
	Public Property FCT As String
	Public Property FCI As String
	Public Property SourceXmlNode As XmlNode
	Public Property IsValid As Boolean = False

	Public Sub New(fcdNode As XmlNode)
		Try
			Dim tempValid As Boolean = True
			' Set properties
			If fcdNode IsNot Nothing Then
				Me.SourceXmlNode = fcdNode
				Me.ID = GetNodeValue(fcdNode, "ID")
				Me.Receiver = GetNodeValue(fcdNode, "Receiver")
				Me.NSC = GetNodeValue(table1Node, "NSC")
				Me.FCT = GetNodeValue(table1Node, "FCT")
				Me.FCI = GetNodeValue(table1Node, "FCI")
			Else ' source node is Nothing.
				tempValid = False
			End If
			Me.IsValid = tempValid
		Catch ex As Exception
			Me.IsValid = False
		End Try
	End Sub
End Class

' use like:
' Dim myTable1Element as New Table1Element(GetNodeValue(myXmlFile1, "Table1"))
' now you can get the elements within ...
' myTable1Element.Receiver
' myTable1Element.Nsc

Open in new window

0
 

Author Comment

by:vcharles
ID: 41835513
Hi,

My actual project contains about 35 data elements and hundreds or records, the xml files provided was just an example, if at all possible, woudd like to avoid hard coding all the data elements.

Can you help me modify the code in part B to add additional records, this approach avoids hard coding all the data elements. It would also work with other xml files if I just pass the name the fields I want to compare.

Thanks,

Victor
0
 

Author Comment

by:vcharles
ID: 41835518
Hi again,

If not possible to avoid hard ciding the field names, Another approach, is to create another file with all the records that did not match my search criteria, and merge it with the file created with records that matched the search criteria.

As mentioned I am able to create a file for matching records only. I posted a question on how to create a file for non matching records. Would then be able to merge both xml files.
0
 

Author Closing Comment

by:vcharles
ID: 41901394
Thank You.
0

Featured Post

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!

Question has a verified solution.

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

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
Loops Section Overview
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

824 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