Medical Records PDF to Excel for a Malpractice Timeline

To move the clinical tables in medical records from PDF to Excel, pull the medication administration record, the vital sign flowsheets and the lab results out of the hospital chart, convert each to a workbook, and reshape them into one row per event with a date, a time and a page reference. Narrative notes stay as searchable text.

A malpractice case is argued in minutes and milligrams: when a reading changed, when someone was told, when a drug went in. Those facts sit in the chart's tables, spread across hundreds of pages in the order the hospital's system chose to print them. A spreadsheet lets you put them in the order they happened.

Flowsheet times are easier to trust when you can find the matching consent page. Search the converted orders for informed consent language and set those clock times beside the medication rows. The chart is what a medical malpractice case uses when people discuss the standard of care. Confirm each dose on the image, and note any health information privacy legend before identifiers go into the workbook.

Which pages of a hospital chart are tables worth exporting?

The pages worth exporting are those where each row or column is one timed event: the medication administration record, vital sign and intake and output flowsheets, lab result tables, and order lists. Narrative pages, such as progress notes and consult reports, belong in a searchable PDF and not in cells.

Section names differ from one hospital's printout to the next, so go by what a page looks like. The names used here are the common ones. Check them against the table of contents or section headers of your own chart.

Choose by the theory of the case. If the claim is a delayed response to falling blood pressure, the vitals, the orders and the medication record for a two-day window are the tables to convert, and the rest of the stay can wait. Converting an entire admission's flowsheets when twelve hours matter spends your checking time in the wrong place.

Billing tables are a different job with a different purpose. Itemized charges and payments go into a damages schedule, which the post on how to convert medical records to a spreadsheet sets out.

Why does an electronic health record printout run to thousands of pages?

A chart printed from an electronic record is long because a print job turns every stored entry into lines on paper and repeats the page furniture on every sheet. Each flowsheet cell, dose and result becomes its own line, and the same data can print in more than one report.

Look at your own printout for these causes of bulk. Which ones apply depends on the system and the report settings the records department used:

• Headers and footers on every page. Patient name, record number, encounter number, print date and page count repeat on each sheet.
• One line per recorded value. A set of vital signs that filled one column on a screen may print as five or six lines, each with its own time and the name of the person who entered it.
• The same data in several reports. A lab result may appear in the results section, again in a cumulative summary, and again quoted inside a progress note.
• Copied-forward note text. Daily notes may carry forward earlier text, so a paragraph appears on many dates.
• Reference text. Ranges, legends, and explanations of abbreviations may print with each table.
• Empty rows. A flowsheet template prints every row it has, used or not.

Three things follow for conversion. First, find the tables before converting anything: page through the PDF and write down the page range of each section, since converting two thousand pages to find sixty is waste. Second, treat repeats as repeats. When the same result appears in three places, your timeline needs it once, with the page of the primary report as the source. Third, read the header on every extracted range. A chart can contain more than one encounter, and a medication record from last year's admission looks exactly like this year's.

Ask what you were given. A printout is a report generated from the record, and the settings chosen at print time decide what it includes. If entries you expect are absent, the question for the records custodian is whether a different report or format exists. That is a discovery question for the lawyer, and the page inventory you built is the evidence for asking it.

For the surrounding work of splitting, naming and converting the full set, follow a method for converting a records set.

Medication administration records as spreadsheet rows

A medication administration record converts to useful rows when each row ends up as one dose event: drug, dose, unit, route, scheduled time, time given, status and who recorded it. The printed layout decides how much reshaping that takes, so identify the layout first.

The term is used here in its common sense, a record of each dose given to a patient during a stay. Look at your pages to see which form they take.

Two layouts

List layout. One printed line per dose event, with the drug name repeated or shown as a heading above a block of lines. This is close to what you want. After export, fill the drug name down into every row beneath its heading, and you have a table.

Grid layout. Drugs down the left side, dates or hours across the top, and a time with initials in each cell where a dose was given. A paper record completed by hand may take this form, and so may a printed report. The export gives you the grid as a grid. To get one row per dose, you have to unpivot it: each filled cell becomes a row carrying the drug from its row label and the date from its column header. Excel's Power Query has an Unpivot Columns command for this, described in Microsoft's support article on how to unpivot columns in Power Query, or you can do it by hand for a short window.

The target columns

Keep dose and unit apart. A cell reading 0.5 mg sorts and filters badly, and splitting it makes a missing decimal point easier to see.

Things that go wrong

• Drug headings lost. When a drug name sits on its own line above its doses, a conversion may put it on a separate row. Filling down fixes it, but check the first dose under each heading against the scan.
• Wrapped lines. A long drug name or comment wraps onto a second line and becomes a second row with empty columns. Merge it back into the row it belongs to.
• Infusions. A continuous infusion is recorded as a start, rate changes and a stop, not as doses. Give those rows a Rate column and do not add them up as if they were doses.
• Statuses in small print. "Held" and "Given" are short words in a narrow column. A dose that was held and reads as given changes the story. Read the status of every row you rely on.
• Initials and handwriting. On a handwritten grid, recognition is weaker than on print, and nothing in the converted output marks an uncertain cell. Transcribe the window that matters by hand, with a second reader.

Schema Law reads each page into text and tables and shows the parsed pages on screen, so you can see whether a medication record came through as a table before exporting it. Excel and CSV exports are on every plan, and the plans listed on the pricing page differ in how many pages you can convert.

