Public Sub ImportTextData()
' this routine demonstrates importing a text file which includes a row for each field of data
' the sample file this program reads looks like this:
'   Product = 12
'   Qty = 5
'   Cost = 14.75
'   Product = 13
'   Qty = 6
'   Cost = 0.98
'   Product = 15
'   Qty = 120
'   Cost = 12
'
' code developed for UA (www.utteraccess.com by 'Clippit'
  
  Dim db As Database              ' refernece to current database
  Dim rsOut As Recordset          ' recordset to output data to table
  Dim strRow As String            ' row of data read from input file
  Dim strField As String          ' field identifier part of input
  Dim strData As String           ' data part of input
  Dim intSeparatorPos As Integer  ' position in input of separator character
  Dim boolStartOfData As Boolean  ' flag to identify first row processing
  Dim boolAtEOF As Boolean        ' flag to identify that last row has been reached
  
  Const strcInputFile As String = "c:\temp\NarrowProductData.txt" ' name of the text file
  Const strcOutputTable As String = "WideProductData" ' name of the table to output to
  
  On Error GoTo ErrorHandler
  
  Set db = CurrentDb
  
  Open strcInputFile For Input As #1 ' open input text file
  
  ' delete current records from table- without this lines will be appended
  db.Execute "delete * from " & strcOutputTable
  
  Set rsOut = db.OpenRecordset(strcOutputTable) ' open output table
  
  boolStartOfData = True
  boolAtEOF = False
      
  Line Input #1, strRow ' read first line from inout file
  Do Until boolAtEOF
      ' if current record is the last one then set a flag so that
      ' this is last time through loop
      If EOF(1) Then boolAtEOF = True
      
      ' separate two parts of the input line (field identifier and data)
      intSeparatorPos = InStr(1, strRow, "=")
      If Nz(intSeparatorPos) > 1 Then ' if no separator found do not process input line
          strField = Left(strRow, intSeparatorPos - 1)
          strData = Mid(strRow, intSeparatorPos + 1)
          Select Case strField ' decide what to do with data depending on field identifier
          Case "Product"
              ' the product field comes first, and indicates a new group of data
              '   (a row in the ouptut)
              If Not boolStartOfData Then
                  rsOut.Update ' save the previous record to the table
              Else
                  boolStartOfData = False
              End If
              rsOut.AddNew ' start a new record
              rsOut!Product = CLng(strData)
          Case "Qty"
              rsOut!Qty = CInt(strData)
          Case "Cost"
              rsOut!Cost = CCur(strData)
          Case Else
              MsgBox ("Unexpected data found in output file")
          End Select
      End If
      If Not boolAtEOF Then
          Line Input #1, strRow
      End If
  Loop
  If Not boolStartOfData Then
      rsOut.Update 'save the last record
  End If
  
  Close #1
  rsOut.Close
  
  Set rsOut = Nothing
  Set db = Nothing
EndSub:
  Exit Sub
  
ErrorHandler:
  Select Case Err.Number
  Case 53 ' file not found
    Debug.Print MsgBox("An error has occurred importing data:" & vbCrLf & _
        "The input file " & strcInputFile & " cannot be found", vbCritical, "Error")
  Case 55 ' file already open
    Close #1
    Resume
  Case Else
  Debug.Print MsgBox("An error has occurred importing data:" & vbCrLf & _
      Err.Number & " " & Err.Description, vbCritical, "Error")
  End Select
  
End Sub