How to Convert FHIR Patient Data to Excel (CSV) Without Losing Information
Why FHIR doesn't fit a spreadsheet, how flattening works, what repeating elements lose, Excel import pitfalls, and when to use SQL on FHIR instead.
By the FHIR Toolbox team, Omindra Labs · 5 min read ·
Checked against the official specifications listed under Standards and references at the end of this article.
Sooner or later someone asks for "the FHIR data in Excel": a quality team checking demographics, a researcher counting lab results, a project manager reconciling a migration. FHIR is a tree and a spreadsheet is a grid, so every conversion makes choices about what to keep. This guide shows those choices explicitly, so the spreadsheet you hand over means what people think it means.
Why FHIR doesn't fit in a grid
A single Patient can have:
- several
nameentries (official, maiden, nickname), each with severalgivennames, - several
identifiers from different systems, - several
telecomandaddressentries, - extensions nested at any level.
A spreadsheet row has one cell per column. To turn a resource into a row, every nested path becomes a column name, and every repeating element needs a rule: take the first entry, join them all, or make a row per entry.
How the FHIR Toolbox converter flattens
The FHIR to CSV converter produces one row per resource (per Bundle.entry for a Bundle, or a single row for one resource) and applies three rules:
- Nested objects become dotted paths.
name[0].familybecomes the columnname.family, andaddress[0].citybecomesaddress.city. - Arrays of simple values are joined with
;.given: ["Amy", "V."]becomesAmy; V.. - Arrays of objects keep only the first entry, and a
_countcolumn records how many there were. A patient with two names gets the first name's columns plusname._count = 2.
Input:
{
"resourceType": "Bundle",
"type": "searchset",
"entry": [
{
"resource": {
"resourceType": "Patient",
"id": "p1",
"name": [
{ "use": "official", "family": "Shaw", "given": ["Amy", "V."] },
{ "use": "maiden", "family": "Baxter" }
],
"gender": "female",
"birthDate": "1987-02-20"
}
},
{
"resource": {
"resourceType": "Patient",
"id": "p2",
"name": [{ "family": "Lee", "given": ["Daniel"] }],
"gender": "male",
"birthDate": "1990-11-03"
}
}
]
}
Output:
resourceType,id,name.use,name.family,name.given,name._count,gender,birthDate
Patient,p1,official,Shaw,Amy; V.,2,female,1987-02-20
Patient,p2,,Lee,Daniel,,male,1990-11-03
The columns are the union of every path found across all rows, so a patient without a use simply has an empty cell. Values containing commas, quotes or line breaks are quoted using standard CSV escaping.
What you lose — and how to tell
Rule 3 is the trade-off. In the example, Amy's maiden name "Baxter" isn't in the spreadsheet; only name._count = 2 tells you something was dropped. Before handing a file over:
- Scan the
_countcolumns. Any value above 1 means data was left out for that row. - Put the most important entry first if you control the export (for example sort
namesouse = officialcomes first). - Split by type. Convert Patients, Observations and Encounters separately. A Bundle with mixed resource types produces one wide sheet where most cells are empty for most rows.
Choosing columns deliberately: FHIRPath first
If you need a specific value, such as the *official* name, the *phone* number or the MRN from one system, extract it explicitly instead of relying on "first entry". Test the expressions in the FHIRPath playground:
| Column | FHIRPath |
|---|---|
| Official family name | Patient.name.where(use = 'official').family.first() |
| All given names | Patient.name.first().given.join(' ') |
| Mobile phone | Patient.telecom.where(system = 'phone' and use = 'mobile').value.first() |
| MRN | Patient.identifier.where(system = 'http://hospital.example.org/mrn').value.first() |
For age, export birthDate and compute it in the spreadsheet. FHIRPath date arithmetic adds and subtracts durations (today() - 18 years) but doesn't return the difference between two dates.
Opening the CSV in Excel without breaking it
Double-clicking a CSV lets Excel guess each column's type, and it guesses wrong in ways that matter for health data:
- Leading zeros vanish. MRN
00123456becomes123456. ZIP codes02134become2134. - Dates get reformatted.
1987-02-20becomes a locale-specific date, and partial FHIR dates like1987-02may become "Feb-87". - Long identifiers become scientific notation.
123456789012345becomes1.23457E+14and loses digits. - Non-ASCII characters break. Names like "José" or "Müller" show up garbled if Excel doesn't detect UTF-8.
The fix is to import instead of opening: in Excel, use Data → From Text/CSV, set the file origin to 65001: Unicode (UTF-8), choose Transform Data, and set identifier, ZIP and date columns to Text before loading. Google Sheets (File → Import) handles UTF-8 correctly, but you should still turn off "Convert text to numbers, dates, and formulas".
Observations are long, not wide
Patients map to one row each. Observations are different. The natural shape is one row per observation (long format), with columns like subject.reference, code.coding.code, effectiveDateTime, valueQuantity.value, valueQuantity.unit. If someone asks for "one row per patient with their latest HbA1c", that's a pivot you do *after* the export, in Excel or a script, not something to bake into the flattening.
Blood pressure and other panel observations keep their values in component. With first-entry flattening only the first component (usually systolic) appears, so export those with explicit FHIRPath columns instead.
When a spreadsheet is the wrong tool
For recurring reports or anything larger than a few thousand resources, look at SQL on FHIR. It's an HL7 specification that defines *ViewDefinitions*: declarative, FHIRPath-based descriptions of a table, with explicit forEach handling for repeating elements:
{
"resourceType": "ViewDefinition",
"resource": "Patient",
"select": [
{ "column": [
{ "name": "id", "path": "getResourceKey()" },
{ "name": "gender", "path": "gender" },
{ "name": "birth_date", "path": "birthDate" }
] },
{ "forEach": "name.where(use = 'official').first()",
"column": [
{ "name": "family", "path": "family" },
{ "name": "given", "path": "given.join(' ')" }
] }
]
}
The same definition runs on several engines and produces the same table every time. That's what you want for analytics pipelines.
Privacy before you export
A spreadsheet is the easiest way for patient data to leave a controlled system: it gets emailed, synced and forgotten in Downloads folders. Before exporting:
- Export only the columns the recipient needs.
- De-identify if the recipient doesn't need to know who the patients are. See De-identifying FHIR Data for what that really takes.
- Use a tool that doesn't upload the data. The FHIR Toolbox converter runs entirely in your browser, and the file you download is created locally.
Try it
Paste a Bundle into the FHIR to CSV converter, check the _count columns, and download the CSV. To preview a large Bulk Data export before choosing what to convert, open it in the Bulk Data Preview first.
Standards and references
FHIR Toolbox is a free collection of HL7 FHIR tools by Omindra Labs. The tools process your data in your browser; it is not uploaded for normal tool operations.