I85PREP
⌘ K
ICDL Using Databases study materials
ICDL Using Databases study resources

The Import That Broke the Postcodes

ICDL Using Databases · Put your knowledge to work in context.

All case studies

The Import That Broke the Postcodes

A charity imports a 2,000-row spreadsheet of donors into a new Donors table. The Postcode column in the source mixes formats: SW1A 1AA, sw1a1aa, and a few entries with a leading apostrophe ('EC1A 1BB). After import, the postcode field shows #Error on about 60 rows, and the apostrophe entries display with the apostrophe still visible. The DonationAmount column, which was numeric in the spreadsheet, has imported as text and now sorts 1000 before 200, because 1 sorts before 2. The import 'succeeded' with no error message.

1. Diagnose the `#Error` postcodes and the visible apostrophes, and state what should have been checked before import.

Read the worked answer

Two separate import problems:

  1. #Error rows - the mixed formats and stray characters mean some values could not be coerced into the field's expected type, or the apostrophe prefix was interpreted as a text qualifier and mangled the value. The import forced values into a field whose type did not tolerate them.
  2. Visible apostrophes - the leading ' is a spreadsheet convention meaning 'treat as text'. On import it was carried through as a literal character rather than stripped.

Before importing, the correct step is to inspect and clean the source: standardise postcode casing and spacing, remove leading apostrophes, and confirm a consistent format. Critically, the operator should have previewed the import mapping and set the correct data types per column, then spot-checked a sample before committing all 2,000 rows.

2. Why does `DonationAmount` sorting `1000` before `200`, and what is the correct remedy?

Read the worked answer

The column was imported as text, so sorting is character-by-character: 1 comes before 2, so 1000 precedes 200. This is the same text-vs-number trap as dates stored as text.

The remedy is to correct the data type, not the sort order:

  1. Add a numeric (Currency or Number) field.
  2. Convert the text values into it, watching for stray symbols, spaces, or thousands separators that would block conversion.
  3. Verify totals against the original spreadsheet.
  4. Remove or hide the text field so it cannot be used in calculations.

Once the field is numeric, sorting and summing behave correctly.

3. The import reported success but produced bad data. What does this reveal about 'successful' imports, and what verification step should follow every import?

Read the worked answer

A successful import only means the rows were written, not that they are correct. Type mismatches, stray characters, and formatting artefacts can all pass silently.

A sensible post-import check:

  • Row count: imported rows match the source (2,000).
  • Spot-check a sample of rows against the source, including known-tricky values (apostrophe postcodes, mixed case).
  • Aggregate check: sum DonationAmount and compare with the spreadsheet total; a mismatch flags text-as-number problems.
  • Sort/scan key columns for anomalies like #Error or visible punctuation.

Treat the import as unverified until these checks pass; fix the source and re-import rather than patching 60 rows by hand.