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'd Love To Change The World - 10 Years After.MP3">
<title value="I'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
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'd Love To Change The World - 10 Years After.MP3">
<title value="I'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