[AccessD] cataloguing mp3 albums

Dave said:
> I have sent the MDB directly to Kathryn.

and regarding posting:
> Assuming that We can't; if any one would
> like a copy, let Me know and I'll send You
> a copy directly ( zip is only 35K ).

I changed the name and put it up where everyone who wants it can get it.
(215kb). 
http://www.ourmailinglist.org/misc/mp3cataloguefromtext.mdb

Next, since this is to be a learning process for me, and possibly others
on the list, I'll let you know what I did to explore it. I was expecting a
series of queries to populate tables but found none. So I went exploring.
There are 3 tables "Text_In", "AlbumData", and "AlbumTrackData". And, they
are populated, the first with my data, pretty much as I gave it, and the
other two populated with the expected data. Next, I had to figure out how
they got populated without queries, and I found the macro.

The macro is called DoesItAll - and that has two action lines - first
ReadText ()  then Process_The_Text () . So next I went to find the codes
for what happens. I found a module called Read_The_Text, content below.
The first part is pretty easy to figure out, but I have some
questions/comments intersperced. To conservere space, I'll leave out the
blank lines.

Function ReadText()
'first get rid of all old data
ClearTable ("Text_In")
Dim FileNo As Integer
Dim LineRead As String
Dim Db As DAO.Database
Dim RsText As DAO.Recordset
Set Db = CurrentDb
Set RsText = Db.OpenRecordset("Text_In")
FileNo = FreeFile     what is this?
Open "C:\My_CD_Data.txt" For Input As #FileNo   Aha, you made a text file
of what I sent. I can either make mine named the same, in the same
location, or I need to change the path/filename in the module. The rest of
it is also pretty easy to understand. Even though I wouldn't be able to
"write" the code, at least I can understand the process, and that's part
of the learning,
Do While Not EOF(FileNo)
Line Input #1, LineRead
If Not IsNull(LineRead) And Len(LineRead) > 0 Then
RsText.AddNew
RsText!TextLine = LineRead
RsText.Update
End If
Loop
Close FileNo
Set RsText = Nothing
Set Db = Nothing
End Function

So now, that reads the raw data and puts it into the "Text_In" table. On
to the next part.

Function Process_The_Text()
'Album : Biggest Hits
'first get rid of all old data
ClearTable ("AlbumData")
ClearTable ("AlbumTrackData")
Dim AlbumID As Integer
Dim AlbumName As String
Dim TrackNo As Integer
Dim TrackTitle As String
Dim Artists As String
Dim Length  As String
Dim ArtistStart As Integer
Dim LengthStart As Integer
Dim Db As DAO.Database
Dim RsText As DAO.Recordset
Dim RsAlbums As DAO.Recordset
Dim RsTracks As DAO.Recordset
Set Db = CurrentDb
Set RsText = Db.OpenRecordset("Text_In")
Set RsAlbums = Db.OpenRecordset("AlbumData")
Set RsTracks = Db.OpenRecordset("AlbumTrackData")
Followed almost everything so far, the two other tables have been cleared,
for me to do this from scratch when I've finished ripping everything. The
first batch of dimension statements tell what the field names are, and
whether they are strings or numbers (integers). I kinda know about the
DAO.database and recordset from listing to things on the list, but I'm not
real clear on it yet. I understand enough to follow along, but one day
will have to get a better handle on what it does. Then the Set statements
I understand as well..
AlbumID = 0
ok, this one I'm guessing is setting a starting point so that the first
albumID will be a one.
Do While Not RsText.EOF()
Do While Not RsText.EOF And Mid(RsText!TextLine, 1, 5) <> "ALBUM"
Loop
The first line is do it until the ent of file is reached, but the next one
one I'm not *sure* of myself. I think that the purpose is to make sure
that the lines with Album descriptions (title) are a natural breaking
point - like an end of file, but not really ending the file, just that
particular album's EOF. Is that a reasonable understanding?
AlbumID = AlbumID + 1
AlbumName = Trim(Mid(UCase(RsText!TextLine), InStr(RsText!TextLine, ":") +
1))
This one is to trim the title of the track, using the first hyphen as the
breaking point. But I'm not clear on the purpose of the +1, nor am I clear
on why the Mid, and is UCase for upper case? I really need this line of
code explained in plain English.
RsAlbums.AddNew
RsAlbums!AlbumID = AlbumID
RsAlbums!AlbumName = AlbumName
RsAlbums.Update
RsText.MoveNext
Do While Not RsText.EOF
If Mid(RsText!TextLine, 1, 5) = "ALBUM" Then
What are the 1 & 5 about?
  Exit Do
