Solved

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

Posted on 2016-10-02
15
57 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
15 Comments
 
LVL 29

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 29

Expert Comment

by:Pawan Kumar
ID: 41826101
Can you give the XML , I shall code for you ?
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

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 29

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 29

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 29

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 29

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 29

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 29

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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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