By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,987 Members | 1,607 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,987 IT Pros & Developers. It's quick & easy.

Recordset

P: n/a
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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.Update

End With

Wend

Close #1
Wend

End Sub

Aug 11 '06 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.Update

End With

Wend

Close #1
Wend

End Sub
Aug 11 '06 #2

P: n/a
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" <ma********@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.Update

End With

Wend

Close #1
Wend

End Sub

Aug 11 '06 #3

P: n/a
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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.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(Linecounter)
Linecounter = Linecounter + 1
Loop

' SETUP DATASET
....

HTH

--
Smartin
Aug 12 '06 #4

P: n/a
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" <sm********@yahoo.comwrote in message
news:VJ******************************@giganews.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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter
>
' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.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(Linecounter)
Linecounter = Linecounter + 1
Loop

' SETUP DATASET
...

HTH

--
Smartin

Aug 14 '06 #5

P: n/a
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" <sm********@yahoo.comwrote in message
news:VJ******************************@giganews.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.txt"
While fn <""

Open fn For Input As #1

While Not EOF(1)

For Linecounter = 1 To 49

Line Input #1, Dataline(Linecounter)
Next Linecounter

' SETUP DATASET
Dim dbs As DAO.Database
Dim rstltrs As DAO.Recordset
Set dbs = CurrentDb
Set rstltrs = CurrentDb.OpenRecordset("TBLLtrs", dbOpenDynaset)

'UPDATE RECORD (Parse Record)
With rstltrs
.AddNew

!CaseNo = Val(Mid(Dataline(14), 47, 8))
!Ref = Mid(Dataline(15), 49, 15)
!Name = Trim(Dataline(10))

.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(Linecounter)
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
Aug 14 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.