Random Access File Question

[xathras]

Hi I have a text file with an extract of information:

"Saturday, March 15, 2003" 47 28 4 25 38 24 11
"Wednesday, March 12, 2003" 33 16 6 37 40 41 25
"Saturday, March 08, 2003" 32 5 8 6 33 41 15
"Wednesday, March 05, 2003" 23 8 3 34 46 12 24
"Saturday, March 01, 2003" 29 31 45 44 22 24 35

I have the following code so far:

Module Code:

Option Explicit

Type drawInfo
date As String * 31
ball1 As Integer
ball2 As Integer
ball3 As Integer
ball4 As Integer
ball5 As Integer
ball6 As Integer
bonusBall As Integer
End Type

Public lotResults As drawInfo




Form code:

Dim fieldLength As Long
Dim position As Integer


Private Sub cmdReadRecord_Click()


Get #1, position, lotResults

txtBall1.Text = lotResults.ball1
txtBall2.Text = lotResults.ball2
txtBall3.Text = lotResults.ball3
txtBall4.Text = lotResults.ball4
txtBall5.Text = lotResults.ball5
txtBall6.Text = lotResults.ball6
txtBBall.Text = lotResults.bonusBall
txtDate.Text = lotResults.date

position = position + 1
End Sub

Private Sub Form_Load()


fieldLength = LenB(lotResults)
position = 1
Open "results.txt" For Random As #1 Len = fieldLength

End Sub

It just outputs the data as 0, do i need to convert the file into a format readable by vb or is it because of my code

 

[briancoats]

Your problem is that the data is in string format not integer format. (You can tell because you can actually see the numbers.) Just change you data type to being string * 4, if the numbers all take up 4 spaces, you will have to figure out how many they actually take up, and it should all work fine. If you need the actual numeric value of a string representation of a number use the

val(myString)

function. It returns the numberic value of the number represented by the string.

I hope this helps.

Brian Coats

 

[xathras]

Hi briancoats, thanks for the help but actively changing my code to do that produces no output at all.

Should I change my file to a comma spaced file or something else

 

[ADoozer]

was the data saved with Put # ???



If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[briancoats]

If the spacings arent the same for all the data then you arent going to have any luck with a random access format at all so that would be the first thing to check.

Otherwise you could delimit it in some way though I think the spaces would do fine in this case. If the file is just placed as the characters fall, then you are going to have to read in one line at a time and then manipulate that string to mine hte data. What did you export the data out of?

Brian Coats

 

[ADoozer]

the following is one of many ways to extract the data from your file:-

Private Type DrawInfo
date As String
ball(1 To 6) As Integer
bonusBall As Integer
End Type

Private Sub cmdGetInfo_Click()

Dim myfreefile As Integer
Dim tempstring As String
Dim temparray() As String
Dim arrayDrawInfo() As DrawInfo
Dim i As Integer

