Creating a flat file or Excel file from the X12 xml using XSLT and XslCompiledTransform

The most common use case for using this X12 Parser is to eventually save the data to a database or to save it to a flat file to be consumed by other applications.
The easiest way to do this and brush up on your XPath query skills is to use an XSLT. There are many ways that an XSLT can be transformed. You can run an XSLT within the Visual Studio environment and specify an Input and an Output file while debugging.

This tutorial will show you how to call the XSLT from your C# code so that you can create a console application for parsing to your specific needs.  The completed project can be found here.

Step 1. Create a console application and reference the OopFactory.X12.dll.

CreateProject.png

Step 2. Add an xslt file to your project.

AddNewItemXslt.png

Step 3. Change the build action of the xslt file to "Embedded Resource".

ChangeBuildAction.png
This is being done so that we can embed the xslt into the assembly and not worry about locating the file during execution. Optionally you could copy the file to the output directory, but I have had problems with Unit Test that don't run from the location of the output directory making the code to locate it a bit more difficult.
For transformations that may change frequently, such as UI transformations, it might be advantages to not embed the xslt, but for back-end processing this is rarely the case.

Step 4. Add a sample edi file to the project that will help test the application.

Use any sample EDI you like. For the sake of this example, I have formatted the X12 in this example so that you can see the structure of the edi segments.  It should also work just as well without the extra whitespace.

AddSampleEdiFile.png

Step 5. Change your program file to load to parse the EDI file and use the xslt to transform to a csv format.

The following example assumes that you embedded your xslt.  If you choose to have an external file instead use the FileStream object instead.

using System.Text;
using System.IO;
using System.Reflection;
using System.Xml;
using System.Xml.Xsl;
using OopFactory.X12.Parsing;
using OopFactory.X12.Parsing.Model;

namespace MyX12.Edi835Parser
{
    class Program
    {
        static void Main(string[] args)
        {
            Stream transformStream = Assembly.GetExecutingAssembly().GetManifestResourceStream("MyX12.Edi835Parser.X12-835-To-CSV.xslt");
            Stream inputStream = new FileStream(args[0], FileMode.Open, FileAccess.Read);
            Stream outputFile = new FileStream(args[1], FileMode.Create, FileAccess.Write);

            X12Parser parser = new X12Parser();
            Interchange interchange = parser.Parse(inputStream);
            string xml = interchange.Serialize();

            var transform = new XslCompiledTransform();
            transform.Load(XmlReader.Create(transformStream));

            transform.Transform(XmlReader.Create(new StringReader(xml)), new XsltArgumentList(), outputFile);
        }
    }
}

Step 6. Write your transformation

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl"
>
  <xsl:output method="text" indent="yes"/>

  <xsl:template match="Interchange">
    <xsl:apply-templates select="FunctionGroup/Transaction/Loop[@LoopId='2000']/Loop[@LoopId='2100']"/>
  </xsl:template>

  <xsl:template match="Loop[@LoopId='2100']" >
    <xsl:variable name="trans" select="../../."/>
    <xsl:variable name="payer" select="../../Loop[@LoopId='1000A']"/>
    <xsl:variable name="payee" select="../../Loop[@LoopId='1000B']"/>
    <xsl:variable name="payment" select="."/>
    <xsl:value-of select="$trans/BPR/BPR16"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payer/N1/N102"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payee/N1/N102"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payee/N1/N104"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CLP/CLP01"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CLP/CLP02"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CLP/CLP03"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/NM1[NM101='QC']/NM103"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/NM1[NM101='QC']/NM104"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CAS[CAS01='CO']/CAS02"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CAS[CAS01='CR']/CAS02"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CAS[CAS01='OA']/CAS02"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CAS[CAS01='PI']/CAS02"/>
    <xsl:value-of select="','"/>
    <xsl:value-of select="$payment/CAS[CAS01='PR']/CAS02"/>
    <xsl:value-of select="','"/>
    <xsl:text>&#x0A;</xsl:text>
  </xsl:template>
</xsl:stylesheet>

Since most transactions are very hierarchical in nature, it might be beneficial to have a separate transformation at different levels so the same information is not repeated so often.

This is usually managed by having separate CSV files for different levels in the transaction, or having a fixed-width file with a record indicator column to indicate the expected specification of the fixed-width line.

Step 7. Change the command line arguments in your Debug configuration to run your sample file.

SetCommandLineArguments.png

Step 8. Run your application and inspect your output.

The expected output is as follows:

20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,444444,1,56.70,FUDD,ELMER,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,999999,4,25.95,SAM,YOSEMITE,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,888888,4,162.13,SQUAREPANTS,BOB,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,111111,2,56.52,LEGHORN,FOGHORN,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,121212,4,56.52,EXPLORER,DORA,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,333333,1,74.61,BEAR,YOGI,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,777777,25,136.9,BIRD,TWEETY,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,123456,22,-42.58,SIMPSON,HOMER,,,,,,
20040315,ASHTABULA COUNTY ADAMH BD,LAKE AREA RECOVERY CENTER ,346608640,090909,22,-86.76,DUCK,DAFFY,,,,,,

