Solved

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

Posted on 2016-10-02
15
47 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 24

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 24

Expert Comment

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

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 24

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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
LVL 24

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 24

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 24

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 24

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 24

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

929 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

7 Experts available now in Live!

Get 1:1 Help Now