[RESOLVED] Read write xml

[lag22]

I am new to vb.net and would like some help.

I have a folder called movies. In this folder is a collection of different movies each in their own folder with their own xml file called movie.xml.

I would like to extract lets say the localtitle and aspectratio from each movie xml and send to Excel. What code would I need to achieve this?

So far I have the attached code which reads only one file and it is directly from the xml outside of a folder.

Samples of code and xml files attached. Thanks for any help.

xmlMovie.txt

movie.xml sample.txt

[.paul.]

what is the structure of your movies folder?
am i right in assuming that it has a subfolder for each movie + each subfolder has an xml file called movie.xml?

you want to extract localtitle and aspectratio from each of those subfolders + output to excel?

[lag22]

Yes you are correct.

[.paul.]

try this:

Imports System.Xml
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim outPut As String = ""
        Dim baseFolder As String = "C:\Users\Paul\Desktop\movies"
 
        'read each xml file
        For Each subFolder As String In IO.Directory.GetDirectories(baseFolder)
            Dim xml As New XmlDocument
            xml.Load(IO.Path.Combine(subFolder, "movie.xml"))
 
            'extract values
            outPut &= xml.SelectSingleNode("Title/LocalTitle").InnerText & ","
            outPut &= xml.SelectSingleNode("Title/AspectRatio").InnerText & Environment.NewLine
        Next
 
        'save outPut as excel CSV
        IO.File.WriteAllText("outPut.csv", outPut)
    End Sub
 
End Class

[lag22]

Thanks for your quick response. Will try and let you know.

[lag22]

I am getting the following:
File not foundexception was unhandled
Question; Can the output be shown on the form and then by another button saved?

[.paul.]

what are the names of the xml files? you told me they were all called movie.xml

[.paul.]

you can put the string variable outPut in a textbox:

textbox1.text = outPut

[lag22]

what are the names of the xml files? you told me they were all called movie.xml

Yes. They all have the same name: movie.xml

[.paul.]

there must be a sub folder in your base folder, that doesn't contain an xml file

[lag22]

there must be a sub folder in your base folder, that doesn't contain an xml file

I am using a test folder with only three sub folders. I will check the format of the xml files. In the interim, can you indicate which line to place the TextBox output? Am I correct that the form will have a text box and two buttons, one to start the action and another to save the results?

Thanks for your help.

[.paul.]

ok. try this:

Imports System.Xml
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim outPut As String = ""
        Dim baseFolder As String = "C:\Users\Paul\Desktop\movies"
 
        'read each xml file
        For Each subFolder As String In IO.Directory.GetDirectories(baseFolder)
 
            dim xmlFileName as string = IO.Path.Combine(subFolder, "movie.xml")
 
            if io.file.exists(xmlFileName) then    
                Dim xml As New XmlDocument
                xml.Load()
 
                'extract values
                outPut &= xml.SelectSingleNode("Title/LocalTitle").InnerText & ","
                outPut &= xml.SelectSingleNode("Title/AspectRatio").InnerText & Environment.NewLine
            end if
        Next
 
        'save outPut as excel CSV
        'IO.File.WriteAllText("outPut.csv", outPut)
        textbox1.text = outPut
    End Sub
 
End Class

[.paul.]

yes, you could use 2 buttons + a textbox.

are you sure the xml files are literally called "movie.xml"?

[lag22]

I am using a test folder with only three sub folders. I will check the format of the xml files. In the interim, can you indicate which line to place the TextBox output? Am I correct that the form will have a text box and two buttons, one to start the action and another to save the results?

Thanks for your help.

I deleted the folder that was the exception and it worked.

Ok. I think I am almost there. Thanks for your patience.

The info appears in the textbox but jumbled. Is there a way to place LocalTitle in one column and AspectRation in another?

Also how can I save? Do I need a save button?

[lag22]

yes, you could use 2 buttons + a textbox.

are you sure the xml files are literally called "movie.xml"?

Yes. The name of the xml file is: movie.xml

[.paul.]

you could use a listview (readonly). add a listview + 2 buttons to your form:

Imports System.Xml
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim baseFolder As String = "C:\Users\Paul\Desktop\movies"
 
        'read each xml file
        For Each subFolder As String In IO.Directory.GetDirectories(baseFolder)
            Dim xml As New XmlDocument
            xml.Load(IO.Path.Combine(subFolder, "movie.xml"))
 
            'extract values + add to listview
            Dim lvi As New ListViewItem(xml.SelectSingleNode("Title/LocalTitle").InnerText)
            lvi.SubItems.Add(xml.SelectSingleNode("Title/AspectRatio").InnerText)
            ListView1.Items.Add(lvi)
 
        Next
 
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ListView1.View = View.Details
        ListView1.Columns.Add("Title")
        ListView1.Columns.Add("AspectRatio")
        ListView1.Columns(0).Width = 150
        ListView1.Columns(1).Width = 150
    End Sub
 
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'save to csv
        Dim outPut As String = ""
 
        For Each lvi As ListViewItem In ListView1.Items
            outPut &= String.Join(",", lvi.SubItems.Cast(Of ListViewItem.ListViewSubItem).Select(Function(si) si.Text).ToArray) & Environment.NewLine
        Next
 
        'save outPut as excel CSV
        IO.File.WriteAllText("outPut.csv", outPut)
        Process.Start("outPut.csv")
    End Sub