End If
ArtistStart = InStr(RsText!TextLine, "-") + 1
LengthStart = InStr(RsText!TextLine, "(0") - 1
Yeah, I got this one - the artist name starts one space after the hyphen.
and the length of the track starts with ( and the -1 backs it up so that
it includes the (. I crossed out the part above regarding the +1 because I
thought I had I figured it out with this one. But I can't figure out why
it's +1 in the Album Name, instead of -1.
    RsTracks.AddNew
    RsTracks!AlbumName = AlbumName
    RsTracks!AlbumID = AlbumID
    RsTracks!TrackNo = Val(Mid(RsText!TextLine, 1, 3))
    RsTracks!TrackTitle = Trim(Mid(RsText!TextLine, 4, ArtistStart - 5))
Again, what are the 1,3 & 4 & 5 about? and now I'm getting lost. I'm
guessing the rest of this section has something to do with what to do when
there is missing artist data. But I really need it explained in English
instead of code.
    If LengthStart > ArtistStart Then
    RsTracks!Artists = Trim(Mid(RsText!TextLine, ArtistStart, LengthStart
- ArtistStart))
    Else
    RsTracks!Artists = " "
    End If
    RsTracks!Length = Trim(Mid(RsText!TextLine, LengthStart))
    RsTracks.Update
    RsText.MoveNext
Loop
Loop
Set RsTracks = Nothing
Set RsAlbums = Nothing
Set RsText = Nothing
Set Db = Nothing
End Function

Well teacher, how did I do? I give myself a C+/B- in the understanding.
Now to go try it. Oh, btw, I noticed something odd 
AlbumName	AlbumID	TrackNO	TrackTitle	Artists	Length	
BIGGEST HITS	2	2	Colorado Cool	Aid - Johnny Paycheck
(00:02:57)	
What made Aid end up with artist instead of TrackTitle? Oh, wait a minute,
I bet I know even without going to look at the raw text. I'll bet the
title is Colorado Cool-Aid, making that the first hyphen. I probably have
other bits to clean up too, but this sure makes things easier. Back in a
minute, while I go test it out.

I'm back. It took longer than a minute. Some of the albums ripped since
the sample have loooong album titles and track titles (error message told
me it was too smal to accept data), and a couple don't have artist,
consequently no hyphen (causing a debug error in RsTracks!TrackTitle =
Trim(Mid(RsText!TextLine, 4, ArtistStart - 5)) line. So, first, I went and
changed the lengths on the text fields, then ran the macro over and over.
Each time I'd get further before hitting the debug error. I'd go look and
sure enough, it encountered one where there was no hyphen. I'd fix the raw
text file and run again. At least the ease of use makes it easy to find
where the raw text needs cleaning.

All in all, this is pretty cool! Is there a way to tackle doing the same
thing with update and make-table queries? It might be fun to compare how
they work the same or different.

Thanks again!

--
Kathryn Rhinehart Bassett (Pasadena CA)
"Genealogy is my bag" "GH is my soap"
kathryn at bassett.net
http://bassett.net  

-------------- next part --------------
A non-text attachment was scrubbed...
Name: winmail.dat
Type: application/ms-tnef
Size: 5468 bytes
Desc: not available
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030914/38ed0b7a/attachment-0001.bin>