[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2016-10-02
15
Medium Priority
?
67 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 37

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 37

Expert Comment

by:Pawan Kumar
ID: 41826101
Can you give the XML , I shall code for you ?
0
Independent Software Vendors: 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!

 

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 37

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 37

Accepted Solution

by:
Pawan Kumar earned 2000 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 37

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 37

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 37

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 37

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

Industry Leaders: 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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
In real business world data are crucial and sometimes data are shared among different information systems. Hence, an agreeable file transfer protocol need to be established.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses
Course of the Month19 days, 9 hours left to enroll

873 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