Vital sign flowsheets and lab result tables

Flowsheets and lab tables are the same shape of data, a named measurement with a value at a time, and both belong in a long table: one row per reading with columns for date, time, measure, value, unit and page. That shape sorts by time and filters by measure, which a wide printed grid cannot do.

Flowsheets

Where a printed flowsheet has a column for each time a set of readings was taken, a stay of several days produces more columns than a page holds, and the grid continues on the next sheet with the row labels repeated. After export you have several tables that are really one. The post on Excel conversion explains tables that span several pages and how to rejoin them.

Before unpivoting, check on each sheet that:

1. the date belongs to the right columns. A date may print once above the first column of the day and not above the others.
2. the row labels match across pages. If one page has a row for oxygen flow and the next does not, the rows beneath it have shifted up.
3. blood pressure stayed in one cell. 118/76 can split into two cells or be read as a date or a fraction by Excel. Format the column as text before pasting.
4. empty cells are still empty and in place. A reading that slid one column to the left now carries the wrong time.

Then unpivot to the long shape:

The values are invented to show the layout.

Lab results

A lab report gives more than a number. For each result, capture the test name, the value, the unit, the reference range printed beside it, any flag such as H, L or a critical marker, and the times the report prints. A lab table may print several times for one result, for example when the sample was collected, when the lab received it and when the result was reported. Keep each in its own column. For a claim about delay, the gap between them is the evidence.

Points to check on converted lab tables:

• Flags detached from values. A flag is one letter in a narrow column. If it lands in the next row, a normal result looks abnormal and the reverse.
• Symbols. Results such as <0.01 or >100 contain a sign that changes the meaning. Keep the cell as text and compare with the scan.
• Cumulative reports. A summary that shows several days side by side repeats earlier results. Take each result once.
• Comments. A note such as "specimen hemolyzed" or "critical value called to" a named person at a stated time prints under the result. Copy it into a comment column, since it can be an event in its own right.

A disability claim file can hold the same kinds of tables inside its treatment records, and the post on a Social Security disability file covers converting treatment records in the medical evidence section.

How do you merge chart entries from several sources into one timeline?

Merge them by giving every source the same six columns, stacking the rows on one sheet and sorting by a combined date and time. The columns are date, time, source, event, detail and page. Tables supply their rows directly. You add the events from narrative notes by hand, each with its page.

Normalize first

1. One date-time column. Combine date and time into a real Excel date-time so the sort is exact. Where the chart prints times in 24-hour form, a cell of 0315 needs to become 03:15, and you should confirm that 0015 did not lose its leading zeros and turn into 15.
2. Watch midnight. In a grid, the date header may not change at the column where the day does. A reading at 0030 filed under the previous day's date will sort 24 hours early.
3. A source column. MAR, Vitals, Lab, Orders, Nursing note, Physician note. This is what you filter on.
4. A time-type column. Say what the time is: event time, entry time, collected, resulted, ordered. Where the printout shows both when something happened and when it was written down, keep both.

A merged excerpt

All names and values are fictional.

Sorted this way, the chart answers the questions an expert will ask: what was known at each moment, and how long each step took. Add a column that computes the minutes between rows you pair up, such as page to order and order to administration.

Keep the layers separate

• The untouched exports stay in their own workbook.
• The cleaned long tables for each source stay on their own sheets.
• The merged timeline is built from those sheets, never typed over.
• A Verified column on the timeline records who read the row against the chart page and when.

A timeline that mixes verified and unverified rows without saying which is which is a risk to the person who relies on it. Give the expert the timeline with the verification column showing, and the chart PDF with the same page numbers.

The post on building a timeline from converted police reports treats the same problem of conflicting times from several writers in a different kind of file.

Checking times, doses and units against the chart page

Check every row the case depends on by reading it against the chart page: the time, the number, the decimal point, the unit and the status. Nothing in converted output marks a doubtful character, and clinical tables are made of short strings where one wrong character yields another value that looks just as plausible.

What to read, and why

The general patterns of digit confusion are set out in the post on why OCR gets numbers wrong.

Tests the sheet can run for you

These find candidates. They do not clear a row.

• Order test. Within one printed table, times should run in sequence. A formula that flags any time earlier than the one before it catches misread hours.
• Range test. Filter each measure for values outside what a human body produces. A heart rate of 812 is a misread, and so is a temperature of 3.72.
• Outlier test for doses. For each drug, list the distinct doses. A lone 50 among rows of 5.0 is the first cell to open.
• Count test. Count the rows your sheet has for a page and count the entries on the page.
• Unit list. A pivot of drug by unit shows any drug that appears with two different units.

Who checks

Have a second person read the rows that will be quoted, and record both names. Where the firm has a nurse consultant, the unit and dose check is theirs: a reader who knows the drug will spot an impossible dose that a formula passes. A doubtful cell is settled by the chart page, and if the page itself is unclear, say so in the row and do not pick a value.

Cite the chart page for every figure that leaves the firm, and keep the PDF, the raw export and the timeline together. The export formats on the homepage list what the Excel, CSV and searchable PDF files each contain.

A hospital chart names a patient and describes their care in detail. Whether it may be uploaded to any online service is a decision for the firm under its own obligations and any agreement or order covering the records. Schema Law does not claim HIPAA compliance or any other certification. Its homepage states four commitments about uploaded documents, and they should be read as written.