Reverse engineering 837 data to SQL table
-
Hello Experts
I have 837 files , and I want to create a procedure to convert it to raw data ans store it in sql table.
any ideas
Thanks
Daniel
-
What do you mean by "raw data"?
What sort of data do the files contain?
Are the files all structured the same? (Number of columns, order of columns, column names etc)
What do you actually want help with? Table design? ETL best practice? Proc design? How to use BCP? How to loop?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Lets say we have sample 837 data below.
and as you know primarly it has:
2000A- Billing Provider
2000B- Subscriber
2000C- Client, though this is only on the form if it’s different than the subscriber.
2300- Claim Information
2400- Service Line Information
Assume I have 5 SQL tables for each above loops ( each columns are elements in the segment)
I have SSIS to import it to sql table to import the 837 in a single table.
the problem is , how can I map this data back to existing SQL tables ( this is what I meant raw data)
ISA*00* *00* *01*9012345720000 *01*9088877320000
*100816*1144*U*00200*000000031*0*T*:~
GS*HC*901234572000*908887732000*20100816*1615*31*X*005010X223A1~
ST*837*0034*005010X223A1~
BHT*0019*00*3920394930203*20100816*1615*CH~
NM1*41*2*HOWDEE HOSPITAL*****46*0123456789~
PER*IC*BETTY RUBBLE*TE*9195551111~
NM1*40*2*BCBSNC*****46*987654321~
HL*1**20*1~
NM1*85*2*HOWDEE HOSPITAL*****XX*1245011012~
N3*123 HOWDEE BLVD~
N4*DURHAM*NC*27701~
REF*EI*123456789~
PER*IC*WILMA RUBBLE*TE*9195551111*FX*6145551212~
HL*2*1*22*0~
SBR*P*18*XYZ1234567******BL~
NM1*IL*1*DOUGH*MARY****MI*24672148306~
N3*BOX 12312~
N4*DURHAM*NC*27715~
DMG*D8*19670807*F~
NM1*PR*2*BCBSNC*****PI*987654321~
CLM*2235057*200***13:A:1***A**Y*Y~
DTP*434*RD8*20100730-20100730~
CL1*1*9*01~
REF*F8*ASD0000123~
HI*BK:25000~
HI*BF:78901~
HI*BR:4491:D8:20100730~
HI*BH:41:D8:20100501*BH:27:D8:20100715*BH:33:D8:20100415*BH:C2:D8:20100410~
HI*BE:30:::20~
HI*BG:01~
NM1*71*1*SMITH*ELIZABETH*AL***34*243898989~
REF*1G*P97777~
LX*1~
SV2*0300*HC:81000*120*UN*1~
DTP*472*D8*20100730~
LX*2~
SV2*0320*HC:76092*50*UN*1~
DTP*472*D8*20100730~
LX*3~
SV2*0270*HC:J1120*30*UN*1~
DTP*472*D8*20100730~
SE*38*0034~
GE*1*30~
IEA*1*000000031~
-
OK, I had no idea what 837 files were. I just thought you had a lot of files!
Excuse my lack of knowledge about these files, but how do you determine which file to map "IEA*1*000000031~" (for example) to?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
"837" files are an EDI specification. They can follow either an EDI format or a similar format in XML (but usually not the later). I wish the world would simply outlaw the use of the text based EDI format.
https://www.1edisource.com/resources/edi-transactions-sets/edi-837/
My recommendation is to NOT try to build something homegrown. Buy a parser for them.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
no problem - I think if you haven't worked on 837 EDI files , it might be difficult.
anyone else have some idea - please
-
If you have some money, buy Andy Leonard's SSIS stuff that does this already. Then just use his. It would be hard to find someone that knows much more about SSIS, so there's that.
-
I wish , but thats not possible for now.
-
Dude... spend the money. The information in 837s is really important to people. You really can't afford to get it wrong because, if you do, you and the company you work for will be liable beyond anything your imagination can comprehend.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Jeff Moden wrote:
"837" files are an EDI specification. They can follow either an EDI format or a similar format in XML (but usually not the later). I wish the world would simply outlaw the use of the text based EDI format.
https://www.1edisource.com/resources/edi-transactions-sets/edi-837/
My recommendation is to NOT try to build something homegrown. Buy a parser for them.
Oh. My. God.
They could use XML???? I did not realize that. Now I'm just being tortured for fun and profit.
Dan, you're going to want to look to the companion guide for your specific situation, (they have one somewhere, ….right??) so that you don't miss out on any of the special customizations that the transmitter/regulator threw in. Because nobody uses the bloody standard as it is. That is a project for you, and you'll want to talk to the users who will consume whatever reports you're going to build to see what's important to them. It is quite possible that you only need a few tables for reporting, and can ignore the rest of the data, as long as you can get to the raw file data somehow for "double-check" situations.
Also, Jeff is right. Advocate for a parser, it's ALWAYS about the money, so if you can show ROI on use of one, I bet you can find an argument for that rather than rolling your own.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - Jeff Moden wrote:
Dude... spend the money. The information in 837s is really important to people. You really can't afford to get it wrong because, if you do, you and the company you work for will be liable beyond anything your imagination can comprehend.
little perspective - we NEVER reported on claims that weren't fully processed, and NEVER used incomplete months' data without including IBNR, because one claim for one sick ICU patient could easily be > $1million, and throw your whole totals off for that month.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses - Jeff Moden wrote:
"837" files are an EDI specification. They can follow either an EDI format or a similar format in XML (but usually not the later). I wish the world would simply outlaw the use of the text based EDI format.
https://www.1edisource.com/resources/edi-transactions-sets/edi-837/
My recommendation is to NOT try to build something homegrown. Buy a parser for them.
Absolutely agree - there are way too many variables to how the 837 can be structured. Purchase an EDI parser...it will save you a lot of time and hassles and allow you to also parse the 835, 270/271, and other formats without having to rebuild everything in SSIS.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Having read all of these posts, I'm mighty glad I've never had to deal with these types of file!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
Check this out on Andy's website, CozyRoc
EMail him and ask him if his EDI solution stuff will solve your problem. See if you can test it out. I just can't see how reinventing the wheel and writing the whole thing yourself makes any sense at all. Either your company pays you to do it, or it buys something that's already built.
From his webpage: Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.
So download it, try it out, and see if it will work for you. What do you have to lose? Testing it for yourself is free.
-
As luck would have it, I recently completed a project doing just this. I can not share the details as it was for another client, but I can certainly tell you the steps involved and the challenges you're about to encounter if you do decide to do this yourself.
You can find an older solution called HIPPATalk, but that's a straight hardcoded method to get data out. It's old and only pulls certain information, again hard-coded. This solution is not set-based but provides a good starting point.
If you are wanting a challenge and do it yourself, first and foremost, you must completely understand the structure of an x12 document and the intricacies around 837's and 835's. Specifically, understanding Transaction Sets, Functional Groups, Interchange Sets, Loops, Segments, Elements and how the data contained depends upon where it lives in the file.
Step one is getting the raw data into a SQL table as TEXT. This is a simple process that can be done in a few steps in SSIS. I used the same process defined in the HIPPATalk code to use a parsing table that maintained the order of the data. *Again, this is important!
Next, you'll want to get the specific configuration definition data into tables.
The approach I took was to pull out the configuration of these Loops-Segments-Elements into a set-based design (above). Licensing issues will make it difficult to get and useable configuration data. However, a workaround is to use the 835, 837 PDF's and extract (copy-paste) to Excel using Adobe PDF. Then you'll need to chop this data up and format in such a way to then import into your configuration tables. This is a tedious process, but crucial! This is also where you would handle the location-specific data definitions, i.e. Occurrence Order, Starts Loop, Starts Internal Loop. You will have to figure these out on your own. 🙁
Once you have the configuration data set and text data loaded, its a matter of writing your SQL to get the data out in XML.
I wish I could share more details, but again, this was for another client.
In short, it can be done. It's fun, challenging and quite rewarding in the end, but unless you have the time (a few months) and a solid understanding of the original format, I'd suggest purchasing a vendor-supported piece of software to handle it for you!
Info:
HIPPATalk - https://hipaatalk.soft112.com/
X12 - http://www.x12.org/x12-work-products/x12-transaction-sets.cfm and here -->
835, 837 overview - http://saplingdata.com/pages/app/blog_x12_837_835?release=33
835 - https://www.cms.gov/Medicare/Billing/ElectronicBillingEDITrans/downloads/Remittance4010A1to5010.pdf
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. Selburg
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply