How to extract certain data patterns from textbox

method · April 25th, 2006, 06:01 PM

Hi all .i got the following inside a textbox inside my vb6 form. I am looking for a way to ripp the song path and title from it and use them and insert them to mysql datbase. But i do not know how to ripp those part
i be happy that an expert tell me how i can do it using vb6. The number of song path are not know in before and the parts
that i want to write to mysql are shown in bold.Thanks

data inside my texbox:

Code:

<?xml version="1.0" encoding="UTF-8" ?> 
- <player showDisplay="yes" showPlaylist="yes" autoStart="yes">
  <song path="http://www.mysite.com/TtdlI3l1lIl0OO...bum1/song1.mp3" title="song title" /> 
  <song path="http://www.mysite.com/TtdlI3l1lIl0OO.../songname2.mp3" title="song title 2" /> 

  </player>

Raghunathan · April 26th, 2006, 11:56 AM

hi,

Since the data displayed in the text box is in a well-formed xml format, easiest way to get the path and title attributes is loading the xml text in DOMDocument and getting the value.

Here is the code.
To test this code, Create a VB Form, add a Multi-Line Textbox, 2 ListBoxes and a Command Button (don't change the names of the controls).

Then, Goto Project menu ---> References and select Micrsoft XML 4.0 from the list(which will be available in all systems by default)

then paste the code in the click event of the command button and paste your xml text inside the textbox and run the form and click the button.

you'll the url of the mp3 and title of the song in the both listboxes.

any further queries or updations, mail me.

--------------- Code Listings --------------------------------------
Dim xmlDoc As MSXML2.DOMDocument40
Dim songNode As IXMLDOMNode, songNodeList As IXMLDOMNodeList

'Loading all the text in to a xml dom document
Set xmlDoc = New DOMDocument40
xmlDoc.loadXML Trim(Text1.Text)

'Selecting all the Song nodes from the XML Text in the TextBox
Set songNodeList = xmlDoc.selectNodes("//song")

For Each songNode In songNodeList
    'Getting the Path
    List1.AddItem songNode.Attributes.getNamedItem("path").Text
    'Getting the Title
    List2.AddItem songNode.Attributes.getNamedItem("title").Text
Next

Set xmlDoc = Nothing

-------------------------- End of Coding -------------------------

Regards,
Raghu

method · April 26th, 2006, 02:47 PM

Many Many thanks for u nice explanation. Could u tell me beside showing it to listboxes how i can insert each song path and song title in to mysql db. i know how to connect to mysql db but do not know how to construct the sql isert statment that holds song path and id for each song.

Furthermorw, how i can read song path and title from text file which has the following format and put it in listview for further manipulation :

Code:

<?xml version="1.0" encoding="UTF-8" ?> 
<player showDisplay="yes" showPlaylist="yes" autoStart="yes">
Ã Ã <song path="http://www.mysite.com/TtdlI3l1lIl0OOO0a/singer1/album1/song1.mp3" title="song title" /> 
Ã Ã <song path="http://www.mysite.com/TtdlI3l1lIl0OOO0a/singer1/album1/songname2.mp3" title="song title 2" /> 


<?xml version="1.0" encoding="UTF-8" ?> 
- <player showDisplay="yes" showPlaylist="yes" autoStart="yes">
Ã Ã <song path="http://www.mysite.com/TtdlI3l1lIl0OOO0a/singer2/album1/song4.mp3" title="song title3" /> 
Ã Ã <song path="http://www.mysite.com/TtdlI3l1lIl0OOO0a/singer2/album1/songname8.mp3" title="song title 4" /> 
Ã Ã 
Ã Ã </player>

.....

Thanks and looking forward to your reply.
Ã Ã
Ã Ã </player>

Raghunathan · April 27th, 2006, 09:22 AM

hi,

i've modified previously given code in such a way that it generates a INSERT SQL Statement with a id and song path. Since i don't have an idea about MYSQL Database, i've given a ANSI SQL Insert statement. this code will add the insert statement to the listbox.

For accessing the data from the file, the same code will work with slight modifications.

1) Save the File with the extenstion .xml
2) In the following code change this line
           xmlDoc.loadXML Text1.text
   to
           xmlDoc.load "C:\songs.xml" 'Path the xml file

rest of the code remains the same.

Private Sub Command1_Click()
Dim xmlDoc As MSXML2.DOMDocument40
Dim songNode As IXMLDOMNode, songNodes As IXMLDOMNodeList
Dim strQuery As String
Static idCtr As Integer

Set xmlDoc = New MSXML2.DOMDocument40
xmlDoc.loadXML Text1

Set songNodes = xmlDoc.selectNodes("//song")

idCtr = 1
For Each songNode In songNodes
    strQuery = "INSERT INTO SONGTABLE (ID,SONG) VALUES (" & idCtr & ",'" & songNode.Attributes.getNamedItem("path").Text & "')"
    idCtr = idCtr + 1
    List1.AddItem strQuery
Next

Set xmlDoc = Nothing

End Sub

hope this would help u solve the problems.
kindly revert back for any more queries or modifications

Thanks

Regards,
Raghu

method · May 3rd, 2006, 02:54 AM

Many thanks to your previouse replies. I have simmiler questin about diffrent data pattern that i html not xml an i would be happy if u help me achive this data extraction from html .

I got a html code that i used inet.openURL to put it in textbox. Now i want to extract Artistname , albumname,songname,artistpic from it. The code already extracts song ids but i want to output songname and other informatin with it. I be happy if an expert show me an easy way to extract those data.Thanks

Note: in one page there is one album for single artist but mutliple songnames
Note: the bold parts are dynamaic and changing and i want extract them

These blocks of html are amoung othe codes that i removed them in mypost
html part that holds each song name:

Code:

<img border="0" src="../images/download.gif" width="16" height="16" longdesc="Download songname" alt="Download songname"></a>
                            &nbsp;songname
                            Ã Ã Ã </td>

Html part that holds artist name and album

Code:

::: Singer: <b>artistname</b> Album <b>albumename</b>::::</td>

Html part tha holdes artist image

Code:

<td>
                Ã Ã <br>
                Ã Ã <a href="http://localhost/ShowImage.asp?img=http://localhost/artistpic.jpg" target=_blank>
                Ã Ã <img border="0" src="../CdImages/artistpic.jpg" width="180" height="180" longdesc="Click here to Enlarge" alt="Click here to Enlarge" >
                Ã Ã </a>
                </td>

Code:

Private Sub Command1_Click(Index As Integer)

Select Case Index
    Case 0:
        If txtURL.Text <> "" Then
            RichTextBox1.Text = Inet1.OpenURL(txtURL.Text, icString)
        End If

    Case 1:
        End
End Select
End Sub


Private Sub Command2_Click()
Ã Dim sResult() As String, n As Long


            If GetLine(RichTextBox1.Text, "../player/player.asp?id=", "')", sResult)  Then
    Ã For n = LBound(sResult) To UBound(sResult)
        Ã  List1.AddItem sResult(n)        Ã 
        Next n

Else
        ' No occurances were found
    End If
End Sub




Private Function GetLine(ByVal sText As String, ByVal sStart As String, ByVal sEnd As String, ByRef sArr() As String) As Boolean
    Dim lPos As Long, lEnd As Long, lCount As Long, sTemp() As String

    ReDim sTemp(100)

    lPos = InStr(1, sText, sStart, vbTextCompare)
    Do While lPos
        lEnd = InStr(lPos, sText, sEnd, vbTextCompare)
        If lEnd Then
    Ã 
            sTemp(lCount) = Mid$(sText, lPos, lEnd - lPos)
            lPos = InStr(lEnd, sText, sStart, vbTextCompare)
        Else
            sTemp(lCount) = Mid$(sText, lPos)
            lPos = 0
        End If
        lCount = lCount + 1
        If lCount > UBound(sTemp) Then ReDim Preserve sTemp(100 + lCount)
    Loop

    If lCount > 0 Then
        ReDim Preserve sTemp(lCount - 1)
        sArr = sTemp
    End If
    GetLine = lCount
End Function