Specific Line Input 1

[jcw5107]

I have a text file that I am tryin' to import into a table.
Below is code that I am working with. I need to manipulate this code so it only adds lines that start with N123, N123 -
Any line that starts with N and then 3 numbers...
Any suggestions or examples on this would be the huckleberry. I am almost done in getting this to work the way I want!!! Below is an example of the text file, and below that is the code I'm working with.
Thanks in advance..!!!
jcw5107

Tail Schd Schd Eng Task Chgd By Added By Task
No Gtwy Date Ref Number Rev Pos ApndxApndxApndxApndx NoV? Date Date No
====================================================================================================================================
N120UP KCGN 03/24/07 EO A300-3210-22610 C E 798639N 03/12/07 15:54 02/27/07 20:28 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 724100-P1-1-L-460 S 397034N 02/27/07 20:58 02/27/07 20:58 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 725300-P2-1-L-460 S 397041N 02/27/07 21:00 02/27/07 21:00 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 792203-P4-1-L-460 S 397073N 03/12/07 15:55 02/27/07 20:30 0
------------------------------------------------------------------------------------------------------------------------------------
N120UP KCGN 03/24/07 TC 801301-P1-1-L-460 S 397075N 03/12/07 15:55 02/27/07 21:01 0
------------------------------------------------------------------------------------------------------------------------------------
N124UP KCGN 03/24/07 CK PERIODIC SERVICE 12 S 396908N 03/12/07 16:58 02/27/07 21:19 0
PS12 IS EQUIVALENT TO THE P12 LISTED ON THE TALLY SHEET


Function ImportTable()
Dim dbs As Database, rst As Recordset
Dim Directory As String
Dim MyString As String

DoCmd.SetWarnings False
DoCmd.RunSQL "Delete * from PlannedTasks"
DoCmd.SetWarnings True

Set dbs = CurrentDb
Directory = (Mid(dbs.Name, 1, Len(dbs.Name) - Len(Dir(dbs.Name))))

Open Directory & "\Parts Required For Scheduled Work.txt" For Input As #1

' Create a dynaset-type Recordset object based on Shoes table.
Set rst = dbs.OpenRecordset("PlannedTasks")

Do While Not EOF(1)
Line Input #1, MyString
'Add a new Record
rst.AddNew
rst!Tail = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!SchdGTWY = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!SchdDate = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!DocType = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!DocNo = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst![Date Scheduled] = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!DocRev = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!EngPos = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 4)
rst!Apndx = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!Apndx1 = Left(MyString, InStr(MyString, " ") - 1)
MyString = Mid(MyString, InStr(MyString, " ") + 1)
rst!Apndx2 = MyString
rst.Update
Loop
' Close text file.

MsgBox "Done!"

Close #1
rst.Close
Set dbs = Nothing

End Function

 

[longhair]

jcw5107,
just a thought - and not tested (more like pseudo code):

if left(mystring,1) = "N" and (mid(mystring,2,3) >= 0 and mid(mystring(2,3) <= 999) then
...

put this right before rst.addnew.
not sure off the top of my head on the syntax for left and mid in access vba but this should get you pointed in the right direction.
regards,
longhair

 

[LARiot]

I'd use If IsNumeric() = True instead of checking if it's between 0 and 999.

 

[longhair]

LARiot,
thanks - you learn something new everyday - didn't even think to look to see if it access vba had an isnumeric function.
regards,
longhair

 

[LARiot]

My pleasure... check out

http://www.techonthenet.com/access/functions/index.php

for all (most?) Access vba functions.

 

[jcw5107]

All:

I think I got a work around...
Below is what I'm using... Thanks for the help..!!
jcw5107
Sub deletestuff()
Dim strLine 'As Variant
Dim strType As Variant
Dim strImportFile As String

strImportFile = "C:\Documents and Settings\Owner\Desktop\UPSApps\EO Planner\NewFile.txt"

Open "C:\Documents and Settings\Owner\Desktop\UPSApps\EO Planner\TestOutput.txt" For Input As #1
Open strImportFile For Output As #2

Do Until EOF(1)
Line Input #1, strLine
If Left(strLine, 2) Like "N#*" Then
strType = strLine
Print #2, strType
End If
Loop

Close #1
Close #2
'DoCmd.TransferText acImportFixed, "nmcdatafixed", _
"Imported NMC Data", strImportFile, False
'Kill strImportFile
End Sub