Solved

Help with copying data from file2 to file1 based on 2 identical fields

Posted on 2016-10-02
15
51 Views
Last Modified: 2016-10-03
Hi,

I'm trying the code below to copy values of two fields (Remarks, AGD1) from file2 if both SCOUNTRY and COUNTRY fields in both files have the same values, but received the following error:

'//AOP6B[SCOUNTRY='ITA 10411' AND COUNTRY='ITA']' has an invalid token.

On line:

 Dim y As XmlElement = x2.SelectSingleNode("//AOP6B[SCOUNTRY='" & sn1t & "', COUNTRY='" & sn1tA & "']")

How do I fix this error?

Code:


 Private Sub Button17_Click(sender As System.Object, e As System.EventArgs) Handles Button17.Click
        Dim x1 As New XmlDocument, x2 As New XmlDocument, x3 As New XmlDocument
        x1.Load("fle1.xml")
        x2.Load("file2.xml")
        Dim x As XmlElement = x1.DocumentElement.FirstChild
        While x IsNot Nothing ' loop over all Table1 nodes
            ' check if SN node exists under current Table1 node
            Dim sn1 As XmlElement = x.SelectSingleNode("SCOUNTRY")
            Dim sn1A As XmlElement = x.SelectSingleNode("COUNTRY")
            If sn1 IsNot Nothing Then
                ' check if there is text in the SN node
                Dim sn1t As String = sn1.InnerText
                Dim sn1tA As String = sn1A.InnerText
                If Not String.IsNullOrWhiteSpace(sn1t) Then
                    ' check if NSC node exists under current Table1 node
                    Dim nsc1 As XmlElement = x.SelectSingleNode("REMARKS")
                    If nsc1 IsNot Nothing Then
                        ' check if same SN exists in File2
                        Dim y As XmlElement = x2.SelectSingleNode("//AOP6B[SCOUNTRY='" & sn1t & "', COUNTRY='" & sn1tA & "']")
                        If y IsNot Nothing Then
                            ' check if there is a new value present
                            Dim ric2 As XmlElement = y.SelectSingleNode("REMARKS")
                            If ric2 IsNot Nothing Then
                                ' check if RIC node exists under current Table1 node
                                Dim ric1 As XmlElement = x.SelectSingleNode("REMARKS")
                                If ric1 Is Nothing Then ' create it
                                    ric1 = x.AppendChild(x1.CreateElement("REMARKS"))
                                End If
                                ric1.InnerText = ric2.InnerText
                            End If
                            Dim ric3 As XmlElement = y.SelectSingleNode("AGD1")
                            If ric3 IsNot Nothing Then
                                ' check if RIC node exists under current Table1 node
                                Dim ric4 As XmlElement = x.SelectSingleNode("AGD1")
                                If ric4 Is Nothing Then ' create it
                                    ric4 = x.AppendChild(x1.CreateElement("AGD1"))
                                End If
                                ric4.InnerText = ric3.InnerText
                            End If
                        End If
                    End If
                End If
            End If
            x = x.NextSibling
        End While

        x1.Save("file3.XML")
    End Sub
0
Comment
Question by:vcharles
  • 8
  • 7
15 Comments
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41825931
If you could provide the XML it would have been great !

Query throwing error should be like dis:

Dim y As XmlElement = x2.SelectSingleNode("//AOP6B[SCOUNTRY='" & sn1t & "' and COUNTRY='" & sn1tA & "']")  

Finding:
Error was because of using comma instead of and

XMl file – 2 must have tags with inputs as following in order to run with the above query:

<AOP6B type="debit" SCOUNTRY="ITA 10411" COUNTRY="ITA">
  </AOP6B>
0
 

Author Comment

by:vcharles
ID: 41826096
Hi,

I don't get a syntax error but the Query does not work. I think it's not possible to use a query with two fields with SelectSingleNode.

Thanks,

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826101
Can you give the XML , I shall code for you ?
0
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 

Author Comment

by:vcharles
ID: 41826155
Hi,

Below is an example of the xml files. I need to copy data from file2  to file1 where the field names are the same and values in SN and SCOUNTRY are identical and copy the results in File3.xml


File1.xml
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <table1>
    <SCOUNTRY>ITA  10421</SCOUNTRY>
    <SN>ITA</SN>
    <AGD1></AGD1>
    <REMARKS></REMARKS>
    <NID>CombinedPDF</NID>
  </table1>

File2.xml
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <table2>
    <SCOUNTRY>ITA 10411</SCOUNTRY>
    <SN>10411</SN>
    <FIF>N/A</FIF>
     <REMARKS>TESTA</REMARKS>
    <AGD1>TESTB</AGD1>
  </table2>

Results:
File3.xml
<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <table1>
    <SCOUNTRY>ITA  10421</SCOUNTRY>
    <SN>10411</SN>
    <AGD1>TESTB</AGD1>
    <REMARKS>TESTA</REMARKS>
    <NID>CombinedPDF</NID>
  </table1>
0
 

Author Comment

by:vcharles
ID: 41826163
Correction on File1.xml and File3.xml

File1.xml
 <?xml version="1.0" encoding="utf-8" standalone="yes"?>
 <NewDataSet>
   <table1>
     <SCOUNTRY>ITA  10411</SCOUNTRY>
     <SN>10411</SN>
     <AGD1></AGD1>
     <REMARKS></REMARKS>
     <NID>CombinedPDF</NID>
   </table1>

