Line Input Problem

  • Thread starter Thread starter Wendy
  • Start date Start date
W

Wendy

I am importing data into recordset from a text file the only problem is I
don't want the data which is on every other line. I tried to kill the line
if the data = what I didn't want but that didn't work. This is my code so
far.

Thanks

Wendy

Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If
'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
Loop
Close #1
Kill Filename
End Function
 
Well, if you are **absolutely certain** that you only want ever other line,
you can just add an addtional Line Input line just before Loop statement:
Do While Not EOF(1)
Line Input #1, Dataline
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If
'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
Line Input #1, Dataline
Loop

However, I'm always a little leery of hard coded solutions. Is there some
way of identifying the lines that you don't want? If so, you could put an
IF statement around your current IF statement to skip the line.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Hi Roger

I thought missing every other line would be the easiest way, but what I
would really like would be delete the line if it contains "BACKUPPRI" or
"RECOVERYF". I know a little bit about vb buts thats getting beyond me!

Thanks

Wendy
 
Well in that case, you'd just use the InStr function to test for those
values. Something like this:

Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If (InStr(Dataline,"BACKUPPRI")=0 AND InStr(Dataline,"RECOVERYF")=0)
Then
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If

'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
End If
Loop
Close #1
Kill Filename
End Function


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Roger

Once its tested for those values can it delete the line(s) with those
values on them? I'm now getting a error because its trying to update the
field with a zero length string. Run-time error '3315' Field
'tblPrintArchive.Filename' cannot be a zero length string.

Wendy
 
You don't need to delete the line. You just need to skip it. (That is NOT
import it.)

As for the error, I don't see how it coud be happening. Are you using the
function as I wrote it? Or is it modified? Is the Update code within the
IF statement? Can you post your current code?

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Hi Roger

The only thing I've changed is I had to move the 'Then' after the IF Instr
command because it red-lined it. I do have the fields set as required = Yes
in the table design. When I press the button to run the function it gives
the error "Run-time error '3315' Field 'tblPrintArchive.Filename' cannot be
a zero length string", when I click the debug button it highlights the
..Update line.

Wendy


Private Function UPDPA()

Dim RSTPar As Recordset 'recordset name
Set dbs = CurrentDb
Set RSTPar = CurrentDb.OpenRecordset("TblPrintArchive", dbOpenDynaset)
'select table and db
Filename = "C:\Import\ddirtest.txt" 'specifies file name
Open Filename For Input As #1
Do While Not EOF(1)
Line Input #1, Dataline
If (InStr(Dataline, "BACKUPPRI") = 0 And InStr(Dataline,
"RECOVERYF") = 0) Then
If Left(Dataline, 5) = "STORE" Then
dirnameline = Mid(Dataline, 30, 4)
Else
Dataline = Mid(Dataline, 16, 25)
End If

'UPDATE RECORD (Parse Record)
With RSTPar
.AddNew
!DirName = dirnameline
!Filename = Trim(Dataline)
.Update
End With
End If
Loop
Close #1
Kill Filename
End Function
 
OK, all we were testing for was the presence of BACKUPPRI and RECOVERYF. By
our logic, if it found either of those two strings, it skipped the line.
Otherwise it continued to parse it. So now, we also have to test for a
blank line. Something like this:

If (InStr(Dataline, "BACKUPPRI") = 0 And InStr(Dataline, "RECOVERYF") = 0
And Len(Dataline) > 0) Then

(Again, you'll have to put them all on the same line because the newgroup
post will split it.)

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Back
Top