835 Example to CSV and Excel formats

The next example is very similar to the previous except there are two transformations. The first is to a flat csv file, the 2nd has the same information but is transformed into Excel's xml format so that formatting can be retained. You can download this example here.

This example was an actually request from a user that was new to development but had a very tight deadline that he was expected to meet.  The csv version took me about 2 hours to produce and the Excel version took me about 3 hours to produce.  The output csv looks like the following and can be open with Excel or it can be loaded with some other application into a database for further manipulation.

 

Filename, Check Sequence, Payer Name,Payee Name,Payee ID,check Date,Check $,Check/EFT Number,NPI,Patient Name,Policy/HIC Number,Acct Number,ICN,Date Of Ser From,Date Of Ser To,Procedure,Billed Amount,Allowed Amount,Deductible,Coins,Paid Amount
"835_DeIdent_02.dat","Check 1","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,0,999999999,"9999999999","XXXXX, XXXXX","","","9999999999999",01/27/2011,01/27/2011,"99999",135,99.99,,,0
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXXXXXX, XXXX","","999999999","9999999999999",04/19/2010,04/19/2010,"99999",95,99.99,,,99.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"99999",380,999.99,,,109.26
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"99999",133,99.99,,,99.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"99999",160,99.99,,,99.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"X9999",9999,9999.9,,,9999.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"X9999",9999,99.9,,,99.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"X9999",12,9.99,,,9.99
"835_DeIdent_02.dat","Check 2","XXX - XXX X9 XXXX X","XXXXXXXXXX XXX XXXXXXXX XXX","9999999999",04/05/2011,99999.99,999999999,"9999999999","XXXXX, XXXX","","999999999","9999999999999",02/24/2011,02/24/2011,"99999",133,99.99,,,99.99

The development follows similar steps as outlined above, but the XSLT will be outputing xml instead of text. The finished Excel file will be formated as follows:

835 as Excel

This xml is 10 times larger than the corresponding csv, so I would only use it if the end user is a person instead of another application. The following formatting was applied to this document:

  • The header row is bolded and fixed
  • Each new check is separated by double lines
  • Alternating rows within a check have different backgrounds

To implement the formatted Excel file you don't necessary have to learn Excel's xml format. These are the steps you should follow.

  1. Implement the version that creates a flat csv file following steps 1-8 above.
  2. Open the csv in Excel and apply the formatting that you want (or give it to a business analyst to define with the customer what they want it to look like).
  3. Save the file in "XML Spreadsheet 2003 (*.xml)" format type.
  4. Re-open the xml file in notepad and copy this into your xslt that will output xml.
  5. Reverse engineer your transformation into xml.

To keep the application simple, it just looks at the extension of the output file, if it is .xml then it will produce the formatted excel file otherwise it produces a csv file.

Since Word and Excel support xml formats, you could potentially use this technique for all of your reporting instead of having to use reporting engines that may allow you to control the format on screen, but don't always look the same when you export to Excel.  I am also a fan of using fo-xml for generating pdfs.  There is an open source fo processor that works quite nicely at http://fonet.codeplex.com.

Last edited Oct 7, 2011 at 3:51 PM by dstrubhar, version 16

Comments

spanchal Aug 2, 2016 at 1:46 PM 
I tried this and this is the great repository to work with 837 and 835 files.

I have used the zip under "835 Example to CSV and Excel formats" which allows to created CSV and XML files.

I am trying to get excel file using the code. I am passing
"Sample-835.txt" "Sample-835-Output.xlsx"
arguments to generate the excel file but the excel file generated is not in the proper format. I have tried converting the excel file using openXML and OleDbConnection both but no one is working. I want to get datatable from the excel file so that I can further add those into the database.

Can you please help me on what I am missing? How can I get the proper excel file from the above code and then create datatable from those excel file?

ejc67 May 21, 2015 at 2:13 AM 
I love this -but I have a question on the Procedure Code (CPT Code) and their modifiers. I notice that it just lists the procedure code but not the modifiers. I've modified the XSLT file to include the modifiers but it is not writing them out. I get the column header but not the value for the column. If you look at the example, the procedure code is H0005 and the modifiers are HF and H9 but we don't get the modifiers.

Venkatesh8403 Jun 10, 2014 at 6:24 AM 
how can i see the same concept for 856 Transaction Set Code ?

kzheng Sep 5, 2012 at 4:32 PM 
Hello Dstrubhar, I just wanted to shout out at your awesome work and the absolutely responsive support when it comes to issues and bugs. I am using X12parser now with SSIS, and it saves me tons of time when doing ETL with these EDI files.

Once again, I really appreciate your team of work!!

chowdary2005 Jul 5, 2011 at 7:32 AM 
Hello dstrubhar,

Really this is very great work Doing EDI frame work that too in .net platform in opensource i really didnot expected this. You made impossible thing. I really appreaciate your team good work. Infact, i wanted to add more to this work. Is there any possibility to add XSLT file writer from front end like If we click the nodes of the segement and specify the length. Then it automatially creates .XSLT file which would be of more helpful to the users and adds more to the present thing.

Once again i really appreciate your team of work.

Thank you.