Importing data from a very different structure (1 Viewer)

[joe789]

Hi Folks,

I am trying to help a non profit government agency make use of new billing standards and thru this have been introduced to a file format structure dubbed as 'EDI' which differs considerable from any other flat file structure in that the start of each row of data, as indicated by a segment marker, contains different data. I am struggling here and I cannot seem to find anything in Access or even VB that can handle this. I am wondering if anyone has come across any method that would be able to convert this EDI format into a flat file that can be meaningful. I will attempt to give an example below:

REF*6R*340
CLM*856-3402*145***53:B:1*Y*A*Y*I
HI*ABK:F1120*ABF:F411

So basically, there are about 30 different 'segments', so the first few characters of each line are a unique segment which contain different information ... so perhaps the REF would contain the authorization code and then the CLM would be the claim info and the HI may be the health info and so on and so forth ... and folks, it gets NASTIER, because then in the middle of the structure is 'looping' where nothing is repeated from the above segments ...

Any ideas or help :banghead:

Thanks,

Joe

 

[Frothingslosh]

You're not going to have much luck there. EDI (which is actually not new) simply means 'Electronic Data Interchange', and there are a ton of different standards.

In your case (it sounds like you're talking about an 837), the format in question comes with a gigantic manual explaining what each of the hundreds of possible references means along with when they're used, (including optional, conditional, and unused values). In addition, position in the file is part of the data - one drug code outside the claim loop can indicate prescriptions written as part of the overall treatment, while the same code INSIDE that loop indicates a prescription written as part of an individual line item.

(If you haven't figured it out, I work in health care. I worked with raw claims files for around 3 years, including the change from ICD-9 to ICD-10, and currently work at one of the largest health insurance companies in the US.)

Those files are as flat as they're going to get - the 'loops' are industry terminology for 'groupings', and while most CAN repeat, the claims line is usually the only one that actually does.

Depending on your actual requirements, there are a multitude of claims file readers available for purchase, and even claims management systems that import the file data and store them in your local database. I would recommend looking into those rather than spending the hundreds (MINIMUM) of hours it would take to reinvent the wheel in this case. I would also recommend spending a dozen or two hours going through the manual to learn the information flow in those claims files, or else looking into a class on it, because those things are INSANELY complicated.

Edit: Just to make things even more interesting, there's one set of standards for institutional claims and another one entirely for professional claims.

 

[The_Doc_Man]

I'm with Frothingslosh on this one: If there is a make/buy decision available for a translator and the explanation manual is very large, definitely go for "BUY" on this one. I'm not in the same industry he is, but in my former Dept of Defense lifetime, we had similar types of data interchanges and the specifications manuals were literally overwhelming.

If you can convince the boss to buy a conversion program, you are WAY ahead of the game and can spend a lot LESS money managing the 3rd party conversion process.

 

[jdraw]

I agree with Frothingslosh and Doc_Man. This is not a project where you should reinvent the wheel.
Good luck.

 

[Frothingslosh]

Oh, one other issue, be careful what data you post while asking about this.

Just from the little bit you put up, I now know from the second line that that is patient 856-3402 from whichever clinic or medical center provided this data, who was billed $145.00 for a first visit to the location for this issue, who is a confirmed patient of the clinic, and the that clinic had informed consent for anything they did. The third line tells me that the primary diagnosis was for opioid dependency, with a secondary diagnosis of generalized anxiety disorder. (The REF*6R segment is provider-defined data; my old company kept our internal subscriber ID number there, but it can be used for whatever the provider wants.)

And that's just three lines with PHI not included, so be very, VERY careful about posting things like that from those claims. This one wasn't a HIPAA violation, but it can be incredibly easy to cross that line without meaning to.