[AccessD] Import Fixed Length Data

I think you may have to read each record and parse, if there are field 
delimiters
you can use the split function. The ADO text driver might work but it 
would be convoluted
because it expects column header info at beginning.
You might be able to do with schema.ini playing with
FixedFormat option  Only used when the Format is FixedLength,
this can be set to one of the following values: RaggedEdge or 
TrueFixedLength.
RaggedEdge allows rows to be terminated with a Carriage Return character.
TrueFixedLength requires each row to be an exact number of characters,
and any Carriage Return characters not at a row boundary are assumed to 
be embedded in a field.
 
One of these methods will work

Public Sub ReadFile(ByVal sFileName As String, ByRef sContents As String)
' Dump XML  String to File for debugging
Dim strLine As String
Dim intLine As Long
Dim fhFile As Integer
    intLine = 0
    sContents = ""
    fhFile = FreeFile
   ' Debug.Print "Length of string=" & Len(sContents)
    Open sFileName For Input As #fhFile
Do While Not EOF(1) ' Loop until end of file.
    Input #1, strLine    ' Read data
    intLine = intLine + 1
    sContents = sContents & strLine
    'Debug.Print sContents  ' Print data to Debug window.
Loop
Close #fhFile    ' Close file.

    Debug.Print "Input File" & sFileName & " lines=" & intLine
End Sub
or

Sub ImportFile(strFileName As String)

   Dim rs As Recordset
   Dim strComplete As String
   Dim strLine As String
   Dim fNum As Integer   'for file I/O

   'open rs on table where you want to add fields
   Set rs = CurrentDb.OpenRecordset("tblYourTableHere", dbOpenDynaset)

   'Open data file
   fNum = FreeFile   'file handle for I/O
   Open strFileName For Input As #fNum

   'Data on 3 lines?
   'read a line
   Line Input #1, strLine
   strComplete = strLine
   Line Input #1, strLine
   strComplete = strComplete & strLine
   Line Input #1, strLine
   strComplete = strComplete & strLine

   'we expect to enter this loop with the first 3 lines read... a 
complete record
   'just parse the lines into the proper recordset fields
   Do
      rs.AddNew
      rs!CustNo = left(strComplete, 9)
      rs!CustName = Mid(strComplete, 10, 40)
      rs!CustCity = right(strComplete, 10)
      rs.Update

      'are we at the end of the file?
      If EOF(fNum) Then
         Exit Do
      End If
      'well, we're not at the end of the file so must have more data
      'read in next 3 lines of data and loop
      Line Input #1, strLine
      strComplete = strLine
      Line Input #1, strLine
      strComplete = strComplete & strLine
      Line Input #1, strLine
      strComplete = strComplete & strLine
   Loop
ProcExit:
   Close #fNum   ' Close file.
   'close and set objects to nothing
   rs.Close
   Set rs = Nothing
End Sub


Reuben Cummings wrote:

>I have a LONG text file that I need to extract into an Access table.
>
>Every record is five lines so the Access import system will not work.  The
>field descriptions are also part of the data which is no big deal.  I can
>import the field descriptions and then delete those fields later.
>
>What I am after is...Does anyone have a neat fixed-length import system that
>will handle multiple lines per record?
>
>If not, how do I jump to the next line after I finish with one?
>
>Can I read one entire line into a string and then manipulate that?  Or
>analyze it for certain data in order to handle the entire line differently?
>
>Thanks.
>
>Reuben Cummings
>GFC, LLC
>812.523.1017
>
>
>
>
>  
>

-- 
Marty Connelly
Victoria, B.C.
Canada