looking for a program to read and parse EDI files or 837i.

Posted on 2015-01-19
Last Modified: 2015-08-26
Hi Experts,

I am looking for a program that can read EDI files or to be specific, files of type 837i.
Basically I am trying to parse that file into a text delimited format.
Wondering if someone had already written something for this purpose, or perhaps if someone is familiar with a freeware/shareware software that could recommend it?

Question by:bfuchs
  • 6
  • 5
  • 4
  • +2
LVL 57
ID: 40558554
The processing and mapping of EDI files to another format is fairly complex.

You really want to try and buy something commercial rather than re-invent the wheel, unless your just doing some data extraction and are not planning on doing the main processing of doucments.

Biztalk Server from Microsoft is one such, and there are other translators out there, such as Sterling Gentran or GIS.

LVL 84
ID: 40558762
Ditto what Jim said. Working with EDI stuff is very, very complex. I do a LOT of this in my day-to-day work, and it's not something for the faint of heart!

Also - 837 is HIPPA stuff, so be careful dealing with that in Access. It's nowhere near secure enough to make it through a HIPPA audit.
LVL 57
ID: 40558777
<<It's nowhere near secure enough to make it through a HIPPA audit. >>

 Good point, I should have mentioned that.

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

LVL 35

Expert Comment

ID: 40559045
I am currently working with creating the 837P and reading the 835.  I'm not sure how different the I and P formats are.  If you can tell me a little more about what you need, I might be able to share one of the tools I created for testing. When I created the 837P, I used the curmudgeon method.  Each record type is a separate sub.  For example:
Public Function Create_SV1(pCode As String, BillAmt As Currency, Quantity As Long) As String
''page 353
    Dim strSV1 As String
    strSV1 = "SV1"                                          'rec id
    strSV1 = strSV1 & "*HC:" & pCode                        'SV101 Composite Medical Procedure
    strSV1 = strSV1 & "*" & BillAmt                         'SV102 Monitary Amount      -- same as in CLM
    strSV1 = strSV1 & "*UN"                                 'SV103 Unit or Basis for Measurement Code
    strSV1 = strSV1 & "*" & Quantity                        'SV104 Quantity
    strSV1 = strSV1 & "*"                                   'SV105 Facility Code Value
    strSV1 = strSV1 & "*"                                   'SV106 Service Type Code
    strSV1 = strSV1 & "*1"                                  'SV107 Composite Diagnosis Code Pointer
    strSV1 = strSV1 & "~"                                   'rec terminator
    Create_SV1 = strSV1
End Function

Open in new window


Author Comment

ID: 40559054
Hi Experts,

Just to clarify, I am not trying to do billing system from access or store any highly sensitive info in a access db.
I am trying to help a user who has an old Cobol system for billing, while some parts come from a web app that is being sent to vendor for billing in 837 format, now users account receivable dep is having difficulties matching up records from two systems...

Therefore I came up with this theory to have a program read and save the web info (from 837 file) in a temp table (or excel file), which will be imported into the Cobol system, and there it will eventually be mapped in a way that will not require such extensive users resources.

While doing some google research I came across the following site, which seems to do the desired work, for a reasonable fee, wonder if any of you heard of them or have any experience with it?


Author Comment

ID: 40559080

Attaching a portion of the 837 file, just altered some parts that (may had) contained personal info.
let me know if this would be any help,

LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 167 total points
ID: 40559470
<<While doing some google research I came across the following site, which seems to do the desired work, for a reasonable fee, wonder if any of you heard of them or have any experience with it?>>

 Not here.   I've only worked with regular standard translators.

 As long as your just pulling data from a document, that would be simple enough.   EDI is nothing more than the exchange of delimited files according to a standard.

 Pulling the data out won't be hard.

 In a EDI exchange, you have three levels:


Each has a header and trailer.    You basically can ignore the first two, although the Interchange header contains the delimiters used to form the file.  Here's an example:

ISA*00*          *00*          *ZZ*TGTDVS         *08*xxxxxxxxxx     *150119*1547*U*00401*000015792*0*P*>~

