Loading EDI data into a database – RDPCrystal EDI Library

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 Database
SQLiteConnection.CreateFile("MyDatabase.sqlite");

dbConnection = new SQLiteConnection("Data Source=MyDatabase.sqlite;Version=3;");
dbConnection.Open();

//Create the Patient table
string 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 table
string 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 table
string 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 transactions
ediValidator.EDIRulesFile = "EDIFiles\\Rules_5010_837P_005010X222A1.Rules";

//Set the path of the EDI file to validate and load
ediValidator.EDIFile = "EDIFiles\\sampleEDIFile.txt";

//Validate and load the EDI data into memory
ediValidator.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 objects
Typed5010Document 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 information
List 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

There are 3 easy steps for loading EDI data into a database

Step 1 – Create the database tables

The Code

Step 2: Load EDI Data Into Memory

The Code

Step 3 – Save EDI data into our database tables

The Code

Displaying the data

The Code

Conclusion

RDPCrystal EDI Library

Get Started Today

About Us

Purchase

Contact Us