Hi,
I give below two procedures - one for reading single text file which is
complete and can be slightly modified and can be tested at your machine and
the second procedure giving an idea of reading multiple text files in a
directory using the code in first procedure.
The procedure works only for the type of sample data given by you.
Otherwise you may have to make changes.
'=================== START CODE ===============================
Public Sub ReadSingleTextFile()
'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 as appropriate in your machine.
'3.Add error handling code
'Declare variables.
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim strLine As String
Dim LineNo As Integer
Dim dirPath As String
Dim strFilePathAndName As String
Dim gtPos As Integer 'variable for >
Dim ltPos As Integer 'variable for <
Dim strUN As String 'To store username
Dim strPW As String 'To store password
Dim tagData1 As String 'To store the data between start and end tag for
Line 3
Dim tagData2 As String 'To store the data between start and end tag for
Line 4
Dim tagDataLen 'To store the tag data length
Dim stLen As Integer 'To store the length of line
Dim i As Integer
Dim count 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 & "\TextFileOne.txt"
'Open file.
Set ts = fso.OpenTextFile(strFilePathAndName)
'Loop while not at the end of the file.
'Read line by line.
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
stLen = Len(strLine)
If LineNo = 1 Then
'The code next reads whatever after username: in the line
strUN = Mid(strLine, 10)
Debug.Print strUN
ElseIf LineNo = 2 Then
'The code next reads whatever after password: in the line
strPW = Mid(strLine, 10)
Debug.Print strPW
ElseIf LineNo = 3 Then
'Find the index of first occurence of >
'whose ASCII value is 62
For i = 1 To stLen
If Asc((Mid(strLine, i, 1))) = 62 Then
gtPos = i
Exit For
End If
Next i
'Find the index of second occurence of < in the line
'whose ASCII value is 60
count = 0
For i = 1 To stLen
If Asc((Mid(strLine, i, 1))) = 60 Then
count = count + 1
If count = 2 Then 'found second < in the line
ltPos = i
Exit For
End If
End If
Next i
tagDataLen = ltPos - gtPos - 1
tagData1 = Trim(Mid(strLine, (gtPos + 1), tagDataLen))
Debug.Print tagData1
ElseIf LineNo = 4 Then
'Find the index of first occurence of > in the line
'whose ASCII value is 62
For i = 1 To stLen
If Asc((Mid(strLine, i, 1))) = 62 Then
gtPos = i
Exit For
End If
Next i
'Find the index of second occurence of < in the line
'whose ASCII value is 60
For i = 1 To stLen
If Asc((Mid(strLine, i, 1))) = 60 Then
count = count + 1
If count = 2 Then 'found second < in the line
ltPos = i
Exit For
End If
End If
Next i
tagDataLen = ltPos - gtPos - 1
tagData2 = Trim(Mid(strLine, (gtPos + 1), tagDataLen))
Debug.Print tagData2
End If
Loop
'Assuming 4 lines in your text file and having read all four lines
'and stored values in variables, now you can insert the values
'in Access table.Suppose the field names in the table are
'UserName,Password,Tagdata1 and Tagdata2 and all are text data type
'then use the following code.After testing in immediate window
'you can uncomment and use it.
'Also remember to comment or remove all lines Debug.Print before put to
actual use.
' rst.addnew
' rst!UserName = strUN
' rst!Password = strPW
' rst!tagdata1 = tagdata1
' rst!tagdata2 = tagdata2
' rst.update
' rst.close
' set rst = nothing
End Sub
'----------------------------------------------------
Public Sub ReadMultipleTextFiles()
Dim fso As New FileSystemObject
Dim ts As TextStream
Dim strLine As String
Dim strFileFrom As String
Dim strFromDir As String
Dim f As File
Dim fromFol As Folder
'Change the directory path as appropriate in your code
strFromDir = "C:\Files\FilesFromDir"
Set fromFol = fso.GetFolder(strFromDir)
'Open files to be read one by one, read and insert in Access table
For Each f In fromFol.Files
'read only files with extension .TXT
If Right(f.Name, 4) = ".TXT" Then
strFileFrom = strFromDir & "\" & f.Name 'Full path of the File
to be read
'Open the file for reading
Set ts = fso.OpenTextFile(strFileFrom)
'Loop while not at the end of the file. i.e. read line by line
Do While Not ts.AtEndOfStream
strLine = ts.ReadLine
'From here the code remains the same as given in the
'procedure ReadSingleTextFile
'While closing the recordset variable it should be outside
'the loop as shown below.Otherwise only one file data will be
'added and in the next file reading Error will be thrown.
'To use the code in ReadSingleTextFile you can either declare
all
'variables in this procedure and start pasting the code as it
is
'from the next line
'OR call it as a procedure by making some changes in that
file
'as regards dirPath, FilePathAndName,etc.
'I will leave it to you as a hands on session.
Loop
'Close the text stream object so that it can open other files
afresh
'for reading in the next cycle.
ts.Close
End If
Next
' rst.close
' set rst = nothing
End Sub
'======================= END CODE =====================================
Good Luck,
Surendran
