Loading EDI data into a database

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

  1. Create the necessary database tables that will hold the EDI data
  2. Load the EDI data into memory
  3. 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

database

Our C# table creation code will look like the following:

//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.

//Rules files are used to determine how to load different transactions
ediValidator.EDIRulesFile = "EDIFiles\\Rules_5010_837P_005010X222A1.Rules";

//Loading from a file
ediValidator.EDISource = EDISource.File;

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

//Set the type of file to load
ediValidator.EDIFileType = FileType.X12;

ediValidator.AutoDetectDelimiters = true;

//Tell the ediValidator to not just validate but also load the data into memory
ediValidator.LoadValidatedData = true;

//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.

//Create a Typed EDI Document so we can use high level objects
 X125010Document doc = new X125010Document(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<DocumentLoop> 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<DocumentLoop> 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<SBR>();
      DocumentLoop subscriberInfo = claimSection.GetLoop("2010BA");
      NM1 subName = subscriberInfo.GetSegment<NM1>();
      DMG demographics = subscriberInfo.GetSegment<DMG>();

->Get the patient's insurance information
      DocumentLoop insuranceInfoSection = claimSection.GetLoop("2010BB");
      NM1 insuranceName = insuranceInfoSection.GetSegment<NM1>();

->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<CLM>();
         HI hi = cl.GetSegment<HI>();
                    
         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<SV1>();
           DTP serviceDate = sl.GetSegment<DTP>();

           ->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,

 using (SQLiteCommand cmd = new SQLiteCommand("Select * from Patients", dbConnection))
 {
     DataTable dt = new DataTable();
     dt.Load(cmd.ExecuteReader());
     dgPatients.DataSource = dt;
  }

dataapp

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

[divider]

Take Charge Of EDI

RDPCrystal EDI Library

trial2