Help with appending data from file2 to file1 based onn identical ID

Hi,

How do you copy the value of data element from file2 to existing data element in file1 based on identical CTRY and SN data elements? For example if file 1 contains:

<Root>
<Table1>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101</SN>
<ItemB>ItemB</ItemB>
<ItemX>UtemX</ItemX>
<ItemY>ItemY</ItemY>
<ItemZ>ItemZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>BEL</CTRY>
<SN>102</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>

and file2 contains:

<Root>
<TableX>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101</SN>
<ItemB>NewA</ItemB>
<ItemX>NewX</ItemX>
<ItemY>NewY</ItemY>
<ItemZ>NewZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>USA</CTRY>
<SN>104</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>106</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>4</ID>
<CTRY>ITA</CTRY>
<SN>106</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>5</ID>
<CTRY>GBR</CTRY>
<SN>109</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>



New File 1 should contain:

<Root>
<Table1>
<ID>1</ID>
<CTRY>BEL</CTRY>
<SN>101</SN>
<SN>101</SN>
<ItemB>NewA</ItemB>
<ItemX>NewX</ItemX>
<ItemY>NewY</ItemY>
<ItemZ>NewZ</ItemZ>
</Table1>
<Table1>
<ID>2</ID>
<CTRY>BEL</CTRY>
<SN>102</102>
<ItemB></ItemB>
<ItemX>D</ItemX>
<ItemY></ItemY>
<ItemZ></ItemZ>
</Table1>
<Table1>
<ID>3</ID>
<CTRY>USA</CTRY>
<SN>103</SN>
<ItemB></ItemB>
<ItemX>G</ItemX>
<ItemY>H</ItemY>
<ItemZ></ItemZ>
</Table1>
</Root>

Because only record with ID = 1 have the same SN and CTRY for both files.  File2 may have less data or more data elements than file1 but only matching data elements should copy from file2 to file1 when CN and CTRY are identical without adding a new record.

Thanks,
vcharlesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Load file 1 in a XDocument (or XmlDocument) and file 2 also. Then you can merge then on the appropriate element level.

But your sample data has no elements in common.
0
vcharlesAuthor Commented:
Hi,

I need help with matching the data elements in both files in order to copy the values from  file2 to the correct data element in file1

Both SN and CTRY values are the same for both files as shown for ID = 1, would like to take values from file2 and copy them to matching data elements in file1.

Thanks,

Victor
0
Fernando SotoRetiredCommented:
Hi Victor;

You state that the result should be

New File 1 should contain:
<Table1>
  <ID>1</ID>
  <CTRY>BEL</CTRY>
  <SN>101</SN>
  <SN>101</SN>
  <ItemB>NewA</ItemB>
  <ItemX>NewX</ItemX>
  <ItemY>NewY</ItemY>
  <ItemZ>NewZ</ItemZ>
</Table1>

Open in new window

But did you mean the following, with on one <SN>101</SN>?
New File 1 should contain:
<Table1>
  <ID>1</ID>
  <CTRY>BEL</CTRY>
  <SN>101</SN>
  <ItemB>NewA</ItemB>
  <ItemX>NewX</ItemX>
  <ItemY>NewY</ItemY>
  <ItemZ>NewZ</ItemZ>
</Table1>

Open in new window

0
CompTIA Security+

Learn the essential functions of CompTIA Security+, which establishes the core knowledge required of any cybersecurity role and leads professionals into intermediate-level cybersecurity jobs.

vcharlesAuthor Commented:
Hi Fernando,

Initially I wanted to math two data elements (CTRY and SN) but if not possible would like to accomplish the same goal with only matching the SN, but may run into issues if I have duplicate SNs in file1 or file2.

Thanks,

Victor
0
Fernando SotoRetiredCommented:
Victor please see my last post. All I am pointing out is that in File1 you have a SN node and in File2 you have a matching SN node and in in the final result you show two SN node? Should that be two SN nodes or just one SN node?
0
vcharlesAuthor Commented:
Hi,

My mistake, it should be as follows:

New File 1 should contain:
<Table1>
  <ID>1</ID>
  <CTRY>BEL</CTRY>
  <SN>101</SN>
  <ItemB>NewA</ItemB>
  <ItemX>NewX</ItemX>
  <ItemY>NewY</ItemY>
  <ItemZ>NewZ</ItemZ>
</Table1>

Thanks,

Victor
0
vcharlesAuthor Commented:
Hi Fernando,

To be more clear, File2 will not always have all the data elements in File1. Basically, I'm trying to create an update application where users can use their own excel file to copy data to file1 instead of entering the data. Their excel file would be converted to xml and the code would copy their data to file1 where the fields match with file1, based on an identical data field (i.e. SN).

As a safety precaution, incase I have duplicae SN in both files I wanted to use another field to achieve the same goal to make sure the data is copied to the right record in file1.

Thanks,

Victor
0
ste5anSenior DeveloperCommented:
Why converting Excel to XML? It's already XML. Just unzip it.
0
vcharlesAuthor Commented:
File1 is already in xml, need to convert users excel file to xml to copy their data to File1.
0
ste5anSenior DeveloperCommented:
Excel files (xlsx) are zipped XML files.
0
vcharlesAuthor Commented:
If I undertsand correctly, do you mean I can simply use a code to unzip the excel file and rename it as an xml file? If that is the case will post this question as a new topic.
Thanks.
0
ste5anSenior DeveloperCommented:
Just unzip it, and take a look at it..
0
vcharlesAuthor Commented:
Users want to upload the file to the application's folder and transfer the data to file1, unfortunately your approach would not work for them.
Thanks,
V.
0
Fernando SotoRetiredCommented:
Hi Victor;

The below code will do what you need.

// Load the XML documents into memory
XDocument xDocF1 = XDocument.Load("Path to File1.xml");
XDocument xDocF2 = XDocument.Load("Path to File1.xm2");

// Group file1 by ID, CTRY and SN
var nodesf1 = (from node in xDocF1.Descendants("Table1")
               group node by new GroupKey { ID = node.Element("ID").Value, CTRY = node.Element("CTRY").Value, SN = node.Element("SN").Value } into grouping
               select grouping).ToList();
               
// Group file2 by ID, CTRY and SN               
var nodesf2 = (from node in xDocF2.Descendants("Table1")
               group node by new GroupKey { ID = node.Element("ID").Value, CTRY = node.Element("CTRY").Value, SN = node.Element("SN").Value } into grouping
               select grouping).ToList();

// Join both file1 and file2 on the given Key
var matchf1f2 = (from group1 in nodesf1
                 join group2 in nodesf2 on group1.Key equals group2.Key into grouping
                 from g in grouping
                 where g != null
                 select new {g1 = group1.Single(), g2 = g.Single() }).ToList();
                 
// Iterate through the join'ed groups and modify File1                 
foreach (var grouping in matchf1f2)
{
    // Cast the elements of the IGrouping from source to XElement
    XElement g1 = (XElement) grouping.g1;
    XElement g2 = (XElement) grouping.g2;
    // Iterate through nodes of the elements of file2
    // and compare to file1
    foreach (XElement node in g2.Descendants())
    {
        // No need to add or modify these, they are the same
        if( node.Name.ToString().Equals("ID") || node.Name.ToString().Equals("CTRY") 
            || node.Name.ToString().Equals("SN") )
            continue;
        // Find the node in file2 in file1 if it does not exist it will be null
        XElement g1Element = g1.Element(node.Name.ToString());
        if( g1Element != null )
        {
            // Element was found modify the value with value from file1
            g1Element.Value = node.Value;
        }
        else
        {
            // Element not found add it to the file1 element
            g1.Add(node);
        }
    }
}                 

Open in new window

0
vcharlesAuthor Commented:
Hi Fernando,

I will try it and get back to you.

Thank you.

V.
0
vcharlesAuthor Commented:
Hi Fernando,

Just noticed the solution is in C# will try to convert it to VB.NET.

Thanks,

Victor
0
Fernando SotoRetiredCommented:
Hi Victor;

Try this translation of the C# code.

Dim xDocF1 As XDocument = XDocument.Load("C:\Working Directory\VictorFile1.XML")
Dim xDocF2 As XDocument = XDocument.Load("C:\Working Directory\VictorFile2.XML")

Dim nodesf1 = (From node In xDocF1.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value} Into grouping = Group
               Select grouping).ToList()

Dim nodesf2 = (From node In xDocF2.Descendants("Table1")
               Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("CTRY").Value, .SN = node.Element("SN").Value} Into grouping = Group
               Select grouping).ToList()

