Help converting an old database to something newer

darkcyber · #1 (**permalink**) 12-07-09, 13:50

I apologize if this is not posted in the correct area. This seemed to be the closes one to what I was asking.

Ok, years ago I wrote a simple DOS Basic program to catalog music. I now have something close to 30,000 + songs cataloged in this database. What I would like to do is to be able to convert ALL of this data into something more 'TODAY'...i.e. Excel or something. Does anyone have a clue if this is possible. The old data file is a Random Access .DAT file. I custom configured the array and I am posting some of the code so you can see what I am talking about. Or can anyone point me in the direction of a good database board that might can help me with this?

I wrote this thing back in the earlier 90's and it is still working, but I really would like to move this data over to something else, without having to manually enter over 30,000 songs

Here is the code:

COMMON SHARED MN
' STORES MUSIC, BY MEDIUM, NUMBER, & ARTIST
Type MUSICREC
NUMBER As String * 5
ST As String * 1
SONGTITLE As String * 50
ARTIST As String * 45
RATING As Integer
EDATE As Long
End Type

Dim MR As MUSICREC
Dim A$(200), B$(200), C$(200), D$(200), E(200)

Open "C:\MUSIX\RF.DAT" For Random Access Read Write As #1 Len = 107

healdem · #2 (**permalink**) 12-07-09, 15:45

if you have access to a dos basic interpreter then you could write the existing data to a csv file and then import it into what ever db tool you have access to.
that coudl be Access or FileMaker and so on

kitaman · #3 (**permalink**) 12-08-09, 10:26

If you don't have Basic anymore, if you have a program that prints the entire list, you could create a generic text printer and print the list to disk, then you should have a fixed column file with no printer escape sequences that you could load into a database.

darkcyber · #4 (**permalink**) 12-08-09, 11:11

Quote:

Originally Posted by healdem

if you have access to a dos basic interpreter then you could write the existing data to a csv file and then import it into what ever db tool you have access to.
that coudl be Access or FileMaker and so on

I still have my copy of VB 6.0 and that will open it up. It has been years (and I do mean years) since I have done any programming. Can you share a little more info on what you are talking about writing?

kitaman · #5 (**permalink**) 12-08-09, 12:21

This is somewhat like the blind leading the blind. The following pseudo code should create a tab delimited file.

Quote:

COMMON SHARED MN
' STORES MUSIC, BY MEDIUM, NUMBER, & ARTIST
Type MUSICREC
NUMBER As String * 5
ST As String * 1
SONGTITLE As String * 50
ARTIST As String * 45
RATING As Integer
EDATE As Long
End Type

Dim MR As MUSICREC
Dim A$(200), B$(200), C$(200), D$(200), E(200)

Open "C:\MUSIX\RF.DAT" For sequential Access Read As #1 Len = 107

open "C:\MUSIX\LIST.TXT" for sequential access write as #2
while read #1
do
print #2,number;chr$(9);st;chr$(9);songtitle;chr$(9);art ist;chr$(9);rating;chr$(9);edate
end while
close #2
quit

healdem · #6 (**permalink**) 12-08-09, 13:19

yup that should do it, or get you close to it
it may be smarter to encapsualte string columns with text delimiters eg " or '
it may be smart to repalce existing ' or " in the data stream with the escape sequence (forget waht it is in Access may well be \ eg o'callaghan become o\'callaghan
depending on how you are writing this data it may be smarter to use the comma to separate the columns and save the file as a CSV rather than a TXT file but thats down to your preference
you may bneed to tinker with the date to make certain its valid when importing into access. you will undoubtedly make your life easier if you use the abomination of a US styled date eg mm/dd/yyyy.

darkcyber · #7 (**permalink**) 12-09-09, 11:12

Thanks for the tips, I will give that a try.

I said transfer it to Excel in my earlier post. I really don't care what I can get it transferred into, I just want to get it into something that is actually more for modern Windows.

Update:

When I type these two lines into VB 6:

Open "C:\MUSIX\RF.DAT" For sequential Access Read As #1 Len = 107

open "C:\MUSIX\LIST.TXT" for sequential access write as #2

I get an error. These says 'compile error: Expected: Input or Output or Append or Binary or Random'

Could I be getting this error because the original file is 'Random Access'?

loquin · #8 (**permalink**) 12-17-09, 14:58

Code:

Type MUSICREC
  NUMBER As String * 5
  ST As String * 1
  SONGTITLE As String * 50
  ARTIST As String * 45
  RATING As Integer
  EDATE As Long
End Type
Private Sub XfrData
  Dim N as Integer
  Dim RecBuff as MUSICREC

  Open "C:\MUSIX\RF.DAT" For Random Read As #1

  open "C:\MUSIX\LIST.TAB" for output as #2

  Do While not EOF(#1)
    N=N+1
    Get #1,N,RecBuff
    With RecBuff
      Print #2, """" & .NUMBER & """" & vbTab & """" & .ST & """" & vbTab & """" & .SONGTITLE & """" _
            & vbTab & """" & .ARTIST & """" & vbTab & cStr(.RATING) & vbTab & Cstr(.EDITDATE)
    End With
  Loop

  Close #2
  Close #1
End Sub

loquin · #9 (**permalink**) 12-17-09, 18:41

FYI. It had been a while since I've done anything with random I/O files, and had a couple of hours to burn, so I threw together a little VB6 app that may help.

Merry Christmas!

girishbs · #10 (**permalink**) 01-11-10, 02:17

Once you have exported your .DAT file to CSV format, you could straight away import it into a DB using many free online tools (just google for csv to sql) And Excel opens these csv files anyway.

I'm just curious are these mp3 files? in which case most of players support some form of library management.

Internet Services

Web Development

Digital Marketing

Consumer Tech