EOB Data Extraction to Excel: Schema and Workflow

EOB data extraction to Excel works best when the workbook has one row per adjudicated service line. Repeat the claim and statement identifiers needed for reconciliation, preserve member IDs and other identifiers as text, retain the raw group, CARC, and RARC values, and join payer-legend descriptions without replacing those source codes. Then validate the service lines against claim, statement, and check or EFT totals.

Route the source correctly before extracting anything. A PDF, scan, or image of an Explanation of Benefits is a document: it needs layout-aware extraction or EOB OCR to recover fields and line items. A native X12 835 electronic remittance advice is already structured EDI. It should normally go through an 835 parser, not an EOB parser designed to read page content. Converting an 835 to pixels and running OCR over it throws away structure that the file already contains.

For document inputs, the target is not a page of recognized text. It is a typed, reviewable table that billing staff can filter for payment posting, denial work, and reconciliation. A workflow built to extract EOB PDFs into structured spreadsheet data should define the row grain, columns, data types, and exception rules before the batch runs.

The wider administrative opportunity is substantial. The 2025 CAQH Index reports that U.S. healthcare avoided an estimated $258 billion in administrative costs in 2024 through electronic transactions and improved data exchange. An EOB workbook is a much narrower artifact, but the same principle applies: structured data creates value only when staff can trust what each row and column means.

Build one row per adjudicated service line

Use the adjudicated service line as the workbook's grain. If one claim contains four service lines, it produces four rows. Repeat the payer, statement, patient, claim, and payment identifiers on each row so every record remains usable after filtering or export.

Repetition does not make those values line-level facts. A statement payment total remains a statement-level value, and a claim total remains a claim-level value even when both appear on several rows. Label the scope explicitly and never sum a repeated statement or claim total down the table. This is the main structural difference between a reliable Explanation of Benefits data extraction and a flat collection of numbers.

Column	Excel type	Source scope	Operational purpose
Payer Name	Text	Statement	Filter and group payer layouts
Statement ID	Text	Statement	Tie rows back to one EOB
Patient Name	Text	Claim	Match the adjudication to the patient record
Member ID	Text	Claim	Match payer membership without losing leading zeroes
Claim Number	Text	Claim	Locate the payer claim
Service Date	Date	Service line	Match the billed encounter
Source Line Key	Text	Service line	Distinguish repeated CPT/date lines and trace the row to its source position
CPT/HCPCS	Text	Service line	Identify the adjudicated service
Billed Amount	Currency	Service line	Record the submitted charge
Allowed Amount	Currency	Service line	Record the payer's allowed amount
Deductible	Currency	Service line	Isolate deductible responsibility
Coinsurance	Currency	Service line	Isolate coinsurance responsibility
Copay	Currency	Service line	Isolate copay responsibility
Adjustment Amount	Currency	Service line	Record the line adjustment
Paid Amount	Currency	Service line	Record the adjudicated line payment
Group Code	Text	Service line	Preserve the adjustment responsibility category
CARC	Text	Service line	Preserve the claim adjustment reason code
RARC	Text	Service line	Preserve supplemental remittance detail
Payer Code	Text	Service line or document legend	Retain a payer-specific code exactly as shown
Payer-Code Description	Text	Joined legend	Make the raw payer code readable and searchable
Check/EFT Reference	Text	Statement or payment	Tie the EOB to the payment record
Review Needed	Text	Extracted or calculated	Explain an ambiguity, unmatched code, or failed control

Add source-level columns when the EOB supplies useful claim or statement totals, but name them unambiguously, such as Claim Paid Total (Source) and Statement Payment Total (Source). Calculated control totals belong in separate columns. That separation lets a reviewer compare extracted evidence with workbook arithmetic instead of obscuring a discrepancy.

Build Source Line Key from stable source coordinates, such as payer, statement ID, claim number, and the printed or extracted line sequence. Duplicate CPT codes on the same date then remain distinguishable. Put claim-only and provider-level adjustments in separately scoped tables instead of inventing a service line for them.

An EOB is the payer's adjudication record; it is not the provider's bill to the patient. If the task starts from the amount the patient was asked to pay, use a patient billing statement-to-Excel workflow and reconcile it to the EOB as a separate document. Combining both document types into one extraction schema blurs submitted charges, payer adjustments, patient responsibility, and the provider's final statement balance.

Protect member IDs and references from Excel coercion

