Solved

Converting a random file database

Posted on 2000-05-14
16
156 Views
Last Modified: 2010-05-02
I have a random file database with the TYPE declarations as such:
Type coloradooldinfo
     mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 1000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

I find the need to change the database to this:
Type coloradonewinfo
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 5000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
 
End Type

I would like to read in a record at a time and convert to the new form and then save the record, then I would get the next old record, convert, save, ETC.
I have a hard time doing this. Now with code, how do I go about it?
I am giving alot of points because I really need it.
0
Comment
Question by:dtucker
  • 5
  • 3
  • 3
  • +5
16 Comments
 
LVL 7

Expert Comment

by:kamall
ID: 2808950
Read the records one at a time from the file, convert it to the new format, and save it to a new file. What is the difficulty in that?
Regards.
0
 
LVL 9

Expert Comment

by:GivenRandy
ID: 2808951
Is this an Access database?  Do you care if it is DAO, RDO, or ADO?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2808969
Try this..

1. Start a new Standard.Exe Project.
2. Copy/Paste the following into the code window.
3. Change the Form Load strFileGet / strFilePut strings to the files you desire.
4. Press F5 to run.

<----- Code Begin ----->

Option Explicit

Private Type ColoradoInfoOld
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 1000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Type ColoradoInfoNew
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 5000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Sub Form_Load()
   
   Call xFile_Convert("c:\MyPath\MyFile.INP", "c:\MyPath\MyFile.OUT") '<--- CHANGE

End Sub

Private Sub xFile_Convert(ByVal strFileGet As String, ByVal strFilePut As String)

   Dim intFileGet As Integer
   Dim udtGet As ColoradoInfoOld
   On Error GoTo ErrGet
   Open strFileGet For Random Access Read As intFileGet Len = Len(udtGet)
   On Error GoTo 0
   If LOF(intFileGet) < Len(udtGet) _
   Then
      Close intFileGet
      Exit Sub
   End If
   
   Dim intFilePut As Integer
   Dim udtPut As ColoradoInfoNew
   On Error GoTo ErrPut
   Open strFilePut For Random Access Write As intFilePut Len = Len(udtPut)
   On Error GoTo 0
   
   Dim lngRecords As Long
   lngRecords = LOF(intFileGet) / Len(udtGet)
   
   Dim lngRecord As Long
   For lngRecord = 1 To lngRecords
      Get intFileGet, lngRecord, udtGet
      With udtPut
         .mountain = udtGet.mountain
         .hgt = udtGet.hgt
         .dte = udtGet.dte
         .trail = udtGet.trail
         .comments = udtGet.comments
         .companions = udtGet.companions
         .pixtext = udtGet.pixtext
         .diff = udtGet.diff
         .fun = udtGet.fun
         .check = udtGet.check
      End With
      Get intFilePut, lngRecord, udtPut
   Next lngRecord

   Close (intFileGet)
   Close (intFilePut)
   Exit Sub
   
ErrGet:
   MsgBox "Input File Error: " & strFileGet
   Exit Sub

ErrPut:
   MsgBox "Output File Error: " & strFilePut
   Exit Sub

End Sub

<----- Code End ----->
0
 
LVL 14

Accepted Solution

by:
wsh2 earned 600 total points
ID: 2808974
Ooops... a code correction.. Please use the following rather than the code form above.

<----- Code Begin ----->

Option Explicit

Private Type ColoradoInfoOld
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 1000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Type ColoradoInfoNew
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 5000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Sub Form_Load()
   
   Call xFile_Convert("c:\MyPath\MyFile.INP", "c:\MyPath\MyFile.OUT") ' <-- CHANGE

End Sub