First few characters of each line denote the segment    ISA/IEA is the interchange header and trailer.   if you look at the end of the ISA line, you'll see *,  >, and ~

 These are the element, sub-element, and segment terminators.  These are always in the same place.  So grab the last character of the ISA (and it's always the same length no matter what), and use SPLIT() to pull apart all the segments.  Then start reading them.

You can ignore ISA/IEA, and GS/GE.     Each Document is started with ST and ends with SE.  What's in each segment depends on the standard used (and there are many, most popular being X12), version of that standard (4010,4030, 5010, etc), and the exact doc (in this case a 997, which is a functional acknowledgement).

 As you work through the split array(), you can further split the segments on the element delimiter (* in this case) and process accordingly.

LVL 57
ID: 40559472
I should add that where EDI gets more complex is in formulating documents, checking the elements (some can only be used when others are used, can only be certain values, etc.) and handling the interchanges and groups, all of which are numbered and controlled.

But with just having to extract data, you can ignore all that.

LVL 84

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 167 total points
ID: 40559538
With your particular file, you could Split on the tilde, and then further split on the asterisk, as Jim suggested:

Dim s As String()
Dim i As Integer
Dim j As Integer

s = Split(YourFile, "~")

For i = 0 to UBound(s)
  '/ examine each line
  Dim l as String()
  l = Split(s, "*")
  For j = 0 to UBound(l)
    '/ examine the elements in each line
  Next j  
Next i

You'd need a mapping or schematic for the specific file+version you're being provided, and you should be able to figure it out from there.
LVL 35

Assisted Solution

PatHartman earned 166 total points
ID: 40559902
Converting the 837 file to excel doesn't help the COBOL program at all.  The COBOL program should be able to read it as is.  Here is the code I wrote to import the 837 into a table.  Only the first couple of columns are defined.  Since each record type in the 837 has a different record type, it doesn't lend itself to table recordset processing which presumes every row is like every other row.  Here's code I wrote to import the 837 just to make it easier for me to look at it and ensure I was formatting it correctly.  But I am posting the code so you can see how you have to read it.  The software at the other end that created the file didn't use the standard DOS crlf to delimit records so the whole file has to be read into memory and then parsed into records.  If your file does include crlf at the end of each record, then the processing of the file will be record by record.

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

    On Error GoTo Err_Proc

    Set db = CurrentDb
    Set td = db.TableDefs("tbl837BlobImportForTesting")
    Set rs = td.OpenRecordset(dbOpenDynaset, dbSeeChanges)
    'Open FileNum For Input As #1
    strFullFileName = frm.txtFileName
    FileNum = FreeFile
    Close FileNum
    Open strFullFileName For Input As FileNum
    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)
            If sRecBlob & "" = "" Then
                sTranCode = Left(sRecBlob, InStr(sRecBlob, "*") - 1)
                aryColumns = Split(sRecBlob, "*")
            'add record to  table
                    rs!ImportBatchID = frm!txtImportBatchID
                        If sTranCode = "NM1" Then
                            If aryColumns(1) = "IL" Then
                                saveEMS = aryColumns(9)
                            End If
                        End If
                        If sTranCode = "HL" Then
                            If aryColumns(2) = "1" Then
                                saveSendID = aryColumns(1)
                                rs!SendID = saveSendID
                            End If
                        End If
                        rs!TranCode = sTranCode
                        'can't populate EMS on some records because we don't get the correct EMS until after writing the record.  Update later with query.
                        Select Case sTranCode
                            Case "HL", "SE", "GE", "IEA", "ISA"
                                'don't populate EMS for these codes
                            Case "SBR"
                                rs!SendID = saveSendID
                            Case Else
                                rs!ems = saveEMS
                                rs!SendID = saveSendID
                                'rs!ReceiveID = saveReceiveID
                        End Select
                        For i2 = 1 To UBound(aryColumns)
                            rs.Fields(i2 + 4) = aryColumns(i2)
                        Next i2
                    RecCount = RecCount + 1
                End If
        Next i
    MsgBox "Import Complete", vbOKOnly
''    Set qd = db.QueryDefs!q837TestUpdateCLPwithEMS  'populate EMS on CLP records
''    qd.Execute
    'close files and release
    Set rs = Nothing
    Close FileNum
    Debug.Print RecCount
    On Error GoTo 0
    Exit Function


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

Open in new window


Author Comment

ID: 40561239
Hi Experts,

@Jim, Scott,
I really appreciate your work, however at the present time, this looks like something that would be very time consuming for me to apply your code and then try to figure out all the specs of this document, therefore my manager decided first to give it a try that software I mentioned above.

I guess, if that does not turn out to function well I will have to come back and try the hard way (for me, maybe for you guys these is easy stuff..)

I did test your code as it sounded more specific to our need, and attached is what I got in result, wondering how can I pull out a full scheduling record?

P.S. I will accept all your suggestions, as the fact that I didn't get approval does not mean the suggestions failed to solve the problem:)

