Case 1: Moving Patient Data from Excel to a Database
Last updated on 2026-10-23 | Edit this page
Overview
Questions
- Which tools can help to clean spreadsheet data and move it into a database?
- How can a data steward work with data they do not own?
Objectives
- Compare spreadsheet programs, OpenRefine and Python with pandas for cleaning tabular data.
- Explain why a database system such as REDCap can be better than spreadsheets for clinical data.
- Recommend a tool that fits the project’s skills and the size of the problem.
Scarytale: Gene dating
Meta-analyses suggest that the 2nd of September plays a crucial role in the function of cells. What happened?
Play it as a yes/no guessing game, then read the solution: Gen-Datierung (spreadsheet software turns the gene name SEPT2 into a date). Alternative: Die Tabelle ist voll (UK COVID-19 cases lost at the row limit of an old Excel format).
Source: Research Data Scarytales, Kompetenznetzwerk Forschungsdatenmanagement Thüringen, CC0.
The case
A clinical research project has collected patient data in Excel spreadsheets. Now the project introduces the clinical database system REDCap, and the existing data has to be moved there. The data steward who does the migration is not the owner of the data: whenever values are missing or do not fit the new structure, he has to ask the other project members. The target structure in REDCap has to accommodate all existing data, and the existing data has to be adapted to the import template.
This case is based on a real migration, documented by Michael Rabenstein as a project of the certificate course Research Data Management: Migrating Patient Data from an Excel Spreadsheet to REDCap – Workflow from the perspective of a data steward without data ownership (CC BY 4.0).
What would you recommend?
In small groups, discuss for 15 minutes:
- What exactly is the problem? What does the project need in the end?
- Which tools could help with cleaning and transforming the data? What would the data steward have to learn for each?
- How would you document the migration and the questions to the data owners?
Tools in this case
| Tool | Good for | What the researcher needs to learn | Installation |
|---|---|---|---|
| Spreadsheet program with tidy data rules | transforming data, familiar to everyone | tidy data principles, careful import (formats) | none |
| OpenRefine | cleaning and harmonising values, no programming, records every step | point-and-click, facets, clustering | local installation |
| Python with pandas | automating repeated steps, documented workflow | basic programming | local installation or JupyterLite |
| REDCap | capturing and managing clinical data with validation | data dictionary, import template | provided by the institution |
A small first step
Where to learn more
- Library Carpentry: Tidy Data for Librarians
- Library Carpentry: OpenRefine
- Introduction to Python for Libraries and Research Data Management
- The workflow of the original case
- Spreadsheets are a common starting point, but clinical data belongs in a system with validation.
- OpenRefine helps without programming, Python pays off for repeated work.
- Documenting every change and every open question is part of a migration.