Solved Read Text File Into Table Issue

[Cotswold]

Hello All, I need to read various plain TEXT files into an Access Table but am unable to get it to read the lines separately. I know that using the Access TransferText function will do the job but there will be different text files that I need to handle and I would rather decide in the code how to handle them, instead of creating a new Specification for each new file that arises. Which will not be practical in this instance.

I have extracted the code I have an issue with. Is anyone able to tell me why the following code will only import all of the contents of a text file into one LargeText field rather than each line into separate fields?

Line 18 fails with Error 3163 Field too small to accept data. (with lengths less than 60 characters each in the text file)
Line 19 works as all the lines in PlainText.txt File are concatenated and saved in the Table T1030 FieldMemo which is a LongText type.
The text lines in the text file will not be longer than 128 and there will usually be from about 20 to up to 150 lines of text to read in.

Private Sub btnImportTXT_Click()
'
001 ' Access Table
002 Dim MyDb As DAO.Database
003 Dim MyRS As DAO.Recordset
004 Dim XX As Integer
005 Set MyDb = CurrentDb
006 Set MyRS = MyDb.OpenRecordset("T1030")
007 '--------- Text txt
008 Dim strFileName As String: strFileName = "c:\Path\Path\PlainText.txt" ' hard coded in example
009 Dim strTextLine As String
010 Dim iFile As Integer: iFile = FreeFile
011 '
012 Open strFileName For Input As #iFile
013 XX = 1
014 Do Until EOF(iFile)
015     Line Input #iFile, strTextLine
016     '
017     MyRS.AddNew
018     MyRS("Field128") = Trim(strTextLine) ' <<< if this line is used it fails with: Error 3163 Field too small
019     'MyRS("FieldMemo") = Trim(strTextLine) ' <<< this takes every line and concatenates them into one LargeText Field
020     MyRS.Update
021     'MsgBox strTextLine
022     XX = XX + 1
023     Loop
024     MsgBox XX ' <<<< Only ever as high as 2 indicating the Do Until EOF()/Loop is passed through only once
025     Close #iFile
026     MyRS.Close
027     MyDb.Close
'
End Sub   ' btnImport()

***********

 

[Uncle Gizmo]

I had problems importing CSV data a while back.

I found changing the database text fields to memo fields solved some problems.

No idea if it's relivant to your situation, just thought I should mention it ...

 

[jdraw]

What is/are your end of line character(s)?

 

[Cotswold]

I had problems importing CSV data a while back.

I found changing the database text fields to memo fields solved some problems.

No idea if it's relivant to your situation, just thought I should mention it ...

Thanks Uncle G, but I prefer to avoid LongText or Memo fields if at all possible

 

[jdraw]

you can only have 255 chars in a short text field.

 

[Cotswold]

you can only have 255 chars in a short text field.

I have that set to 128, so no probs

 

[Cotswold]

What is/are your end of line character(s)?

I guess in the main it will be a CR, CHR(13) and it is in my test files. But it could be LF, Chr(10) I suppose, Either is Ok

 

[jdraw]

Do you have some test data you can post and share?
What is the design of "T1030"?

 

[jdraw]

