New York, USA
Menu
There are 3 easy steps for loading EDI data into a database
- Create the necessary database tables that will hold the EDI data
- Load the EDI data into memory
- Save the EDI data into the database tables
Introduction
In this post we’ll be loading a 5010 837 Professional Health Care Claim EDI file into our database. This HIPAA transaction mainly consists of patients, claims and service line items among other things. We’ll start by creating 3 simple tables to store the data. To link the tables together we’ll use simple integer ids. We’ll then populate the tables with the appropriate data
Step 1 – Create the database tables
In this example we’ll be using a simple open source database called SQLLite. You can use any database you prefer. Popular choices include Microsoft SQL Server, Microsoft Access and Oracle.
We create the following tables schema; Patients, Claims and Service Lines
Our C# table creation code will look like the following:
The Code
//Creates an SQLLite DatabaseSQLiteConnection.CreateFile("MyDatabase.sqlite");
dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");dbConnection.Open();
//Create the Patient tablestring patientTable = "CREATE TABLE Patients (Id INT PRIMARY KEY, First TEXT, Last TEXT, Insurance TEXT, RelationShip TEXT, InsuranceType TEXT, DOB TEXT, Gender TEXT)";
SQLiteCommand command = new SQLiteCommand(patientTable, dbConnection);command.ExecuteNonQuery();
//Create the Claim tablestring claimTable = "CREATE TABLE Claims (Id INT, PatientID INT, ClaimID TEXT, Amount REAL, Diagnosis1 TEXT)";
command = new SQLiteCommand(claimTable, dbConnection);command.ExecuteNonQuery();
//Create the Service Line tablestring servicesTable = "CREATE TABLE ServiceLines (Id INT, ClaimID INT, Procedure TEXT, Charge REAL, ServiceDate TEXT)";
command = new SQLiteCommand(servicesTable, dbConnection);command.ExecuteNonQuery();
Step 2: Load EDI Data Into Memory
Now that we have created our 3 tables we need to load the EDI data into memory before we can do anything meaningful with it. Let’s use the EDIValidator component from RDPCrystal EDI Library. This component will load our 5010 837 Health Care Professional file into memory in the same hierarchical structure required by HIPAA. This will make it easy for us the extract the data we need.
The Code
//Rules files are used to determine how to load different transactionsediValidator.EDIRulesFile = "EDIFiles\\Rules_5010_837P_005010X222A1.Rules";
//Set the path of the EDI file to validate and loadediValidator.EDIFile = "EDIFiles\\sampleEDIFile.txt";
//Validate and load the EDI data into memoryediValidator.Validate();
Step 3 – Save EDI data into our database tables
In the last step we loaded the EDI data into memory. In this step we’ll use the loaded data to populate our tables. After calling the Validate() method of the EDIValidator component the entire EDI file is now in memory in an easy to use structure. We’ll need to extract the data we need.
The Code
//Create a Typed EDI Document so we can use high level objectsTyped5010Document doc = new Typed5010Document(ediValidator.EDILightWeightDocument);
DocumentLoop functionalHeaderLoop = doc.MainSection.GetLoop("FUNCTIONAL GROUP"); DocumentLoop stHeaderLoop = functionalHeaderLoop.GetLoop("ST HEADER");
//Get the 2000A BILLING/PAY-TO PROVIDER HIERARCHICAL LEVEL //In an 837 file this loop is 2000A DocumentLoop bpHierLevel = stHeaderLoop.GetLoop("2000A");
//Get all the subscribers and claims informationList subscriberAndClaimsSections = bpHierLevel.GetLoops("2000B");
SaveAllClaims(subscriberAndClaimsSections);
The SaveAllClaims() method above is called to populate the database tables. It basically iterates over all the claims in the EDI file.
private void SaveAllClaims(List claims){ //We will used these variables for table ID keys int patientRecordID = 1; int claimRecordID = 1; int serviceLineRecordID = 1;
foreach (DocumentLoop claimSection in claims) {->Get the patient information SBR subscriber = claimSection.GetSegment(); DocumentLoop subscriberInfo = claimSection.GetLoop("2010BA"); NM1 subName = subscriberInfo.GetSegment(); DMG demographics = subscriberInfo.GetSegment();
->Get the patient's insurance information DocumentLoop insuranceInfoSection = claimSection.GetLoop("2010BB"); NM1 insuranceName = insuranceInfoSection.GetSegment();
->Save it to the Patients table string insert = String.Format("INSERT INTO Patients (Id, First, Last, Insurance, Relationship, InsuranceType, DOB, Gender) VALUES ({0},'{1}','{2}','{3}','{4}','{5}','{6}','{7}')",patientRecordID, subName.FirstName, subName.NameLastOrOrganizationName, insuranceName.NameLastOrOrganizationName, subscriber.IndividualRelationshipCode, subscriber.ClaimFilingIndicatorCode, demographics.DateTimePeriod, demographics.GenderCode);
SQLiteCommand command = new SQLiteCommand(insert, dbConnection); command.ExecuteNonQuery();
foreach (DocumentLoop cl in claimSection.GetLoops("2300")) {->Get the claim information CLM clm = cl.GetSegment(); HI hi = cl.GetSegment(); string diagnosisCode = hi.HealthCareCodeInformation1.IndustryCode1;
->Save it to the Claims table string claimTable = String.Format("INSERT INTO Claims (Id, PatientID, ClaimID, Amount, Diagnosis1) VALUES ({0},{1},'{2}',{3},'{4}')",claimRecordID, patientRecordID, clm.ClaimSubmitterIdentifier, clm.MonetaryAmount, diagnosisCode);
command = new SQLiteCommand(claimTable, dbConnection); command.ExecuteNonQuery(); foreach (DocumentLoop sl in cl.GetLoops("2400")) { ->Get the Service Line information SV1 sv1 = sl.GetSegment(); DTP serviceDate = sl.GetSegment();
->Save it to the Service Line table string serviceLine = String.Format("INSERT INTO ServiceLines (Id, ClaimID, Procedure, Charge, ServiceDate) VALUES ({0},{1},'{2}',{3},'{4}')",serviceLineRecordID++, claimRecordID, sv1.CompositeMedicalProcedureIdentifier.ProductServiceID1, sv1.MonetaryAmount1, serviceDate.DateTimePeriod);
command = new SQLiteCommand(serviceLine, dbConnection); command.ExecuteNonQuery(); } claimRecordID++; } patientRecordID++; } }
Displaying the data
To display the tables we just need to load each table into a regular DataGrid. We can load each table into a separate grid. For example
The Code
using (SQLiteCommand cmd = new SQLiteCommand("Select * from Patients", dbConnection))
{
DataTable dt = new DataTable();
dt.Load(cmd.ExecuteReader());
dgPatients.DataSource = dt;
}
Conclusion
That’s it. We have now successfully saved our 5010 837 Health Care Claim Professional data into our database.
The fully working source code for this post can be found in our Free 30 Day Trial
Take Charge Of EDI
RDPCrystal EDI Library
