Extracting Text File data using VB (1 Viewer)

JohnLee

Registered User.
Local time
Today, 06:20
Joined
Mar 8, 2007
Messages
692
Hi,

I have a text file that I want to extract certain bits of data from into a table, however I’m struggling to work out how to write the code that will get the require data from the text file and put it into my table in my database.

My approach based on what I have picked up so far was to extract the data from the text file and place only the required data into another text file and then to import the new text file into my database.

This is what I have so far:

============
BEGIN CODE
============

Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim FS, F, TS, S

'To create a file:
On Error Resume Next

'Set the Create FileSystemObject
Set FS = CreateObject("Scripting.FileSystemObject")
FS.CreateTextFile "T:\newKaroakeList.txt" 'Create the file newKaroakeList.txt

'Read from the file test.txt

Set F = FS.GetFile("T:\KaroakeListingXML.txt")
Set TS = F.OpenAsTextStream(ForReading, TristateUseDefault)
S = TS.ReadLine
TS.Close

'Write to the newKaroakeList text file

Set F = FS.GetFile("T:\newKaroakeList.txt")
Set TS = F.OpenAsTextStream(ForWriting, TristateUseDefault)
TS.WriteLine S
TS.Close

==========
END CODE
==========

The above code gets the first line of my text file and writes it to the new text file which is the following:

<?xml version="1.0" encoding="iso-8859-1" ?>

But this is not the data I want, so I’m struggle to work out how to get the above code to work through each line until it finds the data that I want and to then place it in the new text file, here is a sample of what the data looks like in the target text file. The data highlighted in bold red is the actual data I want:

=====================
<?xml version="1.0" encoding="iso-8859-1" ?>
<smil>
<body>
<seq>
<monfolder src="F:\Karaoke\Artists"/>
<media src="F:\Karaoke\Artists\1 Giant Leap & Maxi Jazz & Robbie Williams\My Culture - 1 Giant Leap, Maxi, Jazz & Robbie Williams.mp3">
<title value="My Culture"/>
<artist value="1 Giant Leap, Maxi, Jazz & Robbie Williams"/>
<album value="EZH11"/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:04:33"/>
<comment value="A10BF50D"/>
<Format value="2"/>
<bitrate value="128"/>
<trackno value="13"/>
<rating value="0"/>
<id value="{B0AC11C6-3C85-4432-A935-A5680095D9D3}"/>
<pos value="1"/>
</media>
<media src="F:\Karaoke\Artists\10 Years After\I&apos;d Love To Change The World - 10 Years After.MP3">
<title value="I&apos;d Love To Change The World"/>
<artist value="10 Years After"/>
<album value="SC8098-02"/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:04:17"/>
<Format value="2"/>
<bitrate value="128"/>
<rating value="0"/>
<id value="{24DE542C-61C2-4B98-B9F3-C9F06C7F8BAD}"/>
<pos value="2"/>
</media>
<media src="F:\Karaoke\Artists\10,000 Maniacs\Because The Night - 10,000 Maniacs.MP3">
<title value="Because The Night"/>
<artist value="10,000 Maniacs"/>
<album value="SC8113-05"/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:04:13"/>
<Format value="2"/>
<bitrate value="128"/>
<rating value="0"/>
<id value="{25AB7FC3-F0C2-411B-BD4E-FF86C37653CF}"/>
<pos value="3"/>
</media>
<media src="F:\Karaoke\Artists\10,000 Maniacs\Candy Everybody Wants - 10,000 Maniacs .mp3">
<title value="Candy Everybody Wants"/>
<artist value="10,000 Maniacs "/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:03:28"/>
<Format value="1"/>
<bitrate value="128"/>
<rating value="0"/>
<id value="{1C91D68D-C7CB-47CE-8860-48BF11F33F6D}"/>
<pos value="4"/>
</media>
<media src="F:\Karaoke\Artists\10,000 Maniacs\Like The Weather - 10,000 Maniacs.MP3">
<title value="Like The Weather"/>
<artist value="10,000 Maniacs"/>
<album value="MM6066-07"/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:04:25"/>
<Format value="2"/>
<bitrate value="128"/>
<rating value="0"/>
<id value="{D753751F-9E6F-4A55-B2B8-0562FA242717}"/>
<pos value="5"/>
</media>
<media src="F:\Karaoke\Artists\10,000 Maniacs\More Than This - 10,000 Maniacs.MP3">
<title value="More Than This"/>
<artist value="10,000 Maniacs"/>
<album value="PHM9708-07"/>
<bpm value="000.00"/>
<gain value="00.00"/>
<duration value=" 0:03:53"/>
<Format value="2"/>
<bitrate value="128"/>
<rating value="0"/>
<id value="{3CB08E73-6121-4703-A444-C292B19B350C}"/>
<pos value="6"/>
</media>



I also want to place the data in red into the destination text file or what would be even better into my table in my database [tblKaroakeListings (fields are strArtists & strSongTitle)] in the following way as fixed width in a text file Artist being 150 in length and Song Title being 200 in length:

Artist [fixed width 150 chars] Song Title [fixed width 200 chars]

I understand that some sort of looping is required, but I can’t seem to work out which type of loop I should use : For Next, Do While, For Each etc. and of course how to target those specific data shown in red above.

Any Assistance would greatly appreciated.

John
 

RuralGuy

AWF VIP
Local time
Today, 07:20
Joined
Jul 2, 2005
Messages
13,826
This should get you started.
 

Attachments

  • JohnLee.zip
    30.9 KB · Views: 902

JohnLee

Registered User.
Local time
Today, 06:20
Joined
Mar 8, 2007
Messages
692
Hi RuralGuy,

Wow thanks very much, you have given far more than I expected, I've had a brief look at what you have given and it's very complex, I will be testing it out later today.

This will certainly add to my knowledge and help me to understand such complexities.

Your a star of stars.

Thanks once again

John
 

JohnLee

Registered User.
Local time
Today, 06:20
Joined
Mar 8, 2007
Messages
692
Hi RuralGuy,

Tried and tested, works a treat, Thanks once again

FAB

John
 

RuralGuy

AWF VIP
Local time
Today, 07:20
Joined
Jul 2, 2005
Messages
13,826
Excellent! Thanks for posting back with your success.
 

rgponce

New member
Local time
Today, 09:20
Joined
Feb 3, 2010
Messages
1
Hi, i'm a noob in access and trying use your database sample, but there are two things i can't do.
the first thing is add more update cases in your code, but a error happened (no add or edit functions), how can i do this, i'm forgeting to chance something?
the second thing is add a in code a call to copy and rename pictures files, in my table i have a field with a relative path to pictures and the name of the pictures in database is renamed to something like "Foto_XX.jpg" (the XX is the number id of the record).
Here is the code for a button in my form that i use to chose, copy and rename the picture for the record:
Code:
Private Sub cmdBuscarFoto_Click()
On Error GoTo err_cmdBuscarFoto

   Dim strDialogTitle As String
   Dim PathStrg As String
   Dim Msg As String
   Dim relativePath0 As String
   Dim dbPath As String

   'OriginalImagePath variable is always updated by the Forms' OnCurrent
   If Nz(OriginalImagePath0, "") <> "" Then Me![Foto] = OriginalImagePath0

       strDialogTitle = "Select a default image for " & Me!Personagem
       PathStrg = GetOpenFile_CLT(".\", strDialogTitle)
       
   'If no file was selected then the PathStrg variable will be empty.
   'If there was a file selected then.....
   If PathStrg <> "" Then
   
        'setup new file name and appropriate DB subfolder
        relativePath0 = "\Miniaturas\Foto_" & Me.Mini_id & ".jpg"
        dbPath = CurrentProject.Path
        
        'copy selected file with new name and subfolder
        FileCopy LCase(PathStrg), dbPath & relativePath0
        
        'update the table field with the new file name and relative location
        Me!Foto.Value = relativePath0
        'display the image from the subfolder of the DB
        Me!imgFoto.Picture = dbPath & relativePath0
            
   End If

exit_cmdBuscarFoto:
    Exit Sub
    
err_cmdBuscarFoto:
        Msg = "Error # " & Str(Err.Number) & Chr(13) & Err.Description
        MsgBox Msg, vbOKOnly, "The PED", Err.HelpFile, Err.HelpContext
    Resume exit_cmdBuscarFoto

End Sub
there is a way to put a string in txt file with the relative path to picture, so when i upload the data in record, the databse alone, copy the image to this folder (like the form does) and rename it with using the new id?



ps: sorry my bad english
 

Users who are viewing this thread

Top Bottom