tab-delimited blues

I'm in dire need of some assistance on parsing a txt file. I'm currently involved in redoing the website for a Community Supported Radio station here in Austin, TX.



The station's URL is: http://kmfa.org/index.asp

The new site that I'm developing is:

http://wide-awake-garage.homeip.net/cms/



Login using:
user name = guest

password = password



The txt file I'm having the problem parsing is part of the play list area in the existing site:
http://kmfa.org/fortissimo_playlist...&yr=2003&dy=Thu



This play list is generated by a software program called Music Librarian and is exported as a fixed-length txt file. Well, sort of. I think the txt file starts out as fixed-length then loses the fixed lengths. As you see below.





++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++

Play Report




PLAY DATE: Tue, 04/01/2003
12:00 AM PROGRAM 69086A LOT OF NIGHT MUSIC + 8888888
12:01 AM PROGRAM 74191From the Beethoven Satellite Network + 8888888
6:00 AM PROGRAM 72473CLASSICS FROM THE KMFA LIBRARY + 8888888
6:01 AM Leopold Mozart 1736Trumpet Concerto Erato 55014



++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++++++++++++++++++++ +++++++++++++++++++++++++



I would like to clean out the header at the top of the page and parse out the 'PROGRAM' lines and to parse out the numbers prior to third field, ie 69086A LOT OF MUSIC, the 69086 needs to be taken out.



I would like to import this file into Access because I'm not very versed in VBScript, or any other scripting language for that matter. I'm a Graphic Artist learning Web skills to become more marketable - hence this pro-bono job.



I have attached the txt playlist file spewed out by Music Librarian, I have contact the Software vendors and got no help from them. Big surprise there!



Ideally, I need to write a parser that converts the present "whatever" delimited txt file into a comma separated file to so I can import this file into Access.

This is a far as I have gotten in this process.

http://wide-awake-garage.homeip.net...&yr=2003&dy=Thu



The program titles for the station are in a separate db, I would like to have the db create the table headers then VBScript parse in the songs associated to the program.

Is this too much to ask?

Let me know and I really appreciate any assistance you can give!

christopher

You won't be able to do much with a text file that starts out with fixed widths and then loses the fixed widths. Are there any other export options on the music database software?

I spoke to the developer @ Music Librarian and they are pretty
much unwilling to change the exporting functionality. They're looking "into" it - but I'm not keeping my finger crossed.

So, I have done the following.
Parsed the whatever-delimited txt file this way
==============================================
<%

DIM databaseMonths(12)
databaseMonths(0) = "jan"
databaseMonths(1) = "feb"
databaseMonths(2) = "mar"
databaseMonths(3) = "apr"
databaseMonths(4) = "may"
databaseMonths(5) = "jun"
databaseMonths(6) = "jul"
databaseMonths(7) = "aug"
databaseMonths(8) = "sep"
databaseMonths(9) = "oct"
databaseMonths(10) = "nov"
databaseMonths(11) = "dec"

databaseYear = MID(REQUEST("yr"),3,2)
'---SET VARIABLES AND OPEN DATA FILE
dim thepath
thepath = server.mappath("/cms/FileLib/") & "\"
Response.ContentType="application/vnd.ms-excel"
set fso=Server.CreateObject("Scripting.FileSystemObject")
'SET playlistObject = Server.CreateObject("Scripting.FileSystemObject")
set file=fso.OpenTextFile(thepath & databaseMonths(REQUEST("mo")-1) & databaseYear & ".txt")
'set file=fso.OpenTextFile("D:\Inetpub\wwwroot\test\apr03.txt")
'SET playlist = playlistObject.OpenTextFile(thepath & databaseMonths(REQUEST("mo")-1) & databaseYear & ".txt")

size1=len(" 12:00 AM ")
size2=len("PROGRAM ")
size3=len("Parisian Quartet No. 10 (Nouveau No. 4)")
%>
<table border=1 CELLPADDING="1" CELLSPACING="1" BORDERCOLOR="#FFFFFF" BGCOLOR="#CCCCCC">
<%
function getLastNum(stext)
s=""
for i=len(stext) to 1 step -1
if mid(stext,i,1)<>" " then
s=s+mid(stext,i,1)
else
i=-1
end if
next
getLastNum=s
end function
while not file.AtEndOfStream
sline=file.ReadLine
if sline<>" " AND sline<>"" then
if instr(1,sline,"PLAY DATE:")=0 then
stime=left(sline,size1)
sline=replace(sline,stime,"")

stype=left(sline,size2)
sline=replace(sline,stype,"")

snumber=""
snumber=CStr(getNum(sline))
sline=replace(sline,snumber,"")

slastnumber=getLastNum(sline)
sline=replace(sline,slastnumber,"")

stitle=left(sline,size3)
sprod=replace(sline,stitle,"")

if snumber<>"1.#QNAN" then
%>
<tr BORDERCOLOR="#FFFFFF">
<td><font size=2><%=stime%></font>
</td>
<td><font size=2><%=stype%></font>
</td>
<td><font size=2><%=snumber%></font>
</td>
<td><font size=2><%=stitle%></font>
</td>
<td><font size=2><pre><%=sprod%></pre></font>
</td>
<td><font size=2><pre><%=slastnumber%></pre></font>
</td>
</tr>
<%
end if
else
%>
<tr>
<td colspan=6 BORDERCOLOR="#FFFFFF"><font size=3><b><%=sline%></b></font>
</td>
</tr>
<%
end if
end if
wend
%>
</table>
<script language=javascript runat="server">
function getNum(str)
{
return parseInt(str)
}
</script>
<%
rsPLAYlist.Close()
Set rsPLAYlist = Nothing
%>
=============================================
http://wide-awake-garage.homeip.net...&yr=2003&dy=Sat

This creates a excel wrkbok that I can manage to export a tab-delimited file into Access then use my limited experience in, <embarrassed> MM Dreamweaver to display the recordset by three session variables:
1) current time
2) current month
3) current year
I hate to go the well on this thread, but do you know how I could merge two Access tables by one criteria/variable? I'm mean, I have one table that contains the station programming information, and then I have this playlist file residing in another table. Is it possible to have a faction build the program info in the table header then strip in the playlist info accordingly?
For I would like to have the user to see what playing by those three criteria - this was gleaned from a survey.
More importantly - I would like to have the front page display the current composition, real-time, via a timer that queries the db for the current:
1) composition title
2) composer
3) program title/description
4) announcer bio

I have bitten off WAY more than I can chew. I would greatly appreciate any help you could offer.