Streamed Input Data Translation/Conversion 1

[JohnHarkins]

Hi!

I've got a couple of DOS files that are "sort of" text. Some of the files contain LF (or is it CR?) without the usual CR-LF combination. Others contain just a character that marks the fields in the records and it's up to me to count the fields to determine when a new record occurs. This is all shades of mainframe data encoding, but that's life.

Is there a convenient way for Access 2000 to read the stream through VBA and let me dynamically make the appropriate insertions of CR-LF before the data are parsed into fields of a table?

I've searched the TransferText stuff, but I'm left confused.

Thanks, John Harkins

 

[beetee]

This code isn't especially good, or especially convenient, but it could get you started processing a text file using VB.

I don't believe the intrinsic access import functions would be able to handle your problem.



Sub ProcessRecord(FieldArray() As String, FieldCount As Integer)
If FieldCount > 0 Then
Dim counter As Integer
For counter = 0 To FieldCount - 1
Debug.Print FieldArray(counter) & ", ";
Next counter
Debug.Print
End If
End Sub


Sub ReadFile(FileName As String, FieldsPerRecord As Integer)
Dim FieldArray(0 To 127) As String
Dim FieldCount As Integer
FieldCount = 0
Dim CurField As String

Dim MyHandle As Integer
MyHandle = FreeFile
Open FileName For Input As #MyHandle ' Open file.
Dim CurChar As String
Do While Not EOF(1) ' Loop until end of file.
CurChar = Input(1, #1) ' Get one character.
Select Case CurChar
Case ","
FieldArray(FieldCount) = CurField
FieldCount = FieldCount + 1
If FieldCount = FieldsPerRecord Then
ProcessRecord FieldArray, FieldCount
FieldCount = 0
End If
CurField = ""
Case Chr(10)
FieldArray(FieldCount) = CurField
FieldCount = FieldCount + 1
ProcessRecord FieldArray, FieldCount
FieldCount = 0
CurField = ""
Case Chr(13)
FieldArray(FieldCount) = CurField
FieldCount = FieldCount + 1
ProcessRecord FieldArray, FieldCount
FieldCount = 0
CurField = ""
Case Else
CurField = CurField & CurChar
End Select
Loop
Close MyHandle ' Close file.
End Sub

 

[lameid]

The MSKB USED TO HAVE an article with sample code to resolve this problem. Unfortunately, I've changed jobs since I've done it and I don't have my code. Although beetee's code is functional, you have to write a procedure for each file you run across. It is possible to use instr to find each Line Feed (ASCII 10) or Carriage Return (ASCII 13) and then write out sucessive lines to a new file. You could then import that file using a specification and transfertext. Just another way to skin the same cat. I really wish I had that sample code or time to write it.

 

[MichaelRed]

w/o some concept of the structure(s) and perhaps delimiters, I am definitly NOT going into the quagmire of advice on the parseing of some vague blob of ASCII (perhaps mixed with EBDIC?) randomness.

On the other hand, ANY process which needs to process entire text files can reasonably benefit from the LOSS of reading the 'sttuuuffffffff' piecemeal (widely known to be one of the sloooooooower operations available in programming land).

On the other hand (avoiding the question of HOW to PARSE what), the following procedure skeleton (missing the fleshy elements like error trapping ... ) WILL do the grabbing of most any thing and return it to the where-ever land for those who DO want / need to play about w/ the parseing of strange stuff - and can concentrate on just the parseing, as it is surely gooing to work MUCH more quickly with the whols magillia in 'memory' as a lump. After all, the same parse ops will work on the same strings, and one MIGHT even venture into the latest (and POSSIBLY greatest text processing functions (see SPLIT, JOIN, REPLACE nad their ilk in your local help files).

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

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[lameid]

I take it back. Upon closer examination, Beetee's solution requires you to parse up the 'field' or record but will handle either case of having CR or LF.

MichaelRed's mention looks familiar to me. If the low level file handling allows inputing based on a LF or CR then you could input the file line by line and write it out line by line to get the CRLF you need. You might have to concatenate in the CR or LF. I think you do and if my memory serves, Access doesn't care whether it is LFCR or CRLF.

 

[beetee]

Rgarding Michael Red's well-taken comment on reading the input piecemeal; the effectiveness of the approach is dependant on the quality of buffering that the intrinsic function Input provides.

The reason I provided a piecemeal input solution was I was unsure how a line input function would perform with a line delimited with LF's, or delimited with CR's, and was too lazy to write a C++ program to generate test cases. Sometimes actual life intrudes.

Additionally, one could pass in a field delimiter (or set of delimiters) to make the code more generic.

The goal here was to provide a quick answer for a specific problem.

The generic aspect of the code is the Process record function. It can be made table driven and handle all sorts of situations. That solution seems overly complex for this problem.