Help with linking multiple files using VB.NET

Hi,

I am trying the code below to link multiple xml files and create a LinkFinal.xml but the data in LinkFinal only contains data for LinkFinalBEL.xml, any ideas why it does not contain data for the other Link files? I included a message box to ensure the code is executed in each part of the code, therfefore the files do exist in the LinkFiles folder.

fsItem = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkFinalMain.xml", IO.FileMode.Open)
        dtsetItem.Clear()
        dtsetItem.ReadXml(fsItem)
        fsItem.Close()

        Dim DestinationPath As String = IO.Path.Combine(Application.StartupPath + "\LinkFiles\", IO.Path.GetFileName("\LinkFiles\LinkFinalBEL.xml"))
        If IO.File.Exists(DestinationPath) Then
            MsgBox("A")
            fsReceiver = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkFinalBEL.xml", IO.FileMode.Open)
            dtsetReceiver.Clear()
            dtsetReceiver.ReadXml(fsReceiver)
            dtsetItem.Merge(dtsetReceiver, True, MissingSchemaAction.AddWithKey)
        End If
              Dim DestinationPathA As String = IO.Path.Combine(Application.StartupPath + "\LinkFiles\", IO.Path.GetFileName("\LinkFiles\LinkBilateralBEL.xml"))
        If IO.File.Exists(DestinationPathA) Then
            MsgBox("B")
            fsDonor = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkBilateralBEL.xml", IO.FileMode.Open)
            dtsetDonor.Clear()
            dtsetDonor.ReadXml(fsDonor)
            fsDonor.Close()
            dtsetItem.Merge(dtsetDonor, True, MissingSchemaAction.AddWithKey)
        End If
        MsgBox(dtsetItem.Tables(0).Rows.Count)
        Dim DestinationPathB As String = IO.Path.Combine(Application.StartupPath + "\LinkFiles\", IO.Path.GetFileName("\LinkFiles\LinkFCDBEL.xml"))
        If IO.File.Exists(DestinationPathB) Then
            MsgBox("C")
            fsDonor = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkFCDBEL.xml", IO.FileMode.Open)
            dtsetDonor.Clear()
            dtsetDonor.ReadXml(fsDonor)
            fsDonor.Close()
            dtsetItem.Merge(dtsetDonor, True, MissingSchemaAction.AddWithKey)
        End If
        Dim DestinationPathC As String = IO.Path.Combine(Application.StartupPath + "\LinkFiles\", IO.Path.GetFileName("\LinkFiles\LinkNABEL.xml"))
        If IO.File.Exists(DestinationPathC) Then
            MsgBox("D")
            fsDonor = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkNABEL.xml", IO.FileMode.Open)
            dtsetDonor.Clear()
            dtsetDonor.ReadXml(fsDonor)
            fsDonor.Close()
            dtsetItem.Merge(dtsetDonor, True, MissingSchemaAction.AddWithKey)
        End If
        dtsetItem.WriteXml(Application.StartupPath & "\LinkFiles\LinkFinal.xml")
vcharlesAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vcharlesAuthor Commented:
Hi,

After the following code:

  dtsetItem.Merge(dtsetReceiver, True, MissingSchemaAction.AddWithKey)

How do you see the number of records in dtsetItem?

Victor
0
Robert SchuttSoftware EngineerCommented:
I tested your code and it works for me. The question in your follow-up post is already answered by that other MsgBox present in your initial post:
MsgBox(dtsetItem.Tables(0).Rows.Count)

Open in new window

For debugging purposes, you could repeat that line after every merge.

If you see all MsgBox'es (A thru D) then it would seem most logical that the last line is not succeeding, leaving the current contents of the file as they are maybe?

It could also be a problem if the layout of the files are different. Maybe you can post some data files?

One other important thing to check is that you have write permission to the folder and file (or rather, the account running the process that is executing this code, if applicable) and the file is not marked read-only.

To check whether any errors occur, use Try/Catch, also to avoid having to make changes 4 times, use a sub. Here's a new version where I cleaned up a bit (see the comments):
Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        MergeXml()
    End Sub

    Private Sub MergeXml()
        Dim dtsetItem As New DataSet
        Dim fsItem As System.IO.FileStream
        Try
            ' the idea of Path.Combine is that you don't have to mess around with all those backslashes
            fsItem = New System.IO.FileStream(IO.Path.Combine(Application.StartupPath, "LinkFiles", "LinkFinalMain.xml"), IO.FileMode.Open)
            dtsetItem.Clear()
            dtsetItem.ReadXml(fsItem)
            fsItem.Close()
            MsgBox("initial #records = " & dtsetItem.Tables(0).Rows.Count)
            dtsetItem.Merge(ReadXmlFile(dtsetItem, "LinkFinalBEL.xml"), True, MissingSchemaAction.AddWithKey)
            MsgBox("A: #records = " & dtsetItem.Tables(0).Rows.Count)
            dtsetItem.Merge(ReadXmlFile(dtsetItem, "LinkBilateralBEL.xml"), True, MissingSchemaAction.AddWithKey)
            MsgBox("B: #records = " & dtsetItem.Tables(0).Rows.Count)
            dtsetItem.Merge(ReadXmlFile(dtsetItem, "LinkFCDBEL.xml"), True, MissingSchemaAction.AddWithKey)
            MsgBox("C: #records = " & dtsetItem.Tables(0).Rows.Count)
            dtsetItem.Merge(ReadXmlFile(dtsetItem, "LinkNABEL.xml"), True, MissingSchemaAction.AddWithKey)
            MsgBox("D: #records = " & dtsetItem.Tables(0).Rows.Count)
            dtsetItem.WriteXml(IO.Path.Combine(Application.StartupPath, "LinkFiles", "LinkFinal.xml"))
        Catch ex As Exception
            MsgBox("Error in 'MergeXml': " & ex.Message)
        End Try
    End Sub

    Private Function ReadXmlFile(ByRef dtsetItem As DataSet, ByVal filnam As String) As DataSet
        Dim TempDtset As New DataSet
        Try
            Dim TempDestinationPath As String = IO.Path.Combine(Application.StartupPath, "LinkFiles", filnam)
            If IO.File.Exists(TempDestinationPath) Then
                ' you've constructed a path, why not use it?
                Using Temp_fs As New System.IO.FileStream(TempDestinationPath, IO.FileMode.Open)
                    TempDtset.ReadXml(Temp_fs)
                End Using
            End If
        Catch ex As Exception
            MsgBox("Error in 'ReadXmlFile': " & ex.Message)
        End Try
        Return TempDtset
    End Function

End Class

Open in new window

0
vcharlesAuthor Commented:
MsgBox(dtsetItem.Tables(0).Rows.Count) gives me  error message 'Can not find table 0', any ideas why I receive this error message?
Thanks.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Robert SchuttSoftware EngineerCommented:
That would indicate the load has not succeeded. I get correct values after loading a simple xml file (with schema) like:
<?xml version="1.0" encoding="utf-8" ?>
<records>
  <xs:schema id="records" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">
    <xs:element name="records" msdata:IsDataSet="true" msdata:MainDataTable="record" msdata:UseCurrentLocale="true">
      <xs:complexType>
        <xs:choice minOccurs="0" maxOccurs="unbounded">
          <xs:element name="record">
            <xs:complexType>
              <xs:sequence>
                <xs:element name="id" type="xs:string" minOccurs="0" />
                <xs:element name="name" type="xs:string" minOccurs="0" />
              </xs:sequence>
            </xs:complexType>
          </xs:element>
        </xs:choice>
      </xs:complexType>
    </xs:element>
  </xs:schema>
  <record>
    <id>1</id>
    <name>name1</name>
  </record>
</records>

Open in new window

0
vcharlesAuthor Commented:
I don't have a schema for my xml files. How do I create the schema?
0
Robert SchuttSoftware EngineerCommented:
That's a subject on its own. It may not be needed for your situation. Let me remind you that you have not shared the definition of your variables and any of your files. Could you do that or do they contain sensitive information?
0
Robert SchuttSoftware EngineerCommented:
By the way, if your xml files look like the example I posted, you can just copy the schema and replace the node names like 'records', 'id' and 'name' with yours. If your files contain attributes, you need more effort but probably there is an online tool to create a schema from an example file.
0
vcharlesAuthor Commented:
I  am on the road, will reply later. Thanks.
0
vcharlesAuthor Commented:
Sill need to investigate further because the code worked in another project, only difference is
the link files do not have the same <Row> element. Each are named differently For example
LinkFCDBEL contains <RowA>, LinkNABEL contains <RowB>.Unfortunately I can not send you the xml files.
0
Robert SchuttSoftware EngineerCommented:
When I change the structure of the xml files similar to what you indicated, the merge fails in the sense that a new table is added to dtsetItem.Tables instead of the rows being added to dtsetItem.Tables(0) as before.

There may be a simple solution for this, but I can't find anything yet. What I guess I would normally do in a situation like this, is use xslt to make sure the xml files have the same structure and then merge them.

Correction, this may be simpler: if you look at the code I posted earlier, after line 38 I added
TempDtset.Tables(0).TableName = "record" ' make the same for all

Open in new window

and it seems to work nicely: the output file contains all <record> nodes from all input files.
0
vcharlesAuthor Commented:
Hi,

I purposely renamed the rows because I will need to transfer the data to a data table and search different table rows by using Rowa, RowB and Rowc as different tables. If possible I would like to merge using RowA, Rowb and RowC. Below is the code I will use to search.

Dim linker As XElement = XElement.Load(Application.StartupPath + "\LinkFinal.xml")
  IF X = 0
 For Each item As XElement In linker.Elements("Row")
            Dim linkID As String = item.Element("Link_ID").Value
            Dim NAId As String = item.Element("NA_ID").Value
        Dim NAVal As String = String.Empty
            xe = NA.Elements("Row").Cast(Of XElement)().Where(Function(n) n.Element("NA_ID").Value = NAId).FirstOrDefault()
            If xe IsNot Nothing Then
                NAVal = xe.Element("NA").Value
            End If
  Dim dr As DataRow = dt.NewRow()
            dr("Link_ID") = linkID
            dr("NA") = NAVal
            dt.Rows.Add(dr)
        Next
   Dim bs As New BindingSource()
   Dim DV As New DataView(dt, SearchCriteria, Nothing, DataViewRowState.CurrentRows)
   FilteredDT = DV.ToTable
End IF

IF X = 1

For Each item As XElement In linker.Elements("RowA")
            Dim linkID As String = item.Element("Link_ID").Value
            Dim FCDId As String = item.Element("FCD_ID").Value
        Dim FCDVal As String = String.Empty
            xe = FCD.Elements("Row").Cast(Of XElement)().Where(Function(n) n.Element("FCD_ID").Value = FCDId).FirstOrDefault()
            If xe IsNot Nothing Then
                FCDVal = xe.Element("FCD").Value
            End If
  Dim dr As DataRow = dt.NewRow()
            dr("Link_ID") = linkID
            dr("FCD") = FCDVal
            dt.Rows.Add(dr)
        Next
   Dim bs As New BindingSource()
   Dim DV As New DataView(dt, SearchCriteria, Nothing, DataViewRowState.CurrentRows)
   FilteredDT = DV.ToTable
End If
Where LinkFinal.xml includes Row,ROWA,ROWB etc...
0
Robert SchuttSoftware EngineerCommented:
Ah well, ok actually without that additional line of code I posted earlier, the output of saving the merged recordset was:
<?xml version="1.0" standalone="yes"?>
<records>
  <record>
    <id>1</id>
    <name>name1</name>
  </record>
  <record>
    <id>2</id>
    <name>name2BEL</name>
  </record>
  <record>
    <id>3</id>
    <name>name3BilateralBEL</name>
  </record>
  <recordA>
    <id>4</id>
    <name>name4CDBEL</name>
  </recordA>
  <recordB>
    <id>5</id>
    <name>name5NABEL</name>
  </recordB>
</records>

Open in new window

So the 3 separate tables (record, recordA and recordB) were saved fine into 1 output file.

But putting this all in 1 file and then filtering by type again seems strange to me. If you were to make them all similar and add a field that contains the original type that might make the search code a lot more efficient, but not having too much info on the data is quite a handicap. It seems the fields you're using could give me a clue what to put in the xml but it would be a lot easier if you could post some files (with just some 123 and xxx in it for data).

I'm not well versed in LINQ and that code in your last post seems fabricated (from several sources?) and also missing a lot of variable declarations so I'm not really sure what the purpose of that post is.
0
vcharlesAuthor Commented:
Hi,

I need to take this approach because the RowA, RoWB and RowC contain Identical data elements, if they are the same I would not be able to search for the correct data element. I think the issue is with the schema, will look more into obtaining the schema for each xml file.

Thanks,

Victor
0
vcharlesAuthor Commented:
Hi again,

Just realize the schema is not the solution. I would appreciate if you can help me find a solution.

Thanks,

Victor
0
Robert SchuttSoftware EngineerCommented:
Please try going back to my first post and explain how that does not work for you; since you can't post the actual xml I feel it's up to you to determine why that code is not working for you. I've also posted a number of other things to check to which you haven't responded. If there's something you don't understand or know how to do, don't ignore it but ask for more specific info about that point. Now from my perspective you've got working code and you just say "it doesn't work". If you want help, you need to cooperate. A little more code and declarations could be helpful, the problem could be in a part of the code that you haven't posted. Above all, just a one record made up xml file could explain it all as well. Things you can do for yourself is use the debugger and watches. Here's an example of how the dataset looks for me:
capture
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
vcharlesAuthor Commented:
Hi,

Sorry for the late reply.

It's working when I change Donor to DonorA, DonorB, DonorC for each xml file.

  fsDonor = New System.IO.FileStream(Application.StartupPath + "\LinkFiles\LinkBilateralBEL.xml", IO.FileMode.Open)
            dtsetDonor.Clear()
            dtsetDonor.ReadXml(fsDonor)
            fsDonor.Close()
            dtsetItem.Merge(dtsetDonor, True, MissingSchemaAction.AddWithKey)

Thanks for all your Help.

Victor
0
vcharlesAuthor Commented:
Thank You!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

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.