Private Sub xFile_Convert(ByVal strFileGet As String, ByVal strFilePut As String)

   On Error GoTo ErrRead
   Dim intFileGet As Integer
   Dim udtGet As ColoradoInfoOld
   Open strFileGet For Random Access Read As intFileGet Len = Len(udtGet)
   On Error GoTo 0
   
   If LOF(intFileGet) < Len(udtGet) _
   Then
      Close intFileGet
      GoTo ErrNoRecords
   End If
   
   On Error Resume Next
   Kill strFilePut
   
   On Error GoTo ErrWrite
   Dim intFilePut As Integer
   Dim udtPut As ColoradoInfoNew
   Open strFilePut For Random Access Write As intFilePut Len = Len(udtPut)
   
   Dim lngRecords As Long
   lngRecords = LOF(intFileGet) / Len(udtGet)
   Dim lngRecord As Long
   For lngRecord = 1 To lngRecords
      On Error GoTo ErrGet
      Get intFileGet, lngRecord, udtGet
      With udtPut
         .mountain = udtGet.mountain
         .hgt = udtGet.hgt
         .dte = udtGet.dte
         .trail = udtGet.trail
         .comments = udtGet.comments
         .companions = udtGet.companions
         .pixtext = udtGet.pixtext
         .diff = udtGet.diff
         .fun = udtGet.fun
         .check = udtGet.check
      End With
      On Error GoTo ErrPut
      Put intFilePut, lngRecord, udtPut
   Next lngRecord

   On Error GoTo 0
   
   MsgBox (lngRecords & " Records Converted")
   Close (intFileGet)
   Close (intFilePut)
   Exit Sub
   
ErrGet:
   MsgBox "Input File Error: " & strFileGet
   Exit Sub

ErrNoRecords:
   MsgBox "No Records Found To Convert: " & strFileGet
   Exit Sub

ErrPut:
   MsgBox "Output File Error: " & strFilePut
   Exit Sub

ErrRead:
   MsgBox "Read Error: " & strFileGet
   Exit Sub

ErrWrite:
   MsgBox "Write Error: " & strFilePut
   Exit Sub

End Sub

<---- Code End ----->
0
 
LVL 27

Expert Comment

by:Ark
ID: 2809098
Hello
wsh2, may I offer some code improvement?

Option Explicit

Private Type ColoradoInfoOld
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 1000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Type ColoradoInfoNew
    mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 5000
    companions As String * 50
    pixtext As String * 60
    diff As String * 3
    fun As String * 3
    check As Integer
End Type

Private Declare Sub CopyMemory Lib "Kernel32" Alias "RtlMoveMemory" (pDest As Any, pSource As Any, ByVal ByteLen As Long)


Private Sub Form_Load()
   
   Call xFile_Convert("c:\MyPath\MyFile.INP", "c:\MyPath\MyFile.OUT") ' <-- CHANGE

End Sub

Private Sub xFile_Convert(ByVal strFileGet As String, ByVal strFilePut As String)
   Dim nFileGet As Integer, nFilePut As Integer, nRec As Integer, i As Integer
   Dim CIO As ColoradoInfoOld
   Dim CIN As ColoradoInfoOld
   nFileGet = FreeFile
   nFilePut = FreeFile
   Open strFileGet For Random Access Read As nFileGet Len = Len(CIO)
   Open strFilePut For Random Access Write As nFilePut Len = Len(CIN)
   nRec = LOF(nFileGet)\Len(CIO)
   For i = 1 to nRec
       Get #nFileGet, ,CIO
' don't need fill all new udt, just
       CopyMemory CIN, CIO, Len(CIN)
' remove garbage
       CIN.comments = Left$(CIN.comments,1000)
       Put #nFilePut, ,CIN
   Next i
   Close #nFileGet
   Close #nFilePut
End Sub

' Don't add error handling
Cheers
0
 
LVL 27

Expert Comment

by:Ark
ID: 2809107
Ooops...
Sorry, just some bugs, still working on improvement.
Cheers
0
 
LVL 27

Expert Comment