File3.xml
 <?xml version="1.0" encoding="utf-8" standalone="yes"?>
 <NewDataSet>
   <table1>
     <SCOUNTRY>ITA  10411</SCOUNTRY>
     <SN>10411</SN>
     <AGD1>TESTB</AGD1>
     <REMARKS>TESTA</REMARKS>
     <NID>CombinedPDF</NID>
   </table1>
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826164
Okies, Working on the same !!
0
 

Author Comment

by:vcharles
ID: 41826184
Hi,

The following code seems to work. but how do I modify it to copy data from file2 to file1 only if the matching fields in File1 are blank? Also is there a way to avoid hard coding the field names to copy to File1? This can simplify the code incase I need to copy more data from more fields in File2.

 Private Sub Button17_Click(sender As System.Object, e As System.EventArgs) Handles Button17.Click
        Dim x1 As New XmlDocument, x2 As New XmlDocument, x3 As New XmlDocument
        x1.Load("File1.xml")
        x2.Load("File2.xml")
        Dim x As XmlElement = x1.DocumentElement.FirstChild
        While x IsNot Nothing ' loop over all Table1 nodes
            ' check if SN node exists under current Table1 node
            Dim sn1 As XmlElement = x.SelectSingleNode("SCOUNTRY")
            Dim sn1A As XmlElement = x.SelectSingleNode("SN")
            If sn1 IsNot Nothing Then
                ' check if there is text in the SN node
                Dim sn1t As String = sn1.InnerText
                Dim sn1tA As String = sn1A.InnerText
                If Not String.IsNullOrWhiteSpace(sn1t) Then
                    ' check if NSC node exists under current Table1 node
                    Dim nsc1 As XmlElement = x.SelectSingleNode("REMARKS")
                    If nsc1 IsNot Nothing Then
                        ' check if same SN exists in File2
                        ' Dim y As XmlElement = x2.SelectSingleNode("//AOP6B[SCOUNTRY='" & sn1t & "']")

                        Dim y As XmlElement = x2.SelectSingleNode("//AOP6B[SCOUNTRY='" & sn1t & "' and SN ='" & sn1tA & "']")
                        If y IsNot Nothing Then
                            ' check if there is a new value present
                            Dim ric2 As XmlElement = y.SelectSingleNode("REMARKS")
                            If ric2 IsNot Nothing Then
                                ' check if RIC node exists under current Table1 node
                                Dim ric1 As XmlElement = x.SelectSingleNode("REMARKS")
                                If ric1 Is Nothing Then ' create it
                                    ric1 = x.AppendChild(x1.CreateElement("REMARKS"))
                                End If
                                ric1.InnerText = ric2.InnerText
                            End If
                            Dim ric3 As XmlElement = y.SelectSingleNode("AGD1")
                            If ric3 IsNot Nothing Then
                                ' check if RIC node exists under current Table1 node
                                Dim ric4 As XmlElement = x.SelectSingleNode("AGD1")
                                If ric4 Is Nothing Then ' create it
                                    ric4 = x.AppendChild(x1.CreateElement("AGD1"))
                                End If
                                ric4.InnerText = ric3.InnerText
                            End If
                        End If
                    End If
                End If
            End If
            x = x.NextSibling
        End While

        x1.Save("File3.xml")
0
 
LVL 28

Accepted Solution

by:
Pawan Kumar earned 500 total points
ID: 41826188
Below were the errors..

<?xml version="1.0" encoding="utf-8" standalone="yes"?>
<NewDataSet>
  <table1>
    <SCOUNTRY>ITA  10411</SCOUNTRY>
   <COUNTRY>ITA</COUNTRY>
    <SN>10411</SN>
    <AGD1>TESTB</AGD1>
    <REMARKS>TESTA</REMARKS>
    <NID>CombinedPDF</NID>
  </table1>
</NewDataSet>


Dim y As XmlElement = x2.SelectSingleNode("//table1[SCOUNTRY='" & sn1t & "'and COUNTRY='" & sn1tA & "']")
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826199
Try .


Dim sn1t As String = sn1.InnerText 
If (sn1A.Equals("")) Then
//code if verfied
End If
0
 

Author Comment

by:vcharles
ID: 41826218
Hi,

It works with

 If ric1.InnerText = "" Then
  ric1.InnerText = ric2.InnerText
  End If

Is there a way to avoid hard coding the field names? (i.e. AGD1, REMARKS)

Thanks,

Victor
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826244
Yes..

Yes create a string variable.

String a = "AGD1"

and use that variable.

ric4 = x.AppendChild(x1.CreateElement(a))
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826256
@Author - Do you need more help on this ?
0
 

Author Comment

by:vcharles
ID: 41826269
I need help to improve the code but will close this case since it is a new topic
0
 

Author Closing Comment

by:vcharles
ID: 41826272
Thank you.
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41826273
Yes Please open a new question. Please mark one answer as accepted answer, if it helps you.

I shall look into your new question.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

In my previous article (http://www.experts-exchange.com/Programming/Languages/.NET/.NET_Framework_3.x/A_4362-Serialization-in-NET-1.html) we saw the basics of serialization and how types/objects can be serialized to Binary format. In this blog we wi…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

808 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