How to process file data into Access

This is a non-standard format so you won't be able to use any of the built-in import methods.  You will need custom code.

The format of this file is really sloppy.  There are spaces after some field names which will interfere with matching them with a column name since  spaces are also characters.  You can use the Trim() function to strip off leading and trailing spaces and that will also help with the data part of the records.  Field values can also contain comas and that will interfere with using the Split() function so since there is in theory only a single data field, you can probably use InStr() instead of Split() (which is what the attached procedure) to find out where the data starts.

I've included some code that imports an EDI file  EDI (Electronic Data Interchange) is a transaction format standard agreed to by many companies that allows them to transfer data in a pre-defined, standardized format.  The sample code is for an 835 transaction which is used to communicate medical billing information between payers (insurance companies) and providers (doctors, hospitals, etc)

The code is obviously not what you need but it does include all the necessary elements to show you how to import a non-standard file format and load the data into a table.  There are comments to explain some of the more strange conditions the code needed to deal with.

Public Function Import835(frm As Form) As Boolean
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim qd As DAO.QueryDef
Dim rs As DAO.Recordset
Dim td835 As DAO.TableDef
Dim rs835 As DAO.Recordset
Dim str As String
Dim FileNum As Long
Dim RecCount As Long
Dim ary1 As Variant
Dim aryColumns As Variant
Dim aryPCode As Variant
Dim sTranCode As String
Dim sRecBlob As Variant
Dim i As Long
Dim i2 As Long
Dim strFullFileName As String
Dim saveEMS As Variant
Dim saveSendID As Variant
Dim saveReceiveID As Variant

Dim HoldReceivedID As Variant
Dim HoldTransmittalNum As Variant
Dim HoldRecNum As Variant
Dim HoldErrGroup As Variant
Dim HoldErrCode As Variant
Dim HoldEMS As Variant
Dim HoldFromDT As Variant
Dim HoldToDT As Variant
Dim HoldpCode As Variant
Dim HoldUnits As Variant
Dim HoldBilledAmt As Variant
Dim HoldPaidAmt As Variant
Dim HoldCoPay As Variant
Dim HoldRec As Boolean
Dim HoldInvNum As Variant
Dim FoxProFlg As Integer

