How to move throug a fixed length text file like a recordset

[Swi]

Is there any way to move through a text file like you do with a recordset?

Ex. - Rs.MoveLast
Rs.MoveFirst
Rs.Move

I know you can read text files using ADO that are delimited and accomplish this but is there a way to read in a file that has no delimiters whatsoever except a CRLF? My goal is to be able to read in text files that are both line sequential (have CRLF at the end) and non-line sequential files (do NOT have a CRLF at the end) into an interface that can display the text to a user so that they can modify it as well as navigate through the file move one or even 1000 records at a time. Please give your comments. Thanks.

Swi

 

[robk54]

I would setup a class referencing FileSystemObject & mimic the ADO properties & methods.

cMyTxtAdo.open
cMyTxtAdo.movefirst
... etc ...

Obviously you are then limited to single user locking.

Hope this helps

Regards

Rob

 

[Swi]

I agree with the idea of using the FileSystemObject but its fuctionality is only vaguely similar to that of a recordset. For example, in a recordset to can .MovePrevious but there is no command to do this in the FileSystemObject. Any ideas on how to handle this?

Swi

 

[CajunCenturion]

Have you tried access the Text file using ADO (and its recordsets) with the Text Driver?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[MichaelRed]

It may be useful to simply read the entire file into a single string, use code to create an array of the structure desired and populate it. From that point, wheqther to use simple array processing or create an ADO recordset would depend on prefernces, however either would be fairly easy.

There ARE some examples of this technique in these fors (Ms. A. & VB), but either 'Advanced' search is advanced only into senility -or I am- as simplistic forays into looking for routines returns no useful threads. I have posted some, using [LOF & FreeFile & Space], but various combinations of these in the keyword list return not anything which I recognize.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Swi]

When I try running the below code I get the following:

[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only. Microsoft OLE DB Provider for ODBC Drivers 16 Import


Any ideas, I checked the database and it is not read-only.


Private Function ReadTextFile(ByVal sPath As String, ByVal sFile As String) As ADODB.Recordset
On Error GoTo fix_err
Dim Rs As New ADODB.Recordset
Dim sconn As New ADODB.Connection
sconn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & sPath & _
";Extensions=asc,csv,tab,txt,dat;HDR=YES;Persist Security Info=False"
Rs.Open "Select * From " & sPath & sFile, sconn, adOpenStatic, adLockReadOnly, adCmdText
Set ReadTextFile = Rs
Set Rs = Nothing
Exit Function
fix_err:
Debug.Print Err.Description + " " + _
Err.Source, vbCritical, "Import"
Err.Clear
End Function



Swi

 

[MichaelRed]

I looked into some of my tret/dev MDBs and found this part of the process.

Public Function basGrabFile(FilIn As String) As String

    'Michael Red    3/3/2003
    'Sample Usage:  ? basGrabFile("C:\MsAccess\DrawArcsInVB.Txt")
    'Note the Arg [FilIn] is the FULLY QUALIFIED PATH of the Source _
     and the entire text is returned to the caller [prog | procedure]

    Dim MyFil As Integer

    Dim MyTxt As String
    Dim MyPrts() As String
    Dim MyPrtRec() As String

    'Just grab the Stuff
    MyFil = FreeFile

    Open FilIn For Binary As #MyFil

    MyTxt = String(LOF(MyFil), " ")
    Get #MyFil, 1, MyTxt

    Close #MyFil

    basGrabFile = MyTxt

End Function

of course, it is somewhat obscure for some so I also generated the demo function:

Public Function basTestGrabFile(FileIn As String) As Long

    'Example Usage
    '? basTestGrabFile("C:\My Documents\Fleas.Txt")
    '9

    Dim MyBigStr As String
    Dim MyLines() As String

    MyBigStr = basGrabFile(FileIn)
    MyLines = basSplit(MyBigStr, vbNewLine)

    basTestGrabFile = UBound(MyLines) + 1

End Function

which nicely seperates a Cr / Lf file into elements of an array. To see the array, of course, it is necessary to do (just a little) bit of work. Addittional effort (not necessarily 'little') is required to then transmoggify hte individual elements of hte line array into the multidimensional array of lines and fields ... but this part requires some knowledge of the 'structure' in the lines, so not discussed here.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[Swi]

Thank you for the posts. I totally agree with what you are doing in the above posts but VB's Open statement does not handle files over 2 gig in file size if I am correct. That is easily corrected using the FileSystemObject. We have a program now that does no loading whatsoever and seems to just read the file in line by line. We do not want to pay the additional support for the program so I get the task of trying to recreate it. Your above code works great but is still loading the data file into a string or array. Also, the files can sometimes not contain a CRLF and the user would have to enter the record size.

Swi

 

[CajunCenturion]

Swi - I noticed that in the rs.open statment you're using a ReadOnly lock type. Is this correct?

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[RichieSimonetti]

listening...

 

[Swi]

Yes. I did not see that. I will change and try again. Thanks.

Swi

 

[MichaelRed]

In any case / instance of "loading" the file, you will need to eventually have SOME clus re the structure. The function basGrabFile is more or less deliberatly limited to the retrieval. In many (nost) instances it is intended to be used "as is", and it returns the entire file as a single string to the calling procedure. The original intent is to utilize structured programming approachhe, and process the returned string according to the rules which apply to the SPECIFIC file. For fixed width text files, one obviously needs to know the structure to retrieve the individual components (fields) -regardless of wheather the record is even delimited. As stated in the post, the only point to the basTestGrabFile procedure was to illustate a convenient way to break down the single string into 'records'. The intrinsic function Split can accept ANY delimiter, thus the issue is not wheather the original source is delimited by CrLf or any specific character, or wheather the fields within a record arre likewise delimited (as in CSV fiels), but that is is necessary to KNOW how the original file is structured and deal with the results (return value) of basGrabFile on hte basis of that knowledge.

For VERY large files, a procedure would need to be developed which would at least break down the original source file into string manageable "chunks" (e.g. records?) and return the collection in an appropiate structure. This is, however, no different from any other approach.

You apper to be dis-interested in the approach, so I'll drop out of the thread.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[SmokinWrek]

Swi,

What was your eventual solution to this? I have a similar situation where I want to move randomly through a CRLF terminated data file.

Kevin

 

[Swi]

I used the File System Object. Basically I found out how many records were in the file using something like this:

Set FileSystemObject = CreateObject("Scripting.FileSystemObject")
Set FileObject = FileSystemObject.GetFile(txtInputFile.Text)
Set TextStream = FileSystemObject.OpenTextFile(txtInputFile.Text, ForReading, False, TristateFalse)
NumOfRecords = FileObject.Size / RecordSize

Then I navigated through the file by using:

TextStream.Skip CurrentPosition * RecordSize

This may not be the best way but it worked for my purposes.



Swi