> 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
>
> Ocean wrote:
> >I have text file that has data a bit like this:
> >
> >username: john smith
> >password: happy
> ><text_start1>This is a sample<text_end1>
> ><text_start2>This is a sample<text_end2>
> >
> >I want to :
> >a) import the first 2 fields into a access table (eg "john smith" is
> >imported into the 'username' field in the table
> >b) import the text "This is a sample" into a field in a table - but to
> >delete the text within the <> brackets on each side.
> >
> >Can anyone offer some advise or sample way of doing this with VBA.
> >I will be doing this on a lot of files on a regualr basis, so would like to
> >automate as much as possible.
> >Thanks.
>
> --
> Message posted via
http://www.accessmonster.com>