Hi,
I give below the complete code which may solve your needs.
You can paste the code in a new standard module.
Please read the comments given in the code and make changes wherever
necessary before testing.
'===================== START CODE ======================================
Option Compare Database
Option Explicit
'Declare module level variables
Dim tabNo As Integer
Dim tab1 As Integer
Dim tab2 As Integer
Dim tab3 As Integer
Dim tab4 As Integer
Dim tab5 As Integer
Dim tab6 As Integer
Dim tab7 As Integer
Dim tab8 As Integer
Dim tab9 As Integer
'In order to use the code, the following conditions
'are to be met.
'1.Set a reference to "Microsoft Scripting Runtime" (SCRRUN.DLL) library
'This library provides maximum flexibility in
'handling text files using FileSystemObject(fso)
'and TextStream object.
'Users can have complete control in
'processing a text file.
'The fso simplifies the task of dealing with
'any type of file input and output and allows
'you to interrogate,create,delete, and
'manipulate folders and text files.
'2.Change dirpath, text file name, Access table name,
'field names, field data types and data type conversion functions
'as appropriate in your machine.
'3.Add error handling code
'This procedure reads a text file line by line
'Extract the values separated by delimiter pipe
'Assign to mapped field variables
'Then update or append in the Access table
'For demonstartion purpose, only five values are
'assumed in a line in the text file.
Public Sub ReadText()
'Declare variables.
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim strLine As String
Dim LineNo As Integer
Dim strItemNo As String
Dim strHeight As String
Dim strWidth As String
Dim strDepth As String
Dim strWeight As String
Dim dirPath As String
Dim strFilePathAndName As String
Dim itemNoLen As Integer
Dim HtLen As Integer
Dim widthLen As Integer
Dim depthLen As Integer
Dim weightLen As Integer
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
'Replace with actual table name in your code
rst.Open "tblTextFileDemo", cnn, adOpenKeyset, adLockOptimistic,
adCmdTable
'assign the text file to be read with complete path
'Replace with appropriate path and file name
'in your code.
dirPath = "C:\surendran\Communities\Text Files"
strFilePathAndName = dirPath & "\TextFile.txt"
'Open file.
Set ts = fso.OpenTextFile(strFilePathAndName)
'Loop while not at the end of the file.
'Read line by line.
'Get values between delimiters.
'Assign to appropriate field variables.
'Convert to proper data type
'of fields in Access table
'Find the itemNo in table using rst.find
'If found update else add the itemNo
'Assuming the data in text file is
'something like shown below.
'Actual data may be with or without headers
'The numbers shown in first line
'is the delimiter number which is given
'to follow the code and logic used.
' 1 2 3 4
'itemNo!Height!Width!Depth!Weight
'123 !20 !25 !30 !200
LineNo = 0
Do While Not ts.AtEndOfStream
'assign the line being read.
strLine = ts.ReadLine
'Incement LineNo to know the line number being read
LineNo = LineNo + 1
'A procedure is called to assign
'the delimiters position to module
'level variables tab1,tab2,etc which are
'used to extract the values between
'delimiters using mid function.
'The code for the procedure is
'given below separately.
Call getDelimiterPosition(strLine)
itemNoLen = tab1 - 1
HtLen = tab2 - tab1 - 1
widthLen = tab3 - tab2 - 1
depthLen = tab4 - tab3 - 1
'weightLen = tab5 - tab4 - 1
If LineNo = 1 Then
'If headers are in first line leave it.
'If there are no headers and want to read from
'first line, remove if else then structure.
Else
strItemNo = Trim(Mid(strLine, 1, itemNoLen))
strHeight = Trim(Mid(strLine, (tab1 + 1), HtLen))
strWidth = Trim(Mid(strLine, (tab2 + 1), widthLen))
strDepth = Trim(Mid(strLine, (tab3 + 1), depthLen))
'The last field value weight is read from
'(last delimiterposition plus one) to the end of line
strWeight = Trim(Mid(strLine, (tab4 + 1)))
'Check the table has some records
If Not rst.BOF Or Not rst.EOF Then
rst.MoveFirst
If Len(strItemNo) > 0 And IsNumeric(strItemNo) Then
rst.Find "ItemNo = " & CLng(strItemNo)
If Not rst.EOF Then
'The itemNo is found in the table.
'Update the field values.
'Assuming the fields Height,Width
'Depth and Weight is double data type
'convert the string values to double
'before assigning and update.
'Sometimes the values may be empty
'or may not be appropriate for conversion.
'Now checking for one incidence like
'the value may be blank or nil
'Add more checking as appropriate
'like isNumeric etc.
If Len(strHeight) > 0 Then
rst!Height = CDbl(strHeight)
End If
If Len(strWidth) Then
rst!Width = CDbl(strWidth)
End If
If Len(strDepth) Then
rst!depth = CDbl(strDepth)
End If
If Len(strWeight) Then
rst!Weight = CDbl(strWeight)
End If
rst.Update
Else
'The itemNo is not found.
'So add the new itemno
'The itemNo field is assumed to be
'Auto Number. So not included.
rst.AddNew
If Len(strHeight) > 0 Then
rst!Height = CDbl(strHeight)
End If
If Len(strWidth) Then
rst!Width = CDbl(strWidth)
End If
If Len(strDepth) Then
rst!depth = CDbl(strDepth)
End If
If Len(strWeight) Then
rst!Weight = CDbl(strWeight)
End If
rst.Update
End If
End If
End If
End If
'Debug.Print ts.ReadLine
Loop
'Close the text file.
ts.Close
'Close the recordset and release resources.
rst.Close
Set rst = Nothing
End Sub
'========================================
Public Sub getDelimiterPosition(stLine As String)
tabNo = 0
Dim stLen As Integer
Dim i As Integer
'On Error Resume Next
stLen = Len(stLine)
'The ASCII value for !(pipe) character is 33
'To know the ASCII values refer to the
'following resources.
'1.Access help - search for "character set"
'2.
http://www.lookuptables.com/
'If delimiter is different say comma, tab etc.
'then replace the value 33
'with 9 for tab and 44 for comma.
'Get the delimiter index position in the line
'and assign to module level variables.
'This code will assign upto 9 delimiters
'i.e. for upto 10 values in a line
For i = 1 To stLen
If Asc((Mid(stLine, i, 1))) = 33 Then
tabNo = tabNo + 1
'Debug.Print tabNo & vbTab & i & vbTab & stLen
If tabNo = 1 Then
'Debug.Print tabNo & vbTab & i & vbTab & stLen
tab1 = i
ElseIf tabNo = 2 Then
tab2 = i
ElseIf tabNo = 3 Then
tab3 = i
ElseIf tabNo = 4 Then
tab4 = i
ElseIf tabNo = 5 Then
tab5 = i
ElseIf tabNo = 6 Then
tab6 = i
ElseIf tabNo = 7 Then
tab7 = i
ElseIf tabNo = 8 Then
tab8 = i
ElseIf tabNo = 9 Then
tab9 = i
End If
End If
Next i
End Sub
'============================== END CODE ==================================
Good luck,
Surendran
