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
34 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
 

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
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!

 

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 500 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

708 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

19 Experts available now in Live!

Get 1:1 Help Now