End Class

[lag22]

you could use a listview (readonly). add a listview + 2 buttons to your form:

Imports System.Xml
 
Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim baseFolder As String = "C:\Users\Paul\Desktop\movies"
 
        'read each xml file
        For Each subFolder As String In IO.Directory.GetDirectories(baseFolder)
            Dim xml As New XmlDocument
            xml.Load(IO.Path.Combine(subFolder, "movie.xml"))
 
 
 
When I click Button1, nothing appears in List
 
            'extract values + add to listview
            Dim lvi As New ListViewItem(xml.SelectSingleNode("Title/LocalTitle").InnerText)
            lvi.SubItems.Add(xml.SelectSingleNode("Title/AspectRatio").InnerText)
            ListView1.Items.Add(lvi)
 
        Next
 
    End Sub
 
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        ListView1.View = View.Details
        ListView1.Columns.Add("Title")
        ListView1.Columns.Add("AspectRatio")
        ListView1.Columns(0).Width = 150
        ListView1.Columns(1).Width = 150
    End Sub
 
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        'save to csv
        Dim outPut As String = ""
 
        For Each lvi As ListViewItem In ListView1.Items
            outPut &= String.Join(",", lvi.SubItems.Cast(Of ListViewItem.ListViewSubItem).Select(Function(si) si.Text).ToArray) & Environment.NewLine
        Next
 
        'save outPut as excel CSV
        IO.File.WriteAllText("outPut.csv", outPut)
        Process.Start("outPut.csv")
    End Sub
End Class

1.When I click Button1, nothing appears in ListView1.

2.Should a save as box appear after clicking Button2 to indicate where to save?

[.paul.]

1\

in your design time view, doubleclick button1.
if it doesn't take you to the handler you pasted, delete the new handler it's created + doubleclick button1 again until it takes you to the handler you pasted

2\ no it's hardcoded. here's how to add + use a savefiledialog:

http://www.scproject.biz/Using%20Dialogs.htm#bm3

[lag22]

This is what appears in the listView. There are 2 sub folders. The localtitles appear but not the aspectratios. Saving shows all. Is this correct?


http://dl.dropbox.com/u/19298307/xml.JPG

[.paul.]

it worked when i wrote it + tested it.
can you post your code that isn't working?

[lag22]

Here is the code;

code.txt

[.paul.]

you're missing a handles clause:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) handles mybase.load

[lag22]

you're missing a handles clause:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) handles mybase.load

Thanks very much for all your help. I am beginning to understand a little. Here is the latest:

[.paul.]

it should be working now. your picture didn't upload + xmlMovie.txt is your original code from your original post

[lag22]

Sorry.Something went wrong with the attachment;

http://dl.dropbox.com/u/19298307/xml.JPG

[.paul.]

so it's working then?

[lag22]

It's working. However, this is what the the file looks like when saved. "The" from the name of the movie appears in the column with the aspectratio and then pushes thos ratios over to the next column.

http://dl.dropbox.com/u/19298307/File.JPG

[.paul.]

let me guess. the line in question is:

Adjustment Bureau, The

right?

csv is comma separated values + any commas are treated as new columns.
you need to remove the comma or replace the comma with a different character

[lag22]

Thanks. That worked.

[lag22]

I really appreciated your help and hope I am not overdoing it by seeking it again.

I have tweaked the code and almost have it where I want it. Presently there are 2 buttons. Button1 and button2. Button1 will browse for a folder and place that folder in a textbox. Button2 will select data from folder in textbox.

Button1 has to be clicked first. If button2 is clicked before button1 then there is an error.

What I wish to achieve if button2 is clicked first:
MessageBox to indicate that Button1 should be clicked first. However on closing MessageBox, I would like the form to remain open so I can then click button1.

Thanks for any help.

[.paul.]

you could use something like this:

Public Class Form1
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'browse for folder
        Dim fbd As New FolderBrowserDialog
        If fbd.ShowDialog = Windows.Forms.DialogResult.OK Then
            TextBox1.Text = fbd.SelectedPath
        End If
    End Sub
 
    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        If IO.Directory.Exists(TextBox1.Text) Then
            'button1 has already been clicked
            'your code here
        Else
            MessageBox.Show("Please select a folder first", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1, MessageBoxOptions.DefaultDesktopOnly, False)
        End If
    End Sub
 
End Class

[lag22]

Thanks. However when I click in the messagebox, it closes the form. Is there a way to close the messagebox but keep the form open? That way it would not be necessay to reopen the form to select folder.

[.paul.]

have you set the dialogresult property for the button?

you need to give more information. post your code...

[lag22]

What should the dialogresult property be set as?

[.paul.]

it shouldn't be set.

[lag22]

Thanks. I got things to work. Late now. Will test further tomorrow. Adjusted the code and should be ok.
By the way, do you have any suggestions for good reading so I can improve my knowledge.

[lag22]

All works well. Thanks.