ma said:
I import a one field txt file into my table.
(I know this is my problem how to keep my header record
followed by my details. I never worked with this kind of a
table before.)
The first record is my header record for BLD_ID followed
by EQUIP_ID, many BLD_ID followed by equipment. I have
tables setup for building, equipment, scanned data.
Record 1 is a header (5 dig #) for BLD_ID "14000", record
2="1740"& record 3="1045' are EQUIP_id (4 DIG #),this
layout continues threw the file.
I match on 14000 and it reads the whole equipment table,
then I can't get to the next header record to read, or
just the equip table that matches.
Hi MA,
If I understand the structure of your text
file correctly.......
Save the following code in a module:
Option Explicit
Public Sub fImportFile(txtfile As Variant)
On Error GoTo Err_fImportFile
Dim TextLine, strTxtFile As String
Dim hFile As Long, lngLineNum As Long
Dim rst As DAO.Recordset
Dim Response, Msg, varReturn
Dim strSQL As String
Dim lngBldID As Long
'check that txtfile is not null nor ZLS
If Len(Trim(txtfile & "")) > 0 Then
'continue processing
strTxtFile = CStr(txtfile)
Else
MsgBox "Please provide filename and path to process."
GoTo Exit_fImportFile
End If
Msg = "Will be importing data from the following txt file: " _
& vbCrLf & vbCrLf & txtfile
Response = MsgBox(Msg, vbOKCancel)
If Response = vbCancel Then ' User chose to Cancel
GoTo Exit_fImportFile
End If
'check that file exists
If Not (fIsFileDIR(strTxtFile)) Then
MsgBox "Could not find file " & strTxtFile & "."
GoTo Exit_fImportFile
End If
'check that tblImport exists
If TableExists("tblImport") Then
'if it exists, clear out all data
CurrentDb.Execute "DELETE * FROM tblImport", dbFailOnError
Else
'does not exist, so create it
strSQL = "CREATE TABLE tblImport (ID AUTOINCREMENT, " _
& "BLD_ID LONG, EQUIP_ID LONG, " _
& "CONSTRAINT PK_ID PRIMARY KEY (ID ));"
'Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError
End If
Set rst = CurrentDb.OpenRecordset("tblImport", dbOpenDynaset)
'First get a new file handle
hFile = FreeFile
'now open the text file line by line
'reading each line into var "TextLine"
Open strTxtFile For Input As hFile
lngLineNum = 0
Do While Not EOF(1) ' Loop until end of file.
lngLineNum = lngLineNum + 1
'****** update progress display in status bar *****************
varReturn = SysCmd(acSysCmdSetStatus, "Processing Line # " & lngLineNum)
Line Input #1, TextLine ' Read line into variable.
'Strip any leading and trailing spaces from string
TextLine = Trim(TextLine & "")
Select Case Len(TextLine)
Case 5 'have BLD_ID
lngBldID = CLng(TextLine)
Case 4 'have EQUIP_ID
'add to table
rst.AddNew
rst!BLD_ID = lngBldID
rst!EQUIP_ID = CLng(TextLine)
rst.Update
Case Else
'have unexpected line
Debug.Print "Unexpected Value on Line # : " _
& lngLineNum & vbCrLf _
& "Value = (" & TextLine & ")"
End Select
Loop
Close hFile ' Close file.
'clear display in status bar
varReturn = SysCmd(acSysCmdClearStatus)
rst.Close
MsgBox "Have successfully imported " & vbCrLf & strTxtFile _
& vbCrLf & " into table tblImport."
Exit_fImportFile:
Set rst = Nothing
Exit Sub
Err_fImportFile:
MsgBox Err.Description
Reset 'for sure close all files opened by this application
Resume Exit_fImportFile
End Sub
'*********************************************
Public Function fIsFileDIR(stPath As String, _
Optional lngType As Long) _
As Integer
'from
http://www.mvps.org/access/modules/mdl0009.htm
'Fully qualify stPath
'To check for a file
' ?fIsFileDIR("c:\winnt\win.ini")
'To check for a Dir
' ?fIsFileDir("c:\msoffice",vbdirectory)
'
On Error Resume Next
fIsFileDIR = Len(Dir(stPath, lngType)) > 0
End Function
'********************************************
Public Function TableExists(strTableName As String) As Boolean
'from Joe Fallon
On Error Resume Next
TableExists = IsObject(CurrentDb.TableDefs(strTableName))
End Function
'********************************************
Make sure you reference DAO library in References.
Where you were importing the file in code previously,
supply the path of your file to the function,
for example....
fImportFile "C:\somefile.txt"
Please respond back if I did not understand
or was not clear about something.
Good luck,
Gary Walter