Member IDs, claim numbers, statement IDs, check or EFT references, CPT/HCPCS values, and raw adjustment codes are identifiers, even when a payer prints only digits. Store them as Text. Otherwise Excel may remove leading zeroes, convert long strings to scientific notation, or retain only 15 digits of numeric precision.

Formatting a damaged cell as Text afterward is not a repair. If Excel imported member ID 001274 as the number 1274, the two leading zeroes are already gone. If it rounded a long payment reference, the original digits are no longer present in the cell. Re-import from the source with the correct type.

For an EOB to CSV workflow, use Power Query rather than opening the CSV by double-clicking it:

1. In Excel, choose Data, From Text/CSV, select the file, and choose Transform Data.
2. In Power Query, inspect the Applied Steps pane. Remove the automatic Changed Type step if it has already converted identifiers.
3. Select the identifier and code columns and assign the Text data type before loading the table.
4. Assign Date to service dates and the appropriate fixed-decimal or currency type to financial columns deliberately.
5. Load the result, then compare several identifiers against the EOB pages or extracted source values.

The legacy Text Import Wizard can achieve the same result: mark each identifier column as Text before completing the import. The important control is timing. The type must be assigned before numeric coercion occurs.

Direct XLSX output avoids the CSV-open behavior, but it does not remove the need for a schema check. Confirm that identifiers arrived as text, dates as dates, and amounts as numeric values before formulas, joins, or payment-posting exports depend on them.

---

Resolve denial codes with a two-table payer-legend join

Do not collapse every adjustment into one "denial code" field. CMS payment and remittance guidance distinguishes three code sets used for line- or claim-level adjustments:

• A group code assigns financial responsibility for the unpaid portion. CO indicates a contractual obligation, while PR indicates patient responsibility.
• A Claim Adjustment Reason Code (CARC) gives the overall reason for the financial adjustment.
• A Remittance Advice Remark Code (RARC) can add a more specific explanation.

The values may appear together in compact forms. In the current X12 CARC list, CO-45 assigns the adjustment to contractual obligation and says the charge exceeds the applicable fee schedule, maximum allowable amount, or contracted or legislated fee arrangement; PR-1 assigns the deductible amount to patient responsibility; and CARC 16 identifies missing information or a submission or billing error and requires an accompanying remark code. RARC N290 specifies a missing, incomplete, or invalid rendering-provider primary identifier in the current X12 RARC list. These are working examples, not a permanent code catalog, so keep current authoritative code references outside the workbook's hard-coded logic.

Payer-specific legends create a second problem. A service line may contain a short code whose description appears only in a legend on another page. Model this as two tables rather than asking the extraction to replace the code with prose.

Service-line table

Payer	Statement ID	Claim Number	Line	Group Code	CARC	RARC	Payer Code	LegendKey	Payer-Code Description
Example Health	EOB-1042	0093817	1	CO	45		A7	Example Health|EOB-1042|A7	Description printed in this EOB's legend

Legend table

Payer	Statement ID	Raw Code	LegendKey	Description
Example Health	EOB-1042	A7	Example Health|EOB-1042|A7	Description printed in this EOB's legend

Create the same composite key in both tables, for example by joining Payer, Statement ID, and Raw Code with a delimiter. An Excel lookup can then return the description while leaving Payer Code untouched:

=XLOOKUP([@LegendKey], Legend[LegendKey], Legend[Description], "UNMATCHED")

For recurring batches, load both tables in Power Query and use Merge Queries on the payer, statement, and raw-code columns. This is easier to refresh and exposes duplicate matches before they silently multiply service-line rows.

Set Review Needed when a code has no legend match, matches more than one description within the same document, or appears without enough document context to choose safely. The raw code remains the audit evidence; the joined description is an interpretation layer that can be refreshed when a payer changes its legend.

Reconcile service lines, claims, and the payment

A complete-looking table can still be wrong. Add controls at the same levels the EOB uses, and keep every extracted source total separate from its calculated counterpart.

Start at the service-line level. Group rows by payer and claim number, then compare the sum of line paid amounts with Claim Paid Total (Source) when that total appears on the EOB. Repeat the control from claim totals to Statement Payment Total (Source). Finally, match the statement's payment amount and check or EFT reference to the bank, lockbox, or payment record used for posting.

Useful control columns include:

• Claim Paid Total (Calculated)
• Claim Paid Variance
• Statement Paid Total (Calculated)
• Statement Paid Variance
• Payment Reference Matched
• Review Needed Reason

