EDI-to-CSV Conversion User Guide

Overview

You can convert your EDI files to CSV or Excel using any of our converter tools (CLI, API server, Java library). All converters produce the same output.

CSV/Excel conversion is currently supported for 835, 837, and 834 X12 EDI transactions (file types).

You can find many examples of converted files in our GitHub.

The layout of the output file is highly configurable. It is defined by the “csv_conversion.yaml” configuration file (a.k.a. the schema file) in YAML format. You can view the default schema file here.

The schema file defines included and excluded columns, the number of column repetitions for lists, and other parameters.

The schema file contains named schemas (configuration) for each supported EDI transaction type.

For example, this block defines the schema for 835:

- transactionTypes: [ 835 ]
  name: claim-line-separate-files
  aliases:
    - default

The schema name must be unique for a given transaction type.

You can define custom schemas or customize the default schemas.

The schema name can be provided using the schemaName parameter for the API call or the --csv-schema-name option in the CLI tool. If the name is omitted, the converter uses the schema with the “default” alias.

All CSV-related command-line options and API parameters also apply to conversion directly to Excel (-m excel).

Conversion (Flattening) of Lists

EDI files usually contain many child lists (called “loops” in EDI). For example, a single claim can have many service lines and multiple diagnosis codes.

By default, the converter repeats columns for each item from the list. For example, it will convert the list of diagnosis codes to Diag1Code,Diag2Code,Diag3Code,Diag4Code,Diag5Code columns. The schema defines the number of repeats; the default is to repeat three times.

You can specify the number of repeats explicitly using the list’s schema, e.g.:

  listSchemas:
    - names: [ Diags ]
      numberOfColumnRepeats: 5

List schema definitions reside within the transaction’s schema. You can create a new transaction schema with a different name and override the number of repeats.

Note that the converter always creates the specified number of repeated columns even if fewer items are in the list. The unpopulated columns remain blank.

Let’s say there are only two diagnosis codes in a claim. The converter will produce the J0300,Z1159,,, values for Diag1Code,Diag2Code,Diag3Code,Diag4Code,Diag5Code headers.

This approach creates a fixed layout for your CSV/Excel files. The number of columns and their order does not change depending on the EDI input.

All fields from 835/837/834 are converted to CSV by default. For 835/837, the converter creates a row for every service line and duplicates payment/claim level fields on every row.

Defining how to Convert Lists to Files

The converter can split lists and create different output CSV files for different lists. For example, for 835, the converter creates two files, one with the claim (header)-level columns and one with the service line-level columns. The same approach is used for all 837 EDI files.

You can correlate rows in the child list (lines) with the parent list using the converter-generated unique ID of a claim.

When converting to Excel, the converter creates multiple Excel sheets instead of multiple files.

The number of files and their names are defined in the csv_conversion.yaml. The header-level list always has the name Main. The main list’s schema defines what columns to repeat for child lists. You can also specify the suffix that will be added to the file name:

  listSchemas:
    # Schema for the "main" header-level list, e.g., claims or payments
    - names: [ Main ]
      # Repeat these fields on the first line for child lists
      repeatFields: [ Id, PatientControlNumber ]
      # Repeat these fields from the repeatFields on every row for child lists
      repeatEachRow: [ "*" ]
      # By default, the claim-level list doesn't have a suffix, but you can override it here
      fileSuffix: Claims

You can also combine lists in a single file, repeating columns from the parent list in the child list. You define what columns to repeat in the main list’s schema using the repeatFields property. repeatFields applies to the first row. You can use repeatEachRow to define columns to repeat for subsequent rows.

In the example below, we specify that all columns from the claim level will be repeated for the first line using the * wildcard. The remaining lines will only have the “Id,” “PatientControlNumber,” and “PayerControlNumber” populated.

We also suppress the generation of the claim-level file by using output: False.

  listSchemas:
    - names: [ Main ]
      output: False
      repeatFields: [ "*" ]
      repeatEachRow: [ Id, PatientControlNumber, PayerControlNumber ]

If you want to duplicate all claim-level columns for every line, use repeatEachRow: [ "*" ].

Column Exclusion/Inclusion

By default, the converter creates a column for every EDI data element. This may result in a lot of sparsely populated columns in the CSV.

You can exclude the columns from the resulting CSV file by providing exclusion patterns in the conversion schema. For example, you can exclude service provider-related columns from your 835 CSV:

  excludedFields:
    - "ServiceProvider*"