LVL 84
ID: 40561523
Even with Pat's suggestion (or mine), you'd have to "figure out" how to pick out the bits and pieces from that record. In most cases, you can find an EDI "schematic" somewhere online, or you can contact the place supplying you with the document and ask them for the layout/format.

In other words, getting the file broken down to the elemental puzzle pieces is only the first (and generally easiest) part of the battle. After that, you've got to put the puzzle together - and sometimes it feels like you're doing that without the picture on the top of the puzzle box!
LVL 35

Expert Comment

ID: 40562392
Unfortunately, there is a company that controls the EDI formats and if you want the format for a specific transaction, you have to purchase the manual which runs around $500!!!!  A few years ago they were $35 printed.  Now they are $500 as a pdf.

I can't tell you how to pull out a scheduling record.  All I have is the documentation for the 837P.  It might be a "CRC" with "ST" in the third column.  Also, keep in mind that you might actually need several tran types to pull together something meaningful that includes a client number, etc.  In the code I posted, you can see that I save columns from some records and copy the values into others so I can end up with a set of records that I can identify as a "transaction".  There is nothing in the record layout that provides the ability to connect one record to another which is really what makes the EDI files so hard to works with.  The record layouts were obviously developed by someone without a clue regarding how to make them program logic/query logic friendly.  I just had my first file rejected because the service date which is entered as a range had spaces around the dash.  Why start and end dates are not in separate fields is beyond me.  No "programmer" worth his salt would have done that.
LVL 84
ID: 40562785
You also have to be aware of the source of that file. If it came from a VAN or other 3rd party system in place between you and the trading partner, then the output may be in THEIR specific format.

EDI transactions are purposely designed to be autonomous and platform independent and, to me, are fairly logical. They are structured, and can contain nested elements which are "related", and while they cannot be queried in the traditional ways, you can write code to get the data in a structured fashion. There are most definitely some oddities, but once you figure out how to work with them it becomes fairly straight forward.

EDI formats for X12 (the predominant format in North America) are managed by the Data Interchange Standards Association: You can join the organization, but you'll spend around $500 to do so.
LVL 57
ID: 40562854

 As Pat and Scott said, you need the spec for the document.  You probably don't have to buy anything, but just go to people that produced the document.

 They should have a specific for what their using and how they used it.

 One of the thing you'll find quickly in the EDI world is that even though it is based on a standard, there's nothing standard about the way various companies implement the standards.

 For example, a SAC is a Service/Allowance charge segment and when someone hand your an 850 (Purchase Order), the SAC should pertain to the PO.   But I've seen a number of cases where info is passed that pertains to their customer and has nothing to do with the PO.   That's a misuse of the standard.

If you can't get a document spec for whatever reason, what you generally can do is a Google search and find someone that has their spec available that you can get at it.  That will give you an idea of what the segments are.

For example, I just Google'd "837i EDI" and came up with Empire Blue's:


Author Comment

ID: 40563522
Hi Experts,

You will be surprised that the software I mentioned above is very likely doing the job I need w/o any programming or mapping involved, and for the price of $165.

I am just waiting for final user confirmation that the info is 100% accurate.

Basically all I needed is to get the PatientID, Name, Date and time Of Service, Control Number, Amt Charged and perhaps one or two additional fields, and these is what is showing me in that results.
It gives me an option to save in a text delimited format, so from there the Cobol programmer will handle the mappings..

It turned out like Jim quoted above
You really want to try and buy something commercial rather than re-invent the wheel

Author Closing Comment

ID: 40563528
Its always good to know that in case I do have to work with insides of EDI (or any technical issue), I can count on such high valuable resources as you guys!!

Thanks a million.

Expert Comment

by:eric gonzales
ID: 40948769
I can build an 837 pipe delimited export file if you're interested.
LVL 57
ID: 40948980
FYI, I know your set for now, but future readers might want to checkout Liaison's EDI NotePad, which I discovered not too long ago:

 The free version has quite a bit built-in.   For $179, you can get a command line feature and some other stuff, which would probably satisfy the needs of most who are going to cobble something together.

 Even the top of the line version is only $249, and that tosses in a EDI dictionary.

and BTW, no ties to the company.  Just something I was made aware of by someone else.


Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In this article, you will read about the trends across the human resources departments for the upcoming year. Some of them include improving employee experience, adopting new technologies, using HR software to its full extent, and integrating artifi…
Viewers will learn how to use the Hootsuite Dashboard.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question