How to Convert Medical Records to a Spreadsheet

To convert medical records to a spreadsheet, run the scanned billing pages through OCR that detects tables, export an Excel workbook, and copy each provider's charge lines onto one master sheet with a column for the source page. Only the tabular parts of a record belong there. Narrative notes stay as text.

The goal for most firms is a medical specials summary: every charge, who billed it, what was paid, what is still owed, and where the figure comes from. The steps below get you there from a stack of scanned statements without retyping the numbers.

Provider totals on the master sheet are the figures you carry into a special damages list or a compensatory damages schedule. Search each converted statement for the total, the adjustments, and the balance. These billing pages are the money record in the injury case. Confirm every line on the image before you add it, or a repeated page will inflate the sum.

Which parts of a medical record belong in a spreadsheet?

The parts of a medical record that belong in a spreadsheet are the ones already laid out in rows: itemized bills, account ledgers, insurer payment summaries, pharmacy printouts and lab panels. Clinical notes do not. A history or an exam finding loses its meaning when it is chopped into cells.

A quick sort before you convert anything saves time.

That last row matters. Providers often send a one-page statement showing only a balance. It cannot support a line-by-line schedule. Request the itemized statement and the payment ledger by name, for the full date range, before you build anything.

Narrative pages have their own workflow. The post on a working method to OCR medical records covers splitting a set, checking it and building a chronology from it.

Pulling billing tables out of scanned statements

Pull billing tables out of scanned statements by gathering the billing pages for one provider into a file, uploading it, checking the tables in the parsed view, and exporting an Excel workbook. Each detected table arrives on its own sheet, labelled with the page it came from.

1. Separate bills from chart notes. A provider's records and its bills usually arrive together. Put the billing pages in their own PDF, and note the original page range in the file name.
2. Upload the file. Schema Law takes PDF, PNG or JPEG files up to 20 MB. Excel and CSV exports are included on every plan. The free plan takes one document a day of up to three pages, so it will give you a workbook from a short bill and show you how well your pages read. The pricing page lists the page allowance of each plan.
3. Look at the parsed pages. Before exporting, compare a page of the parsed result with the scan. You are checking that the grid was found: one charge per row, amounts in their own column.
4. Export Excel. In the workbook, every table has a sheet named with a running number and its page, such as Table 4 (p7). The first row of each sheet is bold. A final sheet named Text holds everything that was not in a table, with a page number beside each block.
5. Convert the amounts to numbers. Exported cells hold text exactly as it was read. A cell showing $1,250.00 will not add up until it is a number. Select the column, strip currency signs and commas, and convert. Excel's VALUE function or Text to Columns will do it.
6. Check a total. Add the charge column and compare it with the total printed on the statement. If they match, the rows were read correctly. If they do not, the difference tells you roughly what to look for.

The mechanics of table detection, and what to do when columns shift or merge, are covered in the guide to getting tables out of a scanned PDF and into Excel. If your case management system wants a flat file instead of a workbook, see the post on scanned PDF to CSV.

What to do with the Text sheet

Do not ignore the Text sheet. Billing pages carry facts outside the grid: the account number, the patient name, the guarantor, the statement date and the remit-to address. Those lines land on the Text sheet with their page numbers. Use them to confirm that each table belongs to the right patient and the right account before you merge anything.

What columns does a damages spreadsheet need?

A damages spreadsheet needs, at a minimum, a date of service, a provider, a description, the amount billed, what was adjusted, who paid what, the balance, and a source reference. Every other column is optional. Those eight let you total by provider, sort by date and prove each figure.

Three rules keep the sheet honest.

• One charge per row. Do not enter a visit total when the bill gives lines. You will need the lines when a single charge is disputed.
• Keep every money column, even those you may not claim. Whether a jurisdiction lets a plaintiff present the amount billed, the amount paid, or something else is a legal question that varies from state to state. Check your own. A sheet that holds all the figures can answer whichever version applies.
• Format codes as text. A code with a leading zero or a trailing letter will be mangled if Excel treats it as a number.

Add a check column that computes billed, less adjustments, less payments, and compare it with the balance column. Rows where the two differ are either misread or show a ledger entry you have not accounted for.

Billing codes, adjustments and write-offs

Billing codes, adjustments and write-offs are the three fields that go wrong most often in a converted bill, because codes are short strings where one wrong character produces another valid code, and reductions are printed in several different ways. Give each its own column and read each against the page.

