Solved

Converting a random file database

Posted on 2000-05-14
16
158 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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
 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access Search and Replace Using VBA 6 69
using web browser with BING 40 119
Added a column screws up code 5 57
Zip Folders Using Chilkat Routines 1 36
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

786 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