Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 79
  • Last Modified:

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

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
vcharles
Asked:
vcharles
  • 8
  • 7
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
vcharlesAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Can you give the XML , I shall code for you ?
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
vcharlesAuthor Commented:
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
 
vcharlesAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Okies, Working on the same !!
0
 
vcharlesAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
Try .


Dim sn1t As String = sn1.InnerText 
If (sn1A.Equals("")) Then
//code if verfied
End If
0
 
vcharlesAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
Yes..

Yes create a string variable.

String a = "AGD1"

and use that variable.

ric4 = x.AppendChild(x1.CreateElement(a))
0
 
Pawan KumarDatabase ExpertCommented:
@Author - Do you need more help on this ?
0
 
vcharlesAuthor Commented:
I need help to improve the code but will close this case since it is a new topic
0
 
vcharlesAuthor Commented:
Thank you.
0
 
Pawan KumarDatabase ExpertCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 8
  • 7
Tackle projects and never again get stuck behind a technical roadblock.
Join Now