Digital Food Safety Log Import Template & Field Mapping

Purpose

This tool helps you move paper or legacy spreadsheet food safety records into a digital Food Safety Management System (FSMS) without losing audit trails or creating noisy false alarms. It provides a recommended field set, practical mapping notes, timestamp and unit conventions, a sample CSV layout, and a step-by-step validation checklist you can follow before and after import.

What you get

• A recommended set of import fields (names and intended use)
• Field-mapping guidance and common transformations
• Timestamp and unit format guidance (to avoid timezone and parsing issues)
• A sample CSV row you can paste into a test import
• A pre-import validation checklist and post-import reconciliation steps
• Common errors and remediation tips

Recommended import fields (use these as mapping targets)

Not every FSMS uses the same field names. Use these as canonical targets and map from your source columns accordingly.

• ImportBatchID — Unique identifier for this import job (string). Helps group rows and roll back if needed.
• SourceRecordID — Original paper or spreadsheet row identifier (if available).
• LocationID — Location or site code (use your internal location key).
• RecorderName — Person who recorded the observation (text).
• RecorderRole — Role or position (e.g., Cook, Supervisor).
• ObservationType — Short controlled value (e.g., "Fridge Temp", "Handwash", "Surface Clean"). Prefer consistent controlled vocabulary.
• ObservedValue — Numeric or text value (use numeric when value is a measurement).
• Unit — Unit for observed value (e.g., °C, °F, ppm). Keep consistent across the import or include Unit so system can convert.
• TargetMin / TargetMax — Acceptable range for numeric checks (optional but useful for automated alerts).
• TimestampUTC — ISO 8601 timestamp in UTC (recommended target). See formatting guidance below.
• RecordedLocalTimestamp — Original local timestamp (optional) with timezone if known.
• CorrectiveAction — Short description of corrective action taken (text).
• CorrectiveActionBy — Who performed the corrective action (text).
• CorrectiveActionTimestamp — When corrective action was completed (ISO 8601 preferred).
• Notes — Free-text notes, hygiene observations, or context.
• AttachmentLink — URL or storage pointer to photos/scans (if available). Prefer unique immutable links.
• ImportedBy — User who ran the import (system account or person).
• RowChecksum — Optional hash of the original row to preserve a tamper-evident trace.

Timestamp and timezone guidance

• Prefer ISO 8601 with timezone information. Example: 2026-09-14T13:45:00-04:00.
• Best practice is to convert to UTC for storage (e.g., 2026-09-14T17:45:00Z) and keep the original local timestamp as a separate field.
• If source timestamps are ambiguous (no timezone), document the assumed timezone and apply it consistently during import.
• Avoid locale-specific formats such as MM/DD/YYYY without clear context. They commonly cause mis-parses.

Unit conventions and numeric values

• Use a single numeric field for measurements and a separate Unit column. This lets the system apply consistent conversions.
• Decimal separators: convert any comma decimal separators to periods before import. Ensure values are numeric, not text with unit appended (e.g., "5.3°C").
• For temperature logs, choose either °C or °F consistently per import, or include Unit and let the system normalize.

Sample CSV row (one-line example)

ImportBatchID,SourceRecordID,LocationID,RecorderName,ObservationType,ObservedValue,Unit,TargetMin,TargetMax,TimestampUTC,Notes,ImportedBy Import_2026-09-14,BATCH001,SITE01,Jane Doe,Fridge Temp,3.7,°C,0,4,2026-09-14T17:45:00Z,"Door left ajar briefly",admin_import

Field-mapping tips

• Map any free-text observation type into a controlled list during staging — e.g., map "Fridge temp", "FridgeTemp", "Fridge Temp" to a single canonical value.
• If your source combines value and unit in one column, parse them into ObservedValue and Unit before import.
• Keep person names and IDs separate when possible (RecorderName + RecorderID) so user accounts can be linked later.
• Preserve original paper identifiers and scans. Never overwrite originals — import should be additive and traceable.

Validation checklist (follow this before running a production import)

1. Run the import into a staging/test tenant or dataset, never directly into production.
2. Verify row counts: source rows match staging rows post-import (account for header rows).
3. Check timestamp parsing on a sample of rows across timezones and midnight boundaries.
4. Confirm numeric fields imported as numbers (not text) and that decimal separators are correct.
5. Validate controlled vocabularies: every ObservationType maps to a defined type in the FSMS.
6. Test alert logic against a few out-of-range values to avoid flood of false alarms.
7. Verify attachments links resolve and permissions are correct (staging storage accessible by intended users).
8. Run a checksum comparison for a sample of source rows if you generate RowChecksum values.
9. Have a documented rollback plan (which rows/batches to delete and how to restore original data).

Common import problems and quick fixes

• Problem: Timestamps show a day-off shift after import. Fix: Confirm timezone assumptions and re-import affected batch after correction.
• Problem: Numbers imported as text (no numeric sorting/filtering). Fix: Convert the column to numeric in the CSV and re-run on staging.
• Problem: System generates many alerts post-import. Fix: Turn off automated alerts for the import batch, re-evaluate target ranges, then re-enable alerts after verification.
• Problem: Missing attachment links. Fix: Ensure attachments are uploaded to an accessible storage location and links are absolute URLs or valid storage pointers.

Suggested test plan

1. Create a small curated test CSV (10–20 rows) that covers typical values, edge cases, and intentionally out-of-range values.
2. Import into staging and verify mapping, datatypes, and alert suppression.
3. Conduct reconciliation: pick 20 random source rows and confirm every field imported correctly.
4. Document the results and only proceed to production import once all checks pass.

Retention, auditability and compliance notes

Keep the original paper/scanned records and the original import file for as long as regulations or your retention policy require. Use ImportBatchID and RowChecksum to create an immutable link between imported rows and their originals. Document who ran each import and retain logs for inspection or audit.

Next steps & templates

Use the recommended field list above to build a spreadsheet with a header row matching your FSMS target fields. Save as UTF-8 CSV. Run the staging test plan, then follow the validation checklist before production import.

When to ask for help

If your source logs include handwritten timestamps, illegible entries, or inconsistent units across rows, consider a manual cleansing step or conservative sampling approach rather than automated parsing. If import affects live alerts or compliance reporting, get your compliance owner or FSMS vendor involved.