Procedure codes. Professional bills usually identify services with CPT codes. The American Medical Association, which maintains the set, describes it as a listing of terms and five-digit codes for medical services and procedures, and notes that the codes can be numeric or alphanumeric. A five-digit code gives recognition software no context. A 3 misread as an 8 produces another five-digit string that looks just as valid. When the bill prints a description beside the code, check that the two still agree after conversion.

Diagnosis codes. ICD-10-CM is, in the words of the Centers for Disease Control and Prevention, a standardized system used to code diseases and medical conditions. These codes mix letters and digits, which is where recognition confuses a letter O with a zero, or a letter I with a one. Put diagnosis codes in a separate column from procedure codes so that a sort does not mix them.

Claim forms. Some billing records arrive as copies of the claim itself. The Centers for Medicare and Medicaid Services describe the CMS-1500 as the standard claim form used by a non-institutional provider or supplier to bill Medicare. On a scanned copy, typed entries sit inside small preprinted boxes and often drift over the box lines. Expect to correct more cells from a claim form than from an itemized statement.

Adjustments and write-offs. A reduction may appear as a negative number, a number in parentheses, a number followed by a minus sign or "CR", or a positive number in a column headed "Adjustments". Decide on one convention for your sheet, normally reductions as negatives, and convert each provider's style to it. After a find-and-replace, spot-check that parentheses became minus signs and did not simply vanish.

Label what kind of reduction each one is where the ledger says. A contractual adjustment under an insurer's agreement, a charity write-off and a balance sent to collections are different events, and they may be treated differently in your case. Copy the ledger's own wording into a note column and leave the characterization to the lawyer.

Bills from several providers in one workbook

Put bills from several providers in one workbook by keeping each provider's exported sheets as they are and copying their rows onto a single master sheet with fixed columns. The raw sheets are your audit trail. The master sheet is the one you sort, total and hand over.

A layout that works:

1. One raw tab group per provider. Rename the exported sheets with a short provider prefix, such as ORTHO Table 2 (p3), so their origin stays visible after you move them into the combined workbook.
2. A mapping step. Each provider names its columns differently. One says "Charges", another "Amount", a third "Total Fee". Map each to your master columns once and write the mapping on a notes tab.
3. A master tab. Paste the mapped rows in, with the provider and source filled on every row.
4. A summary tab. A pivot table by provider gives the totals a demand letter needs: billed, adjusted, paid, outstanding.

Watch for these when sources are combined.

• The same charge twice. A hospital's bill, the insurer's explanation of benefits and a collection agency's letter can all show one charge. Match on date of service, provider and amount, and keep one row, noting the other documents as further sources.
• Facility and professional bills for one visit. An emergency visit commonly produces a hospital bill and a separate physician bill. Those are two real charges, not a duplicate. The billing entity column tells them apart.
• Statement dates in the date column. Monthly statements repeat old charges under a new statement date. Use the service date, and drop repeats.
• Running balances. A ledger's balance column is cumulative. Do not add it up.

Tying each spreadsheet row back to a page

Tie each spreadsheet row back to a page by filling the source column at the moment you paste a row into the master sheet, using the file name and the page number carried in the exported sheet's name. A figure that cannot be traced to a page should not go into a demand or a disclosure.

The export gives you the page for free. A sheet named Table 4 (p7) came from page 7 of the uploaded file. If that file was pages 301 to 340 of the provider's production, page 7 is page 307 of the set. Write the source in the form you will cite: Valley Ortho billing, p. 307, or the Bates number if the set carries them.

A worked example follows. The figures are invented and the codes are placeholders that show the layout only:

Before the sheet leaves your hands, verify it in two passes.

• Totals. For each provider, the sum of your billed column should equal the total charges printed on that provider's statement. Record the comparison on the notes tab.
• Samples. Open the scan beside the sheet and read every row for small sets. For large ones, read every row above a dollar threshold you choose, plus a random handful below it. Digits that recognition confuses, such as 3 and 8 or 1 and 7, will not always break a total if two errors offset.

Keep the scanned statements, the untouched export and the master workbook together in the matter file. When someone asks where a number came from, the answer is a page you can open.

Billing records name the patient and describe the care, so treat them as carefully as the chart itself. Schema Law makes no claim to comply with HIPAA or any similar standard. Decide under your own obligations whether a billing record may be uploaded to any online tool, and read the commitments listed on the homepage as they are written.