Recordset

[Wendy]

Hi

I'm trying to import the contents of a text file which is basically letters
in to a table to add more data to them. The original file comes from the
mainframe.

I'm tring to copy the recordset code someone else used to do a similar job a
few years ago. As I'm fairly new to vb. There are 49 lines of text in each
letter, there can be any number of letters in the file and when I run the
code I get 'Runtime error 62 input past end of file' even though the
linecounter has only got to line37.

Can someone give me some ideas where I''m wrong please?

Thanks

Wendy

Option Compare Database
Option Explicit
Public Linecounter As Integer
Public fn As String
Public Dataline(49)
Public dbs As DAO.Database
Public rstltrs As DAO.Recordset

Sub Ltrcode_Filter( )
fn = "C:\import\sc.t xt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Lineco unter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Datalin e(14), 47, 8))
!Ref = Mid(Dataline(15 ), 49, 15)
!Name = Trim(Dataline(1 0))

.Update

End With

Wend

Close #1
Wend

End Sub

[ManningFan]

Re: Recordset

Put a togglepoint in the code and step through it. I would guess that
line 37 has some issues if you open the TXT file as well, have you
checked it to make sure there wasn't a data drop when you exported it
from the mainframe?

Wendy wrote:

Hi
>
I'm trying to import the contents of a text file which is basically letters
in to a table to add more data to them. The original file comes from the
mainframe.
>
I'm tring to copy the recordset code someone else used to do a similar job a
few years ago. As I'm fairly new to vb. There are 49 lines of text in each
letter, there can be any number of letters in the file and when I run the
code I get 'Runtime error 62 input past end of file' even though the
linecounter has only got to line37.
>
Can someone give me some ideas where I''m wrong please?
>
Thanks
>
Wendy
>
Option Compare Database
Option Explicit
Public Linecounter As Integer
Public fn As String
Public Dataline(49)
Public dbs As DAO.Database
Public rstltrs As DAO.Recordset
>
Sub Ltrcode_Filter( )
fn = "C:\import\sc.t xt"
While fn <""
>
Open fn For Input As #1
>
While Not EOF(1)
>
For Linecounter = 1 To 49
>
Line Input #1, Dataline(Lineco unter)
Next Linecounter
>
' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)
>
'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew
>
!CaseNo = Val(Mid(Datalin e(14), 47, 8))
!Ref = Mid(Dataline(15 ), 49, 15)
!Name = Trim(Dataline(1 0))
>
.Update
>
End With
>
Wend
>
Close #1
Wend
>
End Sub

[Wendy]

Re: Recordset

I'm not sure what you mean by a toggle point. Looking at it on the locals
window all the data is there, line37 is shown blank just 2 sets of double
quotation marks, its not the only line which is blank though. It also looks
ok when I look at the text file through windows explorer. I just want to
ignore the blank lines. I was trying to catch the data I want by using the
line numbers. Is there a better (easier) way to do it?

Thanks

Wendy

"ManningFan " <manningfan@gma il.comwrote in message
news:1155312334 .699204.111600@ h48g2000cwc.goo glegroups.com.. .

Put a togglepoint in the code and step through it. I would guess that
line 37 has some issues if you open the TXT file as well, have you
checked it to make sure there wasn't a data drop when you exported it
from the mainframe?
>
Wendy wrote:

>Hi
>>
>I'm trying to import the contents of a text file which is basically
>letters
>in to a table to add more data to them. The original file comes from the
>mainframe.
>>
>I'm tring to copy the recordset code someone else used to do a similar
>job a
>few years ago. As I'm fairly new to vb. There are 49 lines of text in
>each
>letter, there can be any number of letters in the file and when I run the
>code I get 'Runtime error 62 input past end of file' even though the
>linecounter has only got to line37.
>>
>Can someone give me some ideas where I''m wrong please?
>>
>Thanks
>>
>Wendy
>>
>Option Compare Database
>Option Explicit
>Public Linecounter As Integer
>Public fn As String
>Public Dataline(49)
>Public dbs As DAO.Database
>Public rstltrs As DAO.Recordset
>>
>Sub Ltrcode_Filter( )
>fn = "C:\import\sc.t xt"
>While fn <""
>>
>Open fn For Input As #1
>>
> While Not EOF(1)
>>
>For Linecounter = 1 To 49
>>
> Line Input #1, Dataline(Lineco unter)
>Next Linecounter
>>
>' SETUP DATASET
>Dim dbs As DAO.Database
>Dim rstltrs As DAO.Recordset
>Set dbs = CurrentDb
>Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)
>>
>'UPDATE RECORD (Parse Record)
>With rstltrs
> .AddNew
>>
> !CaseNo = Val(Mid(Datalin e(14), 47, 8))
> !Ref = Mid(Dataline(15 ), 49, 15)
> !Name = Trim(Dataline(1 0))
>>
> .Update
>>
>End With
>>
> Wend
>>
>Close #1
>Wend
>>
>End Sub