Alternatively, you can list only the columns that you want to include in the CSV using the includedFields property:

  includedFields:
    - Id
    - FileName
    - PatientControlNumber
    - ClaimStatus
    - ChargeAmount
    - PaymentAmount
    - PatientResponsibilityAmount
    - DrgCode
    - PaymentDate
    - CheckOrEftTraceNumber
    - TotalAdjAmount

Modifying Conversion Schemas Used by the CLI Tool

The default conversion schema file is in your CLI tool installation’s conf folder. You can modify this file directly or copy it into a different file.

If you created a new file, you must define the path to this file using the CSV_SCHEMA_FILE environment variable. You can also provide the path to the file using the --csv-schema-file command-line option.

Modifying Conversion Schemas Used by the API Server

Download the latest schema file from this location. Modify this file and save it to the etc/csv folder of the Docker container’s volume.

Alternatively, you can save this file in another location and create a bind mount with the source pointing to your file. The destination has to be under the container’s /app directory. Then, define the CSV_SCHEMA_FILE environment variable with the new location.

Built-in Conversion Schemas

Schema with Two Files

Creates two files, one with the claim-level fields and another with the service line-level fields. “Id,” “PatientControlNumber,” and “PayerControlNumber” (835 only) repeat in the line-level file.

This schema is the CLI tool’s default for 835/837 transactions. It is not available when using the API.

Output examples:

Single File Schema

Creates a single CSV file containing all fields from the header and service lines. The first line for the claim includes all claim-level data; subsequent lines contain only the following fields:

Id
PatientControlNumber
PayerControlNumber (835 only)

For 834, all member-level fields repeat at the “health coverage” level.

This schema is the default for the API edi/csv operation. It is also the default for 834 transactions.

When using the CLI tool, you can select this schema by providing --single-csv option.

Our UI also uses this schema when selecting “Export/All Fields” or “Download CSV/All Fields.”

Output examples:

For more examples, go to any EDI example on the site and click “Export/All Fields.”

We also provide a variation of the single file schema with all header-level values repeated on every line. To select this schema, use the --csv-schema-name=lines-with-header-repeat-each-row option in the CLI and schemaName=lines-with-header-repeat-each-row parameter in the API.

Key Fields Schema

Creates a single file with only the most essential fields from 835/837.

To select this schema, use the key-fields schema name.

To see examples, go to any example on the site and select “Export/Key Fields.”

Here is the list of fields:

835

Claim-level fields:

Id
FileName
PatientControlNumber
ClaimStatus
ChargeAmount
PaymentAmount
PatientResponsibilityAmount
DrgCode
PaymentDate
CheckOrEftTraceNumber
TotalAdjAmount
Adj*ReasonCode
Adj*Amount
PayerIdentifier
PayerName
CoverageAmount
PatientIdentifier
PatientLastName
PatientFirstName
ServiceDateFrom
ServiceDateTo

Line-level fields:

LineControlNumber
LineProcedureCode
LineRevenueCode
LineChargeAmount
LinePaidAmount
LineUnitCount
LineServiceDateFrom
LineServiceDateTo
LineTotalAdjAmount
LineAdj*ReasonCode
LineAdj*Amount
LineAllowedAmount
LineRemarkCode*

837P

Claim-level fields:

Id
FileName
PatientControlNumber
ChargeAmount
PlaceOfService
FrequencyTypeCode
TransactionCreationDateTime
ServiceDateFrom
ServiceDateTo
SubscriberIdentifier
SubscriberLastName
SubscriberFirstName
SubscriberPayerIdentifier
SubscriberPayerName
PatientPaidAmount
OtherSubscriberPayerPaidAmount
Diag*

Line-level fields:

LineControlNumber
LineProcedureCode
LineProcedureModifier*
LineChargeAmount
LineUnitCount
LineServiceDateFrom
LineServiceDateTo

837I

Claim-level fields:

Id
FileName
PatientControlNumber
ChargeAmount
FacilityCode
FrequencyTypeCode
TransactionCreationDateTime
StatementDateFrom
StatementDateTo
AdmissionDateAndHour
SubscriberIdentifier
SubscriberLastName
SubscriberFirstName
SubscriberPayerIdentifier
SubscriberPayerName
PriorAuthorizationNumber
PayerClaimControlNum
DrgCode
PrincipalDiag
AdmittingDiag
OtherDiag*
PrincipalProcedure
OtherProcedure*

Line-level fields:

LineControlNumber
LineRevenueCode
LineProcedureCode
LineChargeAmount
LineUnitCount
LineServiceDateFrom
LineServiceDateTo