by:Ark
ID: 2809204
I thought that CopyMemory function is more clever :(
This part of code should be:
       CopyMemory CIN, CIO, Len(CIN)
' remove garbage
       CIN.comments = Left$(CIN.comments,1000)
       CIN.companions = CIO.companions
'etc until the end of udt
       Put #nFilePut, ,CIN
   
But this is almost the same as wsh2 offer. I'm ashamed. Sorry.
' But still triing
Cheers
0
 
LVL 1

Expert Comment

by:RobMWilliams
ID: 2809560
dtucker,
I hate to see those fixed length strings in a file, especially ones like: “comments As String * 5000”
If there is a comment like “Nice mountain” it’s still going to take up 5000 bytes of disk space.  If you have just 100 records, that’s a half a meg of empty space right there (assuming there isn’t anything to comment about).

That’ll slow your sequential searches down looking thru all that empty data.

Why not use a variable length string (comments As String)?  That way there’s no space if there’s no comments, and if someone had a wonderful trip to Mt. Evans and had 55,000 bytes of comments to make, he or she wouldn’t be cut off at 5000. (How rude!)

But if you ARE going to use fixed length records, you should try to have them add up to 4096 (or multiples of) since that’s the size it will take on the disk anyway.  You’ve got about 5400 or so now, so the remaining 2800 will be wasted (in ADDITION to the 5000 in the comments).  This will really add up of you use many files, or god forbid – 1 record per file.

Okay now you will have to work a little harder as a programmer since you won’t be able to just overwrite a record if you have an update (unless it’s smaller), but that’s part of the challenge of programming anyway.

Rob

0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 12

Expert Comment

by:pjknibbs
ID: 2809606
I'm with RobMWilliams. It seems odd you have a simplistic string-based database when you're programming in Visual BASIC, which is quite possibly the easiest language in the world for data access. You could have set this whole thing up as an Access database, using mostly memo fields, and accessed it through ADO--that would also have given you a pre-made editor (Access itself!).
0
 

Expert Comment

by:ahmedali
ID: 2809777
hello : dtucker
1-define your new type of field inclode your change in a size
Ex:  
Type coloradooldinfo1
     mountain As String * 50
    hgt As String * 6
    dte As String * 10
    trail As String * 300
    comments As String * 5000
    companions As String * 50
    pixtext As String * 60
    diff As String * 7
    fun As String * 3
    check As Integer
End Type

2-create a new table in your database
3-read the data from old record and assign it's values to the new record Ex:
coloradooldinfo1.comments=coloradooldinfo.comments
coloradooldinfo1.diff=coloradooldinfo.diff
4-then save it in the new table
whitch you create
you can do all recors by loop .
i hope you success
regards
0
 

Author Comment

by:dtucker
ID: 2810507
To wsh2

Your code looks good but gives me an error right off the bat with errRead.

I just used your code as is and plugged in my file to convert and the name of the converted file and I get the Read Error on my file.

What am I missing?
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2811094
Did you change the filepath statements in this statement to match the filepaths as they exist on your disk?

Call xFile_Convert("c:\MyPath\MyFile.INP", "c:\MyPath\MyFile.OUT") ' <-- CHANGE

Parameter 1, is the file you will be reading from and parameter 2 is the file you will be outputting. The 1 & 2 FilePath names have to be different. Copy/Paste your Call statement here, if you would like us to check your syntax.

To be honest, about the only ErrRead problem that could happen, is the filepathname of parameter 1 cannot be found on your disk. As such, make certain it is correct before proceeding further.. <smile>.
0
 

Author Comment

by:dtucker
ID: 2811493
To wsh2
Here is my code:
Call xFile_Convert("g:\grub14.cmd", "g:\test.cmd") ' <-- CHANGE
and in the root dir of g: is the file grub14.cmd

I also got a few syntax errors until I made the Private statements Public, then the errors went away.
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2811876
The call statement looks great.. and YES, if you are calling the routine from another module you do have to make it Public.

So.. where are you at?.. <smile>
0
 

Author Comment

by:dtucker
ID: 2812150
To wsh2,

Well I figured it out...
intFileGet was set to zero so when I set it to intFileGet=freefile then I got an error on intFilePut so I set that with FreeFile and then it worked perfectly.

Thank you very much,

Dave
0
 
LVL 14

Expert Comment

by:wsh2
ID: 2812580
Talk about not seeing the forest for the trees.. <sheesh>.. Great catch dTucker.. <smile>.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now