Do not impose one universal equation on every payer. An EOB may present contractual adjustments, patient responsibility, interest, recoupments, reversals, or provider-level balance adjustments at different scopes. Some source documents support a clean line-to-claim rollup; others do not expose all components needed to reproduce a displayed total. A non-zero variance is an exception to investigate, not permission to edit the extracted amount until the formula balances.

The review queue should identify the exact failure: "line paid total differs from claim source total," "statement total includes an unallocated provider adjustment," or "EFT reference not found." That turns EOB payment posting into exception-led work. Balanced claims can move through the posting process, while denial staff filter group codes, CARCs, RARCs, and payer descriptions for follow-up.

The same controls become more valuable when billed services and payer adjudication arrive from different sources. A multi-payer OT superbill and EOB reconciliation, for example, must match the provider's service lines to each payer's claim identifiers and decisions before treating a payment as settled.

---

Use an extraction prompt that defines the workbook

Treat the prompt as an output contract, not a request to "read the EOB." It should specify the row grain, field names, types, joins, and review behavior. Test this starting point on a representative payer sample and adapt it to the posting rules used by your organization.

Extract the attached EOB documents into one row per adjudicated service line.

Return these columns: Payer Name, Statement ID, Patient Name, Member ID, Claim Number, Service Date, Source Line Key, CPT/HCPCS, Billed Amount, Allowed Amount, Deductible, Coinsurance, Copay, Adjustment Amount, Paid Amount, Group Code, CARC, RARC, Payer Code, Payer-Code Description, Check/EFT Reference, and Review Needed.

Preserve Member ID, Statement ID, Claim Number, CPT/HCPCS, Check/EFT Reference, Group Code, CARC, RARC, and Payer Code as text exactly as printed, including leading zeroes.

Repeat statement- and claim-level identifiers on each applicable service-line row, but do not present repeated totals as line-level amounts. Keep source statement totals and source claim totals in clearly named separate columns when present.

Create Source Line Key from payer, statement ID, claim number, and the printed or extracted line sequence. Return claim-only and provider-level adjustments in separate tables with their own scope; do not force them onto a service-line row.

When a payer legend is present, extract a separate legend table with Payer Name, Statement ID, Raw Code, and Description. Join the description to each matching service line while preserving the raw code. Do not guess an unmatched or ambiguous description.

Use null for values not present in the document. Set Review Needed with a specific reason when text is unclear, a code has no unique legend match, a row cannot be assigned confidently to a claim, or extracted totals are inconsistent with totals printed on the EOB.

This structure holds the required output steady while payer page layouts change. It also makes an EOB OCR result testable: reviewers can verify a defined field, join, or variance instead of judging a block of recognized text.

Invoice Data Extraction accepts PDF and image inputs and supports custom field and line-item extraction from natural-language instructions. Results can be downloaded as XLSX, CSV, or JSON, including for batch processing. When a result requires manual verification, Review Needed can identify what to check and point back to the source context without changing the extracted value. These capabilities can implement the prompt above, but they do not remove the need to validate specialized EOB layouts before production use.

Pilot across payers before scaling the batch

Start with a small set that represents the work you will actually process. Include the highest-volume payers, multi-page EOBs, denials, reversals or offsets, faint scans, legends that repeat on several pages, and unusual check or EFT references. A clean sample made only of straightforward paid claims will not exercise the joins and exceptions that determine whether the workflow is production-ready.

Measure the pilot against the workbook contract:

• Are all required fields present at the correct statement, claim, or service-line scope?
• Did every identifier retain its original characters and leading zeroes?
• What share of payer codes joined to one description, and which remained unmatched or duplicated?
• Do calculated claim and statement controls agree with source totals where the layout supports comparison?
• Does each Review Needed reason tell a biller what to inspect?

EOBs contain protected health information, so the extraction step cannot be assessed in isolation. Invoice Data Extraction uses HTTPS/TLS in transit, AES-256 encryption at rest, and Row-Level Security for per-account data isolation. Uploaded documents, processing logs, and planning-conversation content are deleted within 48 hours; generated outputs are retained for 90 days, and users can manually delete files and results from the dashboard at any time. These controls do not establish HIPAA certification, and Invoice Data Extraction is not independently certified. A regulated organization must assess the full workflow, contractual safeguards, user access, retention, exports, and downstream handling against its own obligations.

Lock the schema and prompt only after the sample passes the agreed controls. Define which variances or unmatched codes require review, preserve the source document and raw extracted values for the applicable audit period, and then expand payer coverage while monitoring the exception rate. Medicaid programs can add their own remittance and reconciliation requirements; the school district Medicaid remittance extraction workflow shows that narrower operating context.