I just tested a small text file using the logic in your code.
I set up field128 (128 chars short text( in table T1030. My line terminator is Cr Lf
Here's a png after getting the 3163 error

 

[Cotswold]

I just tested a small text file using the logic in your code.
I set up field128 (128 chars short text( in table T1030. My line terminator is Cr Lf
Here's a png after getting the 3163 error

View attachment 96832

That is the same err as I have. I'll send a copy of a database and text file on the next response. The problem is that in the loop it is not looking line by line but gathers every line in the text file.

 

[Cotswold]

I attach a database with one table and one form, plus a text file in a zip file. The Text File will have to be in the C:\Temp\ folder unless you amend the code to the button's ClickEvent(). What I want to do is to put each of the lines from the Text File into a separate field in the Field128 in the T1030 table. All I can do is slam the lot into the FieldMemo. I don't even need a Do Until/Loop if I wanted to do that.

Which begs the question: What is the point of the Do Until EOF()/Loop ?
I could remove the loop, load them all up from the Text File and string handle each line into separate records in the Table...but I don't really want to do it that way.
Regards

 

[Pat Hartman]

You don't need a separate import spec for each file if they are all the same record length.

I imported this file but the record length is 70 because that is the maximum row width in that table. If you want to fix the max at 128, create a file with at least one record that long and import it once to create an import spec - I'm talking about the OLD import spec, not the NEW import spec. Then you can use the same spec for all files with record widths UP TO 128. At some point during the import process press the Advanced button so you can save the spec with a name. That is the name you will use in the TransferText method later.

After the setup import is done, open MSysIMEXColumns and modify the column width from 70 to 128. This is one of the very few MSys tables that allow editing. The columns table lists the column definitions. I didn't make any. I just imported the entire record as one column. The MSysSpecs table is the table that defines each spec. I named the spec Text128 when I made it so that if you have different widths, you can make additional specs.

That allows you to just use TransferText and reference the same spec regardless of the name or location of the file. You can do what you want with the string later.

 

[Cotswold]

Thanks for that Pat but it doesn't explain why the Do/Loop doesn't work. Some files will be CSVs with varying numbers of fields. If I can import from the plain text file I can do the CSV as well. It is just easier to test on a plain file. The routine is all over the place, MS included, as a VBA solution to import/extract text file data and I cannot see why it will not work. As I say, the loop is not reqd to read the data of the whole file into a variable.

 

[Pat Hartman]

Thanks for that Pat but it doesn't explain why the Do/Loop doesn't work.

What are you doing with the data if you don't know what the file contains? This is a really unusual request. Normally, when we import a file, we KNOW what data it contains. You are telling us that somehow, you can figure out what the columns are on the fly? At least with a csv file, you might have column headers in the first row. With a fixed format file, how can you parse it if you don't know exactly what fields it contains and how long each is?

As i mentioned, you can create import specs on the fly. So, if you are getting a layout separately, you can use that layout to create the import spec rather than having to parse the record by itself.

Your text file is not correctly delimited. Here is code I used to handle a file with the same problem. In addition to the delimiter issue, there were multiple record formats. Nothing is easy when your import files are created by idiots. The first few lines of the Do Until should get you started. BIT, the TransferText method correctly identifies each record so you don't have to load the whole file into memory first and then parse it.

Private Sub cmdImport_Click()

    'On Error Resume Next

    Dim db As DAO.Database
    Dim tdH As DAO.TableDef
    Dim rsH As DAO.Recordset
    Dim tdD As DAO.TableDef
    Dim rsD As DAO.Recordset
    Dim HeadId As Long
    Dim increment As Long
    Dim str As String
    Dim LineCount As Integer
    Dim strDate As String
  
    Dim i As Integer
    Dim i2 As Integer
    Dim s As String
    Dim v1 As String
    Dim p1 As Integer, p2 As Integer, p3 As Integer, p4 As Integer, p5 As Integer, p6 As Integer, p7 As Integer
    Dim p8 As Integer, p9 As Integer, p10 As Integer, p11 As Integer, p12 As Integer
    Dim p13 As Integer, p14 As Integer, p15 As Integer, p16 As Integer
  
    Dim ary1 As Variant
    Dim MyLength As Long

On Error GoTo ErrProc
'starting positions for first item instance
    p1 = 65
    p2 = 67
    p3 = 68
    p4 = 69
    p5 = 74
    p6 = 78
    p7 = 80
    p8 = 88
    p9 = 96
    p10 = 97
    p11 = 101
    p12 = 102
    p13 = 111
    p14 = 118
    p15 = 121
    p16 = 124

    Dim strIns As String
    Dim rs As Recordset, strFID As String, strFID2 As String
    Set db = CurrentDb

    Dim fl As String
    fl = GetFile("x")

    strIns = "insert into FileLog (FileName) values ('" & fl & "')"
    db.Execute strIns

    strFID = "select max(ID) from FileLog"
    Set rs = db.OpenRecordset(strFID)
    Do While Not rs.EOF
        strFID2 = rs(0)
        Exit Do
    Loop
    Set rs = Nothing
  
    Set db = CurrentDb()
    Set tdH = db.TableDefs!dbo_Return_Header
    Set rsH = tdH.OpenRecordset
    Set tdD = db.TableDefs!dbo_Return_detail
    Set rsD = tdD.OpenRecordset
  
    Open fl For Input As #1
  
    Do Until EOF(1)
        Line Input #1, str          'because records are not correctly delimited, entire file is read as a blob
        ary1 = Split(str, vbLf)     'split blob into records
        For i = 0 To UBound(ary1)   'for each record in blob
            s = ary1(i)
            MyLength = Len(s)
            LineCount = (MyLength - 64) / 62
            '9  access agency avrid
                v1 = Mid(s, 1, 9)
          
            If v1 = "004158954" Then 'this makes sure it's not a blank or bogus return record
          
        'add record to header table
                rsH.AddNew
                '1  care plan trans type
                    rsH!care_plan_trans_type = Mid(s, 10, 1)
                '10 care plan num
                    rsH!care_plan_num = Mid(s, 11, 10)
                '1  prior auth tran type
                    rsH!prior_auth_trans_type = Mid(s, 21, 1)
                '10 prior auth num
                    rsH!prior_auth_num = Mid(s, 22, 10)
                '12 ems num
                    rsH!ems_num = Mid(s, 32, 12)
    '            '1  first inital
    '                v7 = Mid(s, 44, 1)
                '9  serv prov avrs id
                    rsH!avrsid = Mid(s, 45, 9)
                '2  tot lines
                    rsH!num_lines = Mid(s, 54, 2)
                '3  header error 1
                    rsH!head_err1 = Mid(s, 56, 3)
                '3  header error 2
                    rsH!head_err2 = Mid(s, 59, 3)
                '3  header error 3
                    rsH!head_err3 = Mid(s, 62, 3)
                rsH.Update

''' lookup id using identity
                HeadId = DMax("ID", "dbo_Return_Header")
                increment = 0
              
                For i2 = 1 To LineCount     'LineCount max is 40.  After that client must create new authorization code
                    GoSub AddLine
                Next i2
              
            End If
        Next i
    Loop
      
    Set rsD = Nothing
    Set rsH = Nothing
    Me.FileLog_subform.Requery
    MsgBox "Complete"
  
Exit Sub

AddLine:
        rsD.AddNew
        '-------------------------
        '2  line item num
            rsD!Parent_ID = HeadId
            rsD!line_num = Mid(s, p1 + increment, 2)
        '1  line item pa status
            rsD!line_status = Mid(s, p2 + increment, 1)
        '1  line item trans type
            rsD!line_item_trans_type = Mid(s, p3 + increment, 1)
        '5  proc code
            rsD!proc_code = Mid(s, p4 + increment, 5)
        '4  rev code
            rsD!rev_code = Mid(s, p5 + increment, 4)
        '2  proc code mod list
            rsD!proc_code_mod = Mid(s, p6 + increment, 2)
        '8  from date
            strDate = Mid(s, p7 + increment, 8)
            rsD!from_date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
        '8  to date
            strDate = Mid(s, p8 + increment, 8)
            rsD!to_date = DateSerial(Left(strDate, 4), Mid(strDate, 5, 2), Right(strDate, 2))
        '1  fund source
            rsD!fund_source = Mid(s, p9 + increment, 1)
        '4  freq num
            rsD!freq_num = Mid(s, p10 + increment, 4)
        '1  freq type
            rsD!freq_type = Mid(s, p11 + increment, 1)
        '9  pa requested amount
            rsD!req_amt = Mid(s, p12 + increment, 9)
        '7  pa requested units
            rsD!req_units = Mid(s, p13 + increment, 7)
        '3  detail error code 1
            rsD!detail_err1 = Mid(s, p14 + increment, 3)
        '3  detail error code 2
            rsD!detail_err2 = Mid(s, p15 + increment, 3)
        '3  detail error code 3
            rsD!detail_err3 = Mid(s, p16 + increment, 3)
            rsD!LinkCode = IIf(rsD!rev_code <> "0000", " " & Right(rsD!rev_code, 3) & " ", IIf(rsD!proc_code_mod <> "     ", rsD!proc_code_mod, rsD!proc_code))
          rsD.Update
        
        increment = increment + 62
Return

ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case 75
            GoTo ExitProc
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            GoTo ExitProc
    End Select
End Sub

 

[Isaac]

Dealing with text files can be "fun"....

Without seeing your text file of course, I can only speculate, but feel free to post a sanitized sample if you wish and I will check it out, I am interested in what the problem is.

One quick 'n dirty solution is, read the text slowly into VBA (or however you want) to get the actual character code between lines.
(or whatever your definition of a new line IS - for all I know it could be a newline character or the word Spaghetti - but Access doesn't automatically know that).....and then code to replace all instances of that character with something you KNOW Access will recognize, such as, vbnewline, chr(10), chr(13), or both of the last two at once.

Edit: sorry, just saw your earlier zip attached. When I use an Access database to read that text file in, it has no problem separating it automatically into 6 records. Are you experiencing different behavior when importing with no specs?

Notepad++ does show a linefeed at the end of each line. (Notepad++ is great to analyze 'what' is in a file)

 

[Pat Hartman]

Here's another import with a better format of multiple record types. This is a standard EDI (Electronic Data Interchange) 835 which is used for transmitting medical billing information between providers. EDI is a standard format managed by ONE company and used by thousands to simplify data transmission. Each transaction type is very complicated with multiple records within a single transaction and they cannot be handled by any straight SQL method so it is code and more code to decode the input file. The documentation for each transaction runs about $800 each. Companies are not supposed to give the layouts to their trading partners. Each party is supposed to buy it's own manuals.

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 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("tbl835")
    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)
        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 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.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)
                            HoldTransmittalNum = Left(aryColumns(1), 5)
                            HoldRecNum = Mid(aryColumns(1), 6)
                            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
    
    MsgBox "Import Complete", vbOKOnly
    
    'close files and release
    rs.Close
    Set rs = Nothing
    Close FileNum
    Debug.Print RecCount
    frm.txtTranCount = TranCount
    frm.txtLineCount = LineCount
Exit_Proc:
    On Error GoTo 0
    Exit Function

Write835:
    TranCount = TranCount + 1
    rs835.AddNew
        rs835!BatchID = frm!txtImportBatchID
        rs835!ICN = HoldReceivedID
        rs835!TransmittalNum = HoldTransmittalNum
        rs835!RecNum = Left(HoldRecNum, 5)
        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.Update
    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 Next
End Function

 

[Isaac]

Here's another import with a better format of multiple record types. This is a standard EDI (Electronic Data Interchange) 835 which is used for transmitting medical billing information between providers. EDI is a standard format managed by ONE company and used by thousands to simplify data transmission. Each transaction type is very complicated with multiple records within a single transaction and they cannot be handled by any straight SQL method so it is code and more code to decode the input file. The documentation for each transaction runs about $800 each. Companies are not supposed to give the layouts to their trading partners. Each party is supposed to buy it's own manuals.

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 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("tbl835")
    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)
        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 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.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)
                            HoldTransmittalNum = Left(aryColumns(1), 5)
                            HoldRecNum = Mid(aryColumns(1), 6)
                            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
  
    MsgBox "Import Complete", vbOKOnly
  
    'close files and release
    rs.Close
    Set rs = Nothing
    Close FileNum
    Debug.Print RecCount
    frm.txtTranCount = TranCount
    frm.txtLineCount = LineCount
Exit_Proc:
    On Error GoTo 0
    Exit Function

Write835:
    TranCount = TranCount + 1
    rs835.AddNew
        rs835!BatchID = frm!txtImportBatchID
        rs835!ICN = HoldReceivedID
        rs835!TransmittalNum = HoldTransmittalNum
        rs835!RecNum = Left(HoldRecNum, 5)
        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.Update
    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 Next
End Function

That looks familiar. Not the code, the explanation. I used to work at a healthcare revenue cycle management company and we had a few occasions to write special scripts for their silly file formats...ha....it is really something.

Now of course they COULD potentially re-structure all that in some normalized way, it would just increase the number of separate files and datasets.....but I think medical file formats are one of those things that just got started one day back when, and never got changed because it seems to hard for everyone.

I mean think about it. If I were raised by wolves and had never heard of a database, tables, records, columns, or even seen Excel.....I might say something like this:

• Whenever I type @@, that's the start of a new record.
• Whenever I type ^%^3 followed by "Detail-#", that's the detail of the subclaim of that # that belongs to the.....

(You get the gist - just literally dreaming it up with no guiding principle of any kind).
It's like........the first caveman invented EDI formats, the next one XML at least..

Think of the difficulty when they simply went from icd9 to icd10.
We used to use a program called Market Prominence for medicare stuff and I had to create export files for it. Very interesting stuff..I remember something called the "BE response file" but I can't now remember what in the world it was...just that I had to generate a lot of mumbo jumbo that made no sense unless you knew how to decode it, and the government would decode it and send another file back. Really crazy stuff in place of good programming IMO..

 

[Cotswold]

Thanks to everyone, I've got it working now in a similar way to Pat's idea of a character at a time. Only one strange issue and that is that I have to cobble the code somewhat illogically to get the last record.

The whole object of this is to do with importing bank statement records. Different banks have wildly different CSV export layouts and it is unlikely that I could have examples of them all. One a came across had three lines of pointless information, six blank lines, then the Header and data.

Instead of using the CSV imports, I have been looking at the OFX/QBO/QIF files. The OFX is supposed to be a standard. However, in common with all standards created by committees there are quite a few versions. That's the good thing about standard layouts, there are so many of them.
I've sorted a couple of the OFXs and QBO, so pretty well sorted now as they should cover most of them.

When I came across the issue with the EOF/Loop importing the whole file, I thought unless I sort it now, when I need it later I'll be starting over from scratch. In case anyone has the same issues I've attached an updated ZIP file as before with the copy of the text file used in the code.

Thanks again, Will

 

[Pat Hartman]

Again, I'm not sure what the point of the exercise is. YOU still need to open a new file from a new source and figure out what you've got.

When I did this same project for a client, I used able2Extract. It had a batch mode which was great. Each new bank required a slightly different format with different codes, etc. After I was all done, she discovered that there was a machine readable format but you are right, it was inconsistent so we just stuck with the OCR method and able2Extract did a really great job of converting the pdf's to useable data. I could link to the spreadsheets created from the PDFs and using custom criteria, identify the rows with meaningful data.

 

[Lampje]

Nogmaals, ik weet niet zeker wat het nut van de oefening is. U moet nog steeds een nieuw bestand openen vanuit een nieuwe bron en erachter komen wat u hebt.

Toen ik hetzelfde project voor een klant deed, gebruikte ik able2Extract. Het had een batch-modus die geweldig was. Elke nieuwe bank vereiste een iets ander formaat met andere codes, enz. Nadat ik helemaal klaar was, ontdekte ze dat er een machineleesbaar formaat was, maar je hebt gelijk, het was inconsistent, dus we bleven gewoon bij de OCR-methode en able2Extract deed echt geweldig werk door de pdf's om te zetten naar bruikbare gegevens. Ik kon linken naar de spreadsheets gemaakt van de PDF's en met behulp van aangepaste criteria, identificeren van de rijen met zinvolle gegevens.