On this page
Why a Dirty Export Is a Data-Integrity Problem
Every temperature analysis you run, mean kinetic temperature, excursion impact, alarm tuning, cold room mapping, starts from one file: the raw export off your logger. If that file carries seven metadata rows above the real header, eighteen duplicate readings, and two different date formats in the same column, every downstream number is built on sand. A duplicate timestamp double-counts a hot reading. A misread date silently reorders your series. A power-loss gap that nobody flagged turns into a phantom flat line. The calculation does not error out; it just gives you a confident, wrong answer.
That is not only a maths problem, it is a data-integrity problem. PPB Good Distribution Practice expects your records to meet ALCOA+ principles: attributable, legible, contemporaneous, original, accurate, and complete. A dataset you hand-edited in Excel, with rows quietly deleted and dates retyped, fails accurate and complete the moment an inspector asks how you got from the raw download to the number in your deviation report. The cleaning step itself has to be defensible, repeatable, and documented.
The Data Logger QA Cleaner does that step for you. It strips the metadata, de-duplicates, normalises mixed date formats, flags the gaps where the logger lost power, and gives you a clean file plus a quality score you can attach to the record. This guide walks one real-shaped export end to end, explains each output, and shows how to write it up so it survives an audit. It is a companion to your data-handling SOP, not a replacement for it.
Worked Example: A 4,320-Row LogTag Export
Take a distributor in Nairobi pulling 30 days off a LogTag logger on a 10-minute interval. That is 4,320 rows of readings, and the export looks clean until you open it. It is not.
The raw file carries four faults that would each quietly corrupt an excursion or MKT calculation:
- Seven metadata header rows: LogTag Analyzer writes the serial number, calibration date, trip name, and logging config above the real
Date,Time,Temperatureheader. Loaded blindly, those seven rows become seven garbage "readings" and shift every column by one. - Eighteen duplicate timestamps: the file was re-downloaded mid-trip and the two exports were stitched together, so eighteen timestamps appear twice. A duplicate at 9.2C double-weights that point in any average or MKT roll-up.
- Mixed date formats: some rows read
13/06/2026(DD/MM/YYYY), others06/13/2026(MM/DD/YYYY) after a regional-settings change on the export PC. Parsed naively, the series jumps backward and forward in time and the excursion durations become meaningless. - Three dropout gaps: the logger lost mains power three times, leaving gaps of 40, 90, and 150 minutes with no readings. Left unflagged, an MKT calculation treats the gap as "in spec" and understates exposure.
The cleaner runs the file through a fixed pipeline. It detects and strips the 7 metadata rows by finding the real header, removes the 18 duplicate timestamps (keeping the first occurrence and logging the rest), normalises every date to a single ISO format so the series sorts correctly, and flags the 3 dropout gaps as explicit "no data" intervals rather than silently bridging them. You start at 4,320 raw rows and land at 4,295 valid readings with a quality score of 94 percent and three gap flags carried forward.
Now the downstream maths is trustworthy. Feed those 4,295 clean readings into the MKT calculator and the result reflects real exposure, not double-counted spikes. Run the same file through the excursion impact assessor and the excursion durations are real, because the timestamps are finally in order. The cleaning was the difference between a defensible number and a guess.
What Data You Need and Why
The cleaner is forgiving by design, because the whole point is to accept a messy file. You upload the raw export exactly as the logger produced it: do not pre-clean it in Excel first. Hand-editing before upload defeats the audit trail and is the very thing ALCOA+ warns against. Let the tool record what it changed.
Underneath the mess, the file needs two real columns: a timestamp and a temperature. Headers can sit anywhere (the tool finds the real header below the metadata), dates can be in mixed formats, and duplicate rows are fine because the tool handles them. Export raw interval readings, not chart screenshots or daily averages. Averages have already thrown away the short spikes and gaps the cleaner exists to surface.
One thing to bring with the file: your expected logging interval (10 minutes in the example). The tool uses it to tell a real dropout gap from normal spacing. A 90-minute gap on a 10-minute logger is a power loss worth flagging; the same gap on a logger set to hourly is just one missed reading. Tell the tool the interval and the gap detection is accurate.
Reading the Outputs
The tool returns five things. Read them top down:
- Cleaned row count: how many valid readings survived, 4,295 from 4,320 raw in the example. The drop is the metadata rows plus duplicates removed. If the count falls far more than you expected, the file had more noise than you thought, and that is worth investigating before you trust any analysis.
- Issues fixed: an itemised list of what changed, 7 metadata rows stripped, 18 duplicates removed, dates normalised from two formats to one. This is the heart of the audit trail. It is the answer to the inspector's question, "what did you do to this file and why".
- Gap flags: each dropout the tool found, with its start, end, and duration. The 40, 90, and 150-minute gaps appear here as explicit "no data" intervals. The tool does not invent readings to fill them; it tells you they exist so your MKT or excursion analysis can treat them honestly.
- Quality score: a single percentage (94 percent in the example) summarising how clean the dataset is after processing. A high score means the file is fit for analysis. A low score is a signal to check the logger, the export routine, or the power supply before you rely on the data.
- Downloadable clean file: the corrected CSV, de-duplicated, single date format, sorted, gaps flagged, ready to feed into the next tool. This is the file you analyse and archive, kept alongside (never instead of) the raw original.
Never read the score in isolation. A 94 percent dataset with a 150-minute gap during a heatwave still needs human judgement about what happened in that gap. Pair the outputs with operational context: generator logs, the maintenance record, the goods-in schedule. The cleaner makes the data honest; you still interpret it. If the same loggers keep producing low scores, the fault is upstream, and a cold room mapping study or a calibration check often explains why.
Documenting Cleaning for PPB and GDP Audits
Data cleaning is part of the record, not a private step that happens before the record begins. Capture it the way an inspector expects to see it:
- Keep the raw export unchanged. Archive the original file exactly as the logger produced it. This is your "original" under ALCOA+ and the thing every other artefact traces back to.
- Keep the cleaned file alongside it. Store the downloadable clean CSV next to the raw one, never overwriting it. Two files, both retained.
- Attach the issues-fixed list. The itemised changes (metadata stripped, duplicates removed, dates normalised, gaps flagged) are the audit trail that explains how the cleaned file differs from the raw one. This makes the transformation attributable and accurate, not a black box.
- Record the quality score and gap flags. Note the score and each dropout gap in the deviation or batch record so the completeness of the dataset is visible to a reviewer.
- State the expected interval you supplied, because gap detection depends on it. A reviewer needs to know the tool was told the right logging frequency.
Done this way, the cleaning step strengthens rather than weakens your data integrity: the inspector can see the raw download, the documented transformation, and the clean result, with nothing hidden in between. If you are still building these records, our GMP readiness checklist covers the data-handling and deviation documentation PPB inspectors ask for.
Frequently Asked Questions
Should I clean the file in Excel before uploading?
No. Upload the raw export exactly as the logger produced it and let the tool record what it changed. Hand-editing rows and retyping dates in Excel destroys the audit trail and is exactly the kind of undocumented change ALCOA+ and PPB inspectors flag. The cleaner's value is that the transformation is documented.
Why does the cleaner flag gaps instead of filling them in?
Because inventing readings to bridge a power-loss gap would understate real exposure. If the logger lost power for 150 minutes during a hot afternoon, an MKT calculation that quietly fills the gap with in-spec values is wrong. The tool flags each gap as explicit "no data" so your analysis treats it honestly.
How does it handle mixed date formats like DD/MM/YYYY and MM/DD/YYYY in one column?
It detects the inconsistency and normalises every row to a single format so the series sorts in true chronological order. Left unfixed, mixed formats reorder the series and make excursion durations meaningless, so this is one of the most important fixes for any downstream timing calculation.
What does the quality score actually mean?
It is a single percentage summarising how clean the dataset is after processing, reflecting how many readings survived, how many issues were fixed, and how complete the series is. A high score means the file is fit for analysis. A low score is a signal to check the logger, export routine, or power supply before trusting the data.
When in the workflow should I run this tool?
First, before any other temperature analysis. The cleaner is the preprocessing step that feeds the rest. Run your raw export through it, then take the clean file into the MKT calculator, the excursion impact assessor, the alarm threshold optimizer, or the cold room mapping analyzer.
Run the Tool with Your Own Data
Upload your raw logger export, metadata rows, duplicates, mixed dates and all, and get a cleaned CSV, an itemised issues-fixed list, gap flags, and a quality score your QA team can document immediately.
Open Data Logger QA Cleaner