How to migrate spreadsheet data into a web app

Preserve meaning before moving values

Begin with the current records, owners and business rules. A column named “Status” may mix workflow stage, payment state and free-form notes. Dates may use several formats; names may identify people in one row and companies in another. Moving these strings unchanged can produce a technically successful import with unreliable meaning. Profile each sheet before designing transformations.

Identify the authoritative source for each record type and the time boundary of the migration. Record who can explain ambiguous values and who may approve corrections. Keep an untouched, access-controlled export with a timestamp and source version. This guide assumes the decision to use a web app is complete; it focuses on moving approved data, not choosing whether custom software should exist.

Create a source-to-target field map

Give each source column an explicit outcome: map, transform, combine, split, archive or exclude. Never let an importer silently ignore unknown columns. For the target, record its data type, required status, permitted values, default, uniqueness rule, relationship and validation. OWASP’s input-validation guidance recommends checking data against syntactic and semantic expectations. Migration validation should be performed server-side even when a preparation sheet also flags errors.

Use stable identifiers rather than names to connect related records. If the spreadsheet has no reliable identifier, define how one will be created and how duplicates will be reviewed. Preserve legacy identifiers when they will help support or audit later. Document time zones, decimal separators, currency, units, true/false conventions and how blank differs from zero, unknown or not applicable.

• Source sheet, column, observed type, sample pattern and business meaning.
• Target object, field, type, required status, allowed values and relationship.
• Transformation rule, including trimming, normalization, units, dates and blank handling.
• Identifier and duplicate rule: how records match and which conflicts require review.
• Validation and rejection behavior: accept, quarantine or stop, with a clear reason.
• Owner, approval evidence and the migration script or mapping version that applies.

Resolve data-quality exceptions deliberately

Create an exception log rather than fixing the only copy of a spreadsheet by hand. For each issue, record the source row identifier, field, observed value, rule violated, proposed treatment, approver and resolution. Useful categories include duplicate identifier, missing required value, invalid reference, unrecognized status, impossible date and sensitive data outside the approved target.

Choose whether each category blocks the import, enters a quarantine file or receives a documented transformation. Defaults can conceal missing knowledge, so use them only when the business meaning is true for every affected record. Do not copy obsolete personal data simply because it is available. Limit the migration workspace, logs and evidence to the information needed, and remove temporary copies according to the agreed retention plan.

Run a representative trial import

Use a non-production environment with the same schema and validation behavior as the intended release. Select records that cover normal cases, empty optional fields, every important status, the largest relationships and known exceptions. Test whether the process can be repeated from a clean state. Import scripts should identify their mapping version and produce structured counts without exposing full records in ordinary logs.

Verify the data through the app’s real journeys, not only database queries. Open migrated records under the correct roles, update a safe field, follow relationships, search by identifiers and export where required. A trial may reveal that the target model or validation is wrong; document that finding and revise the agreed mapping before another clean run. Acceptance testing can then verify workflows using reconciled records.

Hypothetical example: migrating service requests

Consider a hypothetical operations team moving service requests from three worksheets. Request numbers are unique only within each year, customer names vary in spelling and “done” includes both completed and cancelled work. The team creates a composite legacy key from year and request number, maps customers through an approved customer table and splits the old status using closure notes reviewed by an owner.

A trial import quarantines twelve rows with customer names that match more than one account instead of guessing. Reconciliation compares source and target counts by year and status, then checks a sample of linked customer histories. The import is rejected until completed and cancelled totals agree with the approved exception decisions. This is a hypothetical planning example, not a Kinetivy client result.

Reconcile the import and make cutover reversible

Use a reconciliation sheet with source count, excluded count, transformed count, rejected count and target count for each record type. Add uniqueness checks, orphan relationships, totals that matter to operations and a sample comparison of complete records. Information integrity controls in NIST SP 800-53 provide a useful security context: detect unauthorized changes and protect the integrity of information and systems. Your sign-off should name who accepts the reconciled result.

For cutover, define the final edit freeze, export time, script version, validation sequence, decision deadline and communications owner. Choose rollback criteria before starting: for example, unmatched core records, broken relationships or a failed essential journey. Preserve the old source as read-only for the agreed period, prevent teams from entering new work into both systems, and record how late changes will be captured. Delete temporary migration files only after acceptance and retention needs are satisfied.

• Approve the field map, transformation rules, exclusions and exception owners.
• Back up the source and target, and verify a clean, repeatable import path.
• Reconcile record counts, stable identifiers, relationships, statuses and meaningful totals.
• Test migrated records through essential role-based app journeys and exports.
• Set the freeze, final delta process, go/no-go owner and rollback threshold.
• Record sign-off, archive required evidence and remove temporary sensitive copies on schedule.