i = 0
myfreefile = FreeFile
Open "C:/temp.txt" For Input As #myfreefile
While Not EOF(myfreefile)
Line Input #1, tempstring
temparray = Split(tempstring, " &quot
temparray(0) = Replace(temparray(0), """", "&quot
ReDim Preserve arrayDrawInfo(i)
arrayDrawInfo(i).date = temparray(0)
For j = 1 To 6
arrayDrawInfo(i).ball(j) = CInt(temparray(j))
Next j
arrayDrawInfo(i).bonusBall = CInt(temparray(7))
i = i + 1
Wend
Close #1

'do what you gotta do with the data here!

End Sub

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[xathras]

The data was not input into the file via put command.

The data is an abstract of a excel file which i exported to Excel.

I dont really understand your solution above ADoozer. Can you explain it a little if possible. At the moment I am trying to achieve viewing a record a time.

Could i use access for this, as i am fairly good at vb to access programming.

 

[ADoozer]

ok:-

basically your txt file is in the following format (i assume from your 1st post)

"date" (4 spaces) number (4 spaces) number (4 spaces) number (4 spaces) number (4 spaces) number (4 spaces) number (4 spaces) number crlf(carraige return and line feed)

what my code does is:-

this is the data for each individual line
Private Type DrawInfo
date As String
ball(1 To 6) As Integer
bonusBall As Integer
End Type

Private Sub cmdGetInfo_Click()

Dim myfreefile As Integer
Dim tempstring As String
Dim temparray() As String
Dim arrayDrawInfo() As DrawInfo
Dim i As Integer

i = 0
this gets an available file number
myfreefile = FreeFile
this opens your file for input
Open "C:/results.txt" For Input As #myfreefile
While Not EOF(myfreefile)
Line Input #1, tempstring
this bit splits the line where it finds 4 spaces into an array
temparray = Split(tempstring, " &quot
this bit removes the quotes around the date
temparray(0) = Replace(temparray(0), """", "&quot
this bit redimensions our array while preserving any original records
ReDim Preserve arrayDrawInfo(i)
this bit assigns the data of the line to our DrawInfo type
arrayDrawInfo(i).date = temparray(0)
For j = 1 To 6
arrayDrawInfo(i).ball(j) = CInt(temparray(j))
Next j
arrayDrawInfo(i).bonusBall = CInt(temparray(7))
increment our counter for the next block of info
i = i + 1
Wend
Close #1

'do what you gotta do with the data here!
for i=0 to i-1
msgbox "The lotto balls on " & arrayDrawInfo(i).date _
" where: " & arrayDrawInfo(i).ball(1) & _
arrayDrawInfo(i).ball(2) & arrayDrawInfo(i).ball(3) & _
arrayDrawInfo(i).ball(4) & arrayDrawInfo(i).ball(5) &
arrayDrawInfo(i).ball(6) & " and the bonus ball was: " _
arrayDrawInfo(i).bonusball, vbokonly,"Lotto Balls"

End Sub

if you put this bit (Dim arrayDrawInfo() As DrawInfo) outside the cmdGetInfo (below the type declaration it will exist for the lifetime of the app so you can do as you wish with the data

hope that makes sense... if you have any more questions just ask, im here all night!!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[ADoozer]

oops... this bit

for i=0 to i-1
msgbox "The lotto balls on " & arrayDrawInfo(i).date _
" where: " & arrayDrawInfo(i).ball(1) & _
arrayDrawInfo(i).ball(2) & arrayDrawInfo(i).ball(3) & _
arrayDrawInfo(i).ball(4) & arrayDrawInfo(i).ball(5) &
arrayDrawInfo(i).ball(6) & " and the bonus ball was: " _
arrayDrawInfo(i).bonusball, vbokonly,"Lotto Balls"

should read

for i=0 to i-1
msgbox "The lotto balls on " & arrayDrawInfo(i).date _
" where: " & arrayDrawInfo(i).ball(1) & _
arrayDrawInfo(i).ball(2) & arrayDrawInfo(i).ball(3) & _
arrayDrawInfo(i).ball(4) & arrayDrawInfo(i).ball(5) &
arrayDrawInfo(i).ball(6) & " and the bonus ball was: " _
arrayDrawInfo(i).bonusball, vbokonly,"Lotto Balls"
next i

good luck!!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[ADoozer]

ok its one of those nights....

ill try again....

this bit

for i=0 to i-1
msgbox "The lotto balls on " & arrayDrawInfo(i).date _
" where: " & arrayDrawInfo(i).ball(1) & _
arrayDrawInfo(i).ball(2) & arrayDrawInfo(i).ball(3) & _
arrayDrawInfo(i).ball(4) & arrayDrawInfo(i).ball(5) &
arrayDrawInfo(i).ball(6) & " and the bonus ball was: " _
arrayDrawInfo(i).bonusball, vbokonly,"Lotto Balls"
next i

should read

for k=0 to i-1
msgbox "The lotto balls on " & arrayDrawInfo(k).date _
" were: " & arrayDrawInfo(k).ball(1) & _
arrayDrawInfo(k).ball(2) & arrayDrawInfo(k).ball(3) & _
arrayDrawInfo(k).ball(4) & arrayDrawInfo(k).ball(5) &
arrayDrawInfo(k).ball(6) & " and the bonus ball was: " _
arrayDrawInfo(k).bonusball, vbokonly,"Lotto Balls"
next k

phew! ok hopefully i got it right this time


If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[xathras]

Hi Adoozer, that code was quiet good but I just noticed a problem

THe data is spaced by tabs not 4 spaces, this is probably done to pasting it on the forum, is the a simple replacement?

 

[ADoozer]

um... er... yes/no!!

do you have access to the code that saves the info??

if you do you could replace the tab with a "," then replace this line:-

temparray = Split(tempstring, " &quot

with

temparray = Split(tempstring, ",&quot

alternatively if the code is from excel, you could try saving it as a CSV file!

unfortunately a tab is only a tab in the format it is written (ie it can be 3 whitespaces 4 whitespaces 5 whitespaces and so on)

there are ways around this problem, but i would like to hear back from you first!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[xathras]

Hi ADoozer, I now receieve a type mismatch error.

Abstract of results.txt formatted with commas

"Saturday, March 15, 2003",47,28,4,25,38,24,11
"Wednesday, March 12, 2003",33,16,6,37,40,41,25
"Saturday, March 08, 2003",32,5,8,6,33,41,15
"Wednesday, March 05, 2003",23,8,3,34,46,12,24
"Saturday, March 01, 2003",29,31,45,44,22,24,35
"Wednesday, February 26, 2003",38,5,40,34,18,45,22
"Saturday, February 22, 2003",30,19,42,33,38,44,31

VB Code:

Private Type DrawInfo
date As String
ball(1 To 6) As Integer
bonusBall As Integer
End Type

Private Sub cmdGetInfo_Click()

Dim myfreefile As Integer
Dim tempstring As String
Dim temparray() As String
Dim arrayDrawInfo() As DrawInfo
Dim i, k As Integer

i = 0
myfreefile = FreeFile
Open "C:/results.txt" For Input As #myfreefile
While Not EOF(myfreefile)
Line Input #1, tempstring
temparray = Split(tempstring, ",&quot
temparray(0) = Replace(temparray(0), """", "&quot
ReDim Preserve arrayDrawInfo(i)
arrayDrawInfo(i).date = temparray(0)
For j = 1 To 6
arrayDrawInfo(i).ball(j) = CInt(temparray(j))
Next j
arrayDrawInfo(i).bonusBall = CInt(temparray(7))
i = i + 1
Wend
Close #1



For k = 0 To i - 1
MsgBox "The lotto balls on " & arrayDrawInfo(k).date & " were: " _
& arrayDrawInfo(k).ball(1) & " " & arrayDrawInfo(k).ball(2) & " " & arrayDrawInfo(k).ball(3) _
& " " & arrayDrawInfo(k).ball(4) & " " & arrayDrawInfo(k).ball(5) & " " & arrayDrawInfo(k).ball(6) _
& " and the bonus ball was: " & arrayDrawInfo(k).bonusBall, vbOKOnly, "Lotto Balls"
Next k

End Sub

 

[ADoozer]

ahh crap... sorry it was late last night!!

the reason your getting a missmatch is because i forgot you had commas in the date line, so obviously when you try and assign March to an integer it bombs!!

ok.

change the deliminator to ";" and this line
temparray = Split(tempstring, ",&quot
to
temparray = Split(tempstring, ";&quot

then maybe, just maybe it will run!!!

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[xathras]

Type Mismatch error on

arrayDrawInfo(i).bonusBall = CInt(temparray(7))

Private Type DrawInfo
date As String
ball(1 To 6) As Integer
bonusBall As Integer
End Type

Private Sub cmdGetInfo_Click()

Dim myfreefile As Integer
Dim tempstring As String
Dim temparray() As String
Dim arrayDrawInfo() As DrawInfo
Dim i, k As Integer

i = 0
myfreefile = FreeFile
Open "C:/results.txt" For Input As #myfreefile
While Not EOF(myfreefile)
Line Input #1, tempstring
temparray = Split(tempstring, ";&quot
temparray(0) = Replace(temparray(0), """", "&quot
ReDim Preserve arrayDrawInfo(i)
arrayDrawInfo(i).date = temparray(0)
For j = 1 To 6
arrayDrawInfo(i).ball(j) = CInt(temparray(j))
Next j
arrayDrawInfo(i).bonusBall = CInt(temparray(7))
i = i + 1
Wend
Close #1



For k = 0 To i - 1
MsgBox "The lotto balls on " & arrayDrawInfo(k).date & " were: " _
& arrayDrawInfo(k).ball(1) & " " & arrayDrawInfo(k).ball(2) & " " & arrayDrawInfo(k).ball(3) _
& " " & arrayDrawInfo(k).ball(4) & " " & arrayDrawInfo(k).ball(5) & " " & arrayDrawInfo(k).ball(6) _
& " and the bonus ball was: " & arrayDrawInfo(k).bonusBall, vbOKOnly, "Lotto Balls"
Next k

End Sub



"Saturday, March 15, 2003";47;28;4;25;38;24;11
"Wednesday, March 12, 2003";33;16;6;37;40;41;25
"Saturday, March 08, 2003";32;5;8;6;33;41;15
"Wednesday, March 05, 2003";23;8;3;34;46;12;24
"Saturday, March 01, 2003";29;31;45;44;22;24;35
"Wednesday, February 26, 2003";38;5;40;34;18;45;22
"Saturday, February 22, 2003";30;19;42;33;38;44;31

 

[xathras]

Not to sure if this will work but:

If there was a way to make the text file as a random text file, i.e. convert it so that it is padded out to a format that can be read by visual basic. Thus meaning if i declared date as string * 31, if the date was not 31 it would be padded with spaces.

Would this work, and does anyone know how to do this?

 

[ADoozer]

xathras: your code works for me with the provided data at the bottom, this mismatch is most likely due to the fact one of the numbers is missing on a particular line!

cint("&quot will cause a mismatch.

like i said there are many ways to put data into files and retrieve it again.

data retrieved with Get is usually written with Put

you may find the section in MSDN on "Open Statement" of use!

good luck!

If somethings hard to do, its not worth doing - Homer Simpson
------------------------------------------------------------------------
A General Guide To Excel in VB FAQ222-3383
File Formats Galore @

http://www.wotsit.org

 

[xathras]

ADoozer, your perfectly correct with the bottom numbers it works so somewhere in the results a number must be missing cheers.