Dim TranCount As Long
Dim LineCount As Long

    On Error GoTo Err_Proc

    Set db = CurrentDb
    Set td = db.TableDefs("tbl835BlobImport")
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    Set td835 = db.TableDefs("tbl835Raw")
    Set rs835 = td835.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    
    'Open FileNum For Input As #1
    strFullFileName = frm.txtFileName
    FileNum = FreeFile
    Close FileNum
    Open strFullFileName For Input As FileNum
    
    HoldRec = False
    TranCount = 0
    LineCount = 0
    
    Do Until EOF(1)                         'import to tbl835BlobImport
        Line Input #FileNum, str            'because records are not correctly delimited, entire file is read as a blob
        ary1 = Split(str, "~")              'split blob into records
        For i = 0 To UBound(ary1)           'for each record in blob
            sRecBlob = ary1(i)
            LineCount = LineCount + 1
            If LineCount Mod 500 = 0 Then
                DoEvents
                frm.txtTranCount = LineCount
                frm.Repaint
            End If
            If sRecBlob & "" = "" Then
            Else
                sTranCode = Left(sRecBlob, InStr(sRecBlob, "*") - 1)
                aryColumns = Split(sRecBlob, "*")
            'add record to  table
                    rs.AddNew
                        ''rs!ImportBatchID = frm!txtImportBatchID
                        If sTranCode = "NM1" Then
                            If aryColumns(1) = "QC" Then
                                saveEMS = aryColumns(9)
                            End If
                        End If
                        If sTranCode = "CLP" Then
                            saveSendID = aryColumns(1)
                            saveReceiveID = aryColumns(7)
                            rs!SendID = saveSendID
                            rs!ReceiveID = saveReceiveID
                        End If
                                               
                        rs!TranCode = sTranCode
                       
                        'can't populate EMS on CLP because we don't get the correct EMS until after writing the CLP record.  Update later with query.
                        If sTranCode = "CLP" Or sTranCode = "PLB" Or sTranCode = "SE" Or sTranCode = "GE" Or sTranCode = "IEA" Then  'don't populate EMS for these codes
                        Else
                            rs!ems = saveEMS
                            rs!SendID = saveSendID
                            rs!ReceiveID = saveReceiveID
                        End If
                        
                        For i2 = 1 To UBound(aryColumns)
                            rs.Fields(i2 + 4) = aryColumns(i2)
                        Next i2
                        rs!ImportBatchID = frm!cboImportBatchID
                    rs.Update
                    RecCount = RecCount + 1
                    ''''' save 835 rec
                    Select Case sTranCode
                        Case "CLP"
                            If HoldRec = True Then
                                GoSub Write835
                            End If
                            GoSub Clear835
                            HoldRec = True
                            HoldReceivedID = aryColumns(7)
                            HoldInvNum = aryColumns(1)
                            If Mid(aryColumns(1), 5, 1) = 0 Or Mid(aryColumns(1), 5, 1) >= 5 Then 'Access
                                HoldTransmittalNum = Left(aryColumns(1), 6)
                                HoldRecNum = Mid(aryColumns(1), 7)
                            Else
                                HoldTransmittalNum = Left(aryColumns(1), 5)         'FoxPro
                                HoldRecNum = Mid(aryColumns(1), 6)
                            End If
                            
                            HoldBilledAmt = aryColumns(3)
                            HoldPaidAmt = aryColumns(4)
                            HoldCoPay = IIf(aryColumns(5) & "" = "", Null, aryColumns(5))
                        Case "NM1"
                            If aryColumns(1) = "QC" Then
                                HoldEMS = aryColumns(9)
                            End If
                        Case "DTM"
                            Select Case aryColumns(1)
                                Case "232"
                                    HoldFromDT = CDate(Left(aryColumns(2), 4) & "/" & Mid(aryColumns(2), 5, 2) & "/" & Right(aryColumns(2), 2))
                                Case "233"
                                    HoldToDT = CDate(Left(aryColumns(2), 4) & "/" & Mid(aryColumns(2), 5, 2) & "/" & Right(aryColumns(2), 2))
                            End Select
                        Case "SVC"
                            aryPCode = Split(aryColumns(1), ":")        'this field has multiple parts separated by colons.  The second one is pcode
                            HoldpCode = aryPCode(1)
                            HoldUnits = aryColumns(5)
                        Case "CAS"
                            HoldErrGroup = aryColumns(1)
                            HoldErrCode = aryColumns(2)
                    End Select
                End If
        Next i
    Loop
        
    If HoldRec = True Then
        GoSub Write835          'write last record
    End If
    
    Set qd = db.QueryDefs!q835UpdateCLPwithEMS  'populate EMS on CLP records
    qd.Execute (dbSeeChanges)
    
    ''MsgBox "Import Complete", vbOKOnly
    
    'close files and release
    rs.Close
    Set rs = Nothing
    Close FileNum
    Debug.Print RecCount
    frm.txtTranCount = TranCount
    frm.txtLineCount = LineCount
    frm.Repaint
Exit_Proc:
    On Error GoTo 0
    Exit Function

Write835:
    TranCount = TranCount + 1
    If HoldRecNum & "" = "" Then
        HoldRecNum = "000000"
    End If
    If HoldToDT >= #12/1/2014# Then     'ignore older dates.  They are handled in FoxPro
        rs835.AddNew
            rs835!BatchID = frm.cboImportBatchID 'frm!txtImportBatchID
            rs835!ICN = HoldReceivedID
            rs835!TransmittalNum = HoldTransmittalNum
            rs835!RecNum = Left(HoldRecNum, 6)
            rs835!ResubmitCD = Right(HoldRecNum, 2)
            rs835!ErrGroup = HoldErrGroup
            rs835!ErrCode = HoldErrCode
            rs835!ems = HoldEMS
            rs835!FromDT = HoldFromDT
            rs835!ToDT = HoldToDT
            rs835!pCode = HoldpCode
            rs835!Units = HoldUnits
            rs835!BillAmt = HoldBilledAmt
            rs835!PaidAmt = HoldPaidAmt
            rs835!copay = HoldCoPay
            rs835!PaidCD = IIf(HoldBilledAmt = HoldPaidAmt, 1, IIf(HoldPaidAmt = 0, 3, 2))
            rs835!InvNum = HoldInvNum
            rs835!RunType = Forms!frmReceive835!txtRunType
            If Len(HoldInvNum) < 14 Then
                FoxProFlg = True
            Else
                FoxProFlg = False
            End If
            rs835!FoxPro = FoxProFlg
        rs835.Update
    End If
    Return
    
Clear835:
    HoldReceivedID = Null
    HoldTransmittalNum = Null
    HoldRecNum = Null
    HoldErrGroup = Null
    HoldErrCode = Null
    HoldEMS = Null
    HoldFromDT = Null
    HoldToDT = Null
    HoldpCode = Null
    HoldUnits = Null
    HoldBilledAmt = Null
    HoldPaidAmt = Null
    HoldCoPay = Null
    Return
    
Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import835 of Module modEDI"
    End Select
    Resume Exit_Proc
    Resume
End Function

Select allOpen in new window

Pat's code should get you started, but before going there - are you sure your ERP system can't give a more standard format, like a CSV dump? I I work with EDI data every day, and that is not a format I've seen before.

This only takes a query to filter the data, if you link the file renamed as a text file (AutoDeereGTL-1.txt):

SELECT 
    [AutoDeereGTL-1].FieldName, 
    [Data1] & (" "+[Data2]) AS Data
FROM 
    [AutoDeereGTL-1]
WHERE 
    Asc([FieldName]) <> 42;

Select allOpen in new window

and a few lines of code:

Public Function FillTable()

    Dim db          As DAO.Database
    Dim rsSource    As DAO.Recordset
    Dim rsTarget    As DAO.Recordset
    
    Set db = CurrentDb
    Set rsSource = db.OpenRecordset("AutoDeereGTL")
    Set rsTarget = db.OpenRecordset("AutoDeere")
    
    rsTarget.AddNew
    While Not rsSource.EOF
        Select Case rsSource!FieldName.Value
            Case "ShipDate"
                rsTarget(rsSource!FieldName.Value).Value = CDate(Format(rsSource!Data.Value, "@@@@\/@@\/@@"))
            Case "Weight"
                rsTarget(rsSource!FieldName.Value).Value = Val(rsSource!Data.Value)
            Case Else
                rsTarget(rsSource!FieldName.Value).Value = rsSource!Data.Value
        End Select
        rsSource.MoveNext
    Wend
    rsTarget.Update

End Function

Select allOpen in new window

If you don't have the source table, it can be created by this tiny function:

Public Function CreateTable()

    Dim db          As DAO.Database
    Dim rs          As DAO.Recordset
    Dim td          As DAO.TableDef
    Dim fd          As DAO.Field
    
    Set db = CurrentDb
    Set rs = db.OpenRecordset("AutoDeereGTL")
    Set td = db.CreateTableDef("AutoDeere")
    
    While Not rs.EOF
        Select Case rs!FieldName.Value
            Case "ShipDate"
                Set fd = td.CreateField(rs!FieldName.Value, dbDate)
            Case "Weight"
                Set fd = td.CreateField(rs!FieldName.Value, dbCurrency)
            Case Else
                Set fd = td.CreateField(rs!FieldName.Value, dbText)
        End Select
        td.Fields.Append fd
        rs.MoveNext
    Wend
    db.TableDefs.Append td
        
End Function

Select allOpen in new window

See the attached demo, which also holds the import specification.
lwl.accdb

Here is complete tutorial on the Implementation using SSIS.
If you want to avoid the SSIS overhead you could develop a small .NET application using the FileSystemWatcher class