Dim matchf1f2 = (From group1 In nodesf1
                 Join group2 In nodesf2 On New GroupKey With {.ID = group1(0).Element("ID").Value, .CTRY = group1(0).Element("CTRY").Value, .SN = group1(0).Element("SN").Value} Equals New GroupKey With {.ID = group2(0).Element("ID").Value, .CTRY = group2(0).Element("CTRY").Value, .SN = group2(0).Element("SN").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("ID") Or node.Name.ToString().Equals("CTRY") Or node.Name.ToString().Equals("SN") 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

Public Class GroupKey
    Public Property ID As String
    Public Property CTRY As String
    Public Property SN As String

    Public Overrides Function Equals(obj As Object) As Boolean
        '' Check for null values and compare run-time types.
        If obj Is Nothing Or Not (Me.GetType() = obj.GetType()) Then Return False

        '' Cast obj to GroupKey
        Dim gp As GroupKey = CType(obj, GroupKey)
        '' Check to see if all three values are equal
        Return (ID = gp.ID) And (CTRY = gp.CTRY) And (SN = gp.SN)
    End Function

    Public Overrides Function GetHashCode() As Integer
        Return ID.GetHashCode()
    End Function

End Class

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Hi Fernando,

How do you create the new xml file with the inputs from file2.

Thanks,

Victor
0
vcharlesAuthor Commented:
Hi,

I am using the code below, the only difference is the following field change (Table1 - AOP40 and ctry to COUNTRY.
    Private Sub Button23_Click(sender As System.Object, e As System.EventArgs) Handles Button23.Click


        Dim xDocF1 As XDocument = XDocument.Load(Application.StartupPath + "\AOP40XYZ.xml")
        Dim xDocF2 As XDocument = XDocument.Load(Application.StartupPath + "\AOP40XYZA.xml")



        Dim nodesf1 = (From node In xDocF1.Descendants("AOP40")
                   Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("COUNTRY").Value, .SN = node.Element("SN").Value} Into grouping = Group
                   Select grouping).ToList()

        Dim nodesf2 = (From node In xDocF2.Descendants("AOP40")
                   Group By Key = New GroupKey With {.ID = node.Element("ID").Value, .CTRY = node.Element("COUNTRY").Value, .SN = node.Element("SN").Value} Into grouping = Group
                   Select grouping).ToList()

        Dim matchf1f2 = (From group1 In nodesf1
                         Join group2 In nodesf2 On New GroupKey With {.ID = group1(0).Element("ID").Value, .CTRY = group1(0).Element("COUNTRY").Value, .SN = group1(0).Element("SN").Value} Equals New GroupKey With {.ID = group2(0).Element("ID").Value, .CTRY = group2(0).Element("COUNTRY").Value, .SN = group2(0).Element("SN").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("ID") Or node.Name.ToString().Equals("COUNTRY") Or node.Name.ToString().Equals("SN") 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

    
    End Sub
End Class
Public Class GroupKey
    Public Property ID As String
    Public Property CTRY As String
    Public Property SN As String

    Public Overrides Function Equals(obj As Object) As Boolean
        '' Check for null values and compare run-time types.
        If obj Is Nothing Or Not (Me.GetType() = obj.GetType()) Then Return False

        '' Cast obj to GroupKey
        Dim gp As GroupKey = CType(obj, GroupKey)
        '' Check to see if all three values are equal
        Return (ID = gp.ID) And (CTRY = gp.CTRY) And (SN = gp.SN)
    End Function

    Public Overrides Function GetHashCode() As Integer
        Return ID.GetHashCode()
    End Function

End Class

Open in new window

0
Fernando SotoRetiredCommented:
Hi Victor;

To your question, "How do you create the new xml file with the inputs from file2." the variable xDocF1 after the code runs will be updated with all the changes. Sp if you wish to save it just do xDocF1.Save("Location to save to").

To your question, "the only difference is the following field change (Table1 - AOP40 and ctry to COUNTRY.", then all you need to do is change all occurrences of Table1 to AOP40 and all ctry to COUNTRY. That is it.
0
vcharlesAuthor Commented:
Hi Fernando,

It works Perfectly.

Thank you.

Victor
0
vcharlesAuthor Commented:
Thank You.
0
Fernando SotoRetiredCommented:
Not a problem Victor, glad to help.
0
vcharlesAuthor Commented:
Hi Fernando,
I ran into an unexpected scenario. How do you modify the solution to match both files by first 3 characters instead of exact values?

Thanks,

Victor
0
Fernando SotoRetiredCommented:
Please post this as a new question and give an example  of what you need to match.

Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.