>

[Smartin]

Re: Recordset

Wendy wrote:

Hi
>
I'm trying to import the contents of a text file which is basically letters
in to a table to add more data to them. The original file comes from the
mainframe.
>
I'm tring to copy the recordset code someone else used to do a similar job a
few years ago. As I'm fairly new to vb. There are 49 lines of text in each
letter, there can be any number of letters in the file and when I run the
code I get 'Runtime error 62 input past end of file' even though the
linecounter has only got to line37.
>
Can someone give me some ideas where I''m wrong please?
>
Thanks
>
Wendy
>
Option Compare Database
Option Explicit
Public Linecounter As Integer
Public fn As String
Public Dataline(49)
Public dbs As DAO.Database
Public rstltrs As DAO.Recordset
>
Sub Ltrcode_Filter( )
fn = "C:\import\sc.t xt"
While fn <""
>
Open fn For Input As #1
>
While Not EOF(1)
>
For Linecounter = 1 To 49
>
Line Input #1, Dataline(Lineco unter)
Next Linecounter
>
' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)
>
'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew
>
!CaseNo = Val(Mid(Datalin e(14), 47, 8))
!Ref = Mid(Dataline(15 ), 49, 15)
!Name = Trim(Dataline(1 0))
>
.Update
>
End With
>
Wend
>
Close #1
Wend
>
End Sub

Some considerations:

Line Input # will stop reading text at a CrLf pair. Are their actually
49 "lines" (as defined by a terminal CrLf) or is it possible some of the
lines wrap in whatever you are viewing the data with that gives the
impression there are more than there actually are?

Have you verified that the last successful line read is what you expect
to be line 37?

IIRC If there is no CrLf at the end of the file, Line Input will throw
error 62 upon attempting to read the last line in the input file.

You don't actually need (or want, I suspect) to loop your input
explicitly 49 times. Let EOF do its job. Try something like this:

Linecounter = 0
Do While Not EOF(1)
Line Input #1, Dataline(Lineco unter)
Linecounter = Linecounter + 1
Loop

' SETUP DATASET
....

HTH

--
Smartin

[Wendy]

Re: Recordset

Hi Smartin

Thanks, I'm a little confused, if I don't use line input to loop the 49
times how do I pick out the names and case numbers to put them into a table?

Wendy


"Smartin" <smartin108@yah oo.comwrote in message
news:VJydncvO5e ETgUDZnZ2dnUVZ_ tGdnZ2d@giganew s.com...

Wendy wrote:

Hi

I'm trying to import the contents of a text file which is basically

letters

in to a table to add more data to them. The original file comes from

the

mainframe.

I'm tring to copy the recordset code someone else used to do a similar

job a

few years ago. As I'm fairly new to vb. There are 49 lines of text in

each

letter, there can be any number of letters in the file and when I run

the

code I get 'Runtime error 62 input past end of file' even though the
linecounter has only got to line37.

Can someone give me some ideas where I''m wrong please?

Thanks

Wendy

Option Compare Database
Option Explicit
Public Linecounter As Integer
Public fn As String
Public Dataline(49)
Public dbs As DAO.Database
Public rstltrs As DAO.Recordset

Sub Ltrcode_Filter( )
fn = "C:\import\sc.t xt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Lineco unter)
Next Linecounter
>
' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Datalin e(14), 47, 8))
!Ref = Mid(Dataline(15 ), 49, 15)
!Name = Trim(Dataline(1 0))

.Update

End With

Wend

Close #1
Wend

End Sub

>
>
>
Some considerations:
>
Line Input # will stop reading text at a CrLf pair. Are their actually
49 "lines" (as defined by a terminal CrLf) or is it possible some of the
lines wrap in whatever you are viewing the data with that gives the
impression there are more than there actually are?
>
Have you verified that the last successful line read is what you expect
to be line 37?
>
IIRC If there is no CrLf at the end of the file, Line Input will throw
error 62 upon attempting to read the last line in the input file.
>
You don't actually need (or want, I suspect) to loop your input
explicitly 49 times. Let EOF do its job. Try something like this:
>
Linecounter = 0
Do While Not EOF(1)
Line Input #1, Dataline(Lineco unter)
Linecounter = Linecounter + 1
Loop
>
' SETUP DATASET
...
>
HTH
>
--
Smartin

[Smartin]

Re: Recordset

Wendy wrote:

Hi Smartin
>
Thanks, I'm a little confused, if I don't use line input to loop the 49
times how do I pick out the names and case numbers to put them into a table?
>
Wendy
>
>
"Smartin" <smartin108@yah oo.comwrote in message
news:VJydncvO5e ETgUDZnZ2dnUVZ_ tGdnZ2d@giganew s.com...

>Wendy wrote:

>>Hi
>>>
>>I'm trying to import the contents of a text file which is basically

letters

>>in to a table to add more data to them. The original file comes from

the

>>mainframe.
>>>
>>I'm tring to copy the recordset code someone else used to do a similar

job a

>>few years ago. As I'm fairly new to vb. There are 49 lines of text in

each

>>letter, there can be any number of letters in the file and when I run

the

>>code I get 'Runtime error 62 input past end of file' even though the
>>linecounter has only got to line37.
>>>
>>Can someone give me some ideas where I''m wrong please?
>>>
>>Thanks
>>>
>>Wendy
>>>
>>Option Compare Database
>>Option Explicit
>>Public Linecounter As Integer
>>Public fn As String
>>Public Dataline(49)
>>Public dbs As DAO.Database
>>Public rstltrs As DAO.Recordset
>>>
>>Sub Ltrcode_Filter( )
>>fn = "C:\import\sc.t xt"
>>While fn <""
>>>
>>Open fn For Input As #1
>>>
>> While Not EOF(1)
>>>
>>For Linecounter = 1 To 49
>>>
>> Line Input #1, Dataline(Lineco unter)
>>Next Linecounter
> >
>>' SETUP DATASET
>>Dim dbs As DAO.Database
>>Dim rstltrs As DAO.Recordset
>>Set dbs = CurrentDb
>>Set rstltrs = CurrentDb.OpenR ecordset("TBLLt rs", dbOpenDynaset)
>>>
>>'UPDATE RECORD (Parse Record)
>>With rstltrs
>> .AddNew
>>>
>> !CaseNo = Val(Mid(Datalin e(14), 47, 8))
>> !Ref = Mid(Dataline(15 ), 49, 15)
>> !Name = Trim(Dataline(1 0))
>>>
>> .Update
>>>
>>End With
>>>
>> Wend
>>>
>>Close #1
>>Wend
>>>
>>End Sub

>>
>>
>Some considerations:
>>
>Line Input # will stop reading text at a CrLf pair. Are their actually
>49 "lines" (as defined by a terminal CrLf) or is it possible some of the
>lines wrap in whatever you are viewing the data with that gives the
>impression there are more than there actually are?
>>
>Have you verified that the last successful line read is what you expect
>to be line 37?
>>
>IIRC If there is no CrLf at the end of the file, Line Input will throw
>error 62 upon attempting to read the last line in the input file.
>>
>You don't actually need (or want, I suspect) to loop your input
>explicitly 49 times. Let EOF do its job. Try something like this:
>>
>Linecounter = 0
>Do While Not EOF(1)
> Line Input #1, Dataline(Lineco unter)
> Linecounter = Linecounter + 1
>Loop
>>
>' SETUP DATASET
>...
>>
>HTH
>>
>--
>Smartin

>
>

In answer to your question, the example I gave still stores the line
input in the Dataline array just as you were. Each time a line is read
the index is incremented. So you can still pick out Dataline(15), etc.
You were doing the indexing using For..Next. What I did wrong in my
example was Initialize Linecounter = 0 -- you probably want Linecounter = 1.

Without seeing the real input file it's hard to know exactly what's
going wrong, but I suspect it is because you are nesting the For..Next
loop (which reads through the file) inside the While Not EOF loop. A
couple things would throw this off:

Input file < 49 lines: The For..Next loop will try to read past EOF
before the loop is finished.

Input file 49 lines: The first pass will read the entire file and
process it. Then, because you have not reached EOF, everything repeats,
including rewriting the array values. Unless the file is /exactly/ a
multiple of 49 lines long, Line Input will choke somewhere in the
For..Next loop because you aren't testing for EOF in there. That is why
I said let EOF do the work for you.

Lastly, I was mistaken about a final CrLf in the file. The following
file will give EOF = TRUE after reading line 03:

Line 01<CrLf>
Line 02<CrLf>
Line 03

HTH (again)
--
Smartin