Computer Specialist in Spreadsheet & Database Level 1: The CSV That Lost Its Leading Zeros

The CSV That Lost Its Leading Zeros

Each month a benefits administrator receives a 12,480-row CSV from a third-party payroll bureau and loads it into a workbook used for reporting. The columns are MemberID, Surname, DateOfBirth, PostalCode, Amount, and a trailing empty column. MemberIDs are nine characters with leading zeros (for example 000418227); dates are meant to be D/M/YYYY; postal codes are four digits with leading zeros; Amount uses a comma thousands separator and parentheses for negatives, for example (1,240.50).

The administrator double-clicks the file, saves it as .xlsx and starts cleaning. Last month's load showed 3,102 MemberIDs missing their leading zeros, 214 dates with day and month swapped (all with a day value of 12 or less), 88 postal codes converted to numbers, and a workbook total of $1,283,693.10 against the bureau's control total of $1,284,933.10. A pivot by month matched for eleven months and was short in one. Cleaning took two days, and the corrections were typed directly into the loaded table.

1. Explain what double-clicking a .csv actually does to these columns, and describe the import procedure that would have preserved them.

Read the worked answer

Why double-clicking fails. Opening a .csv hands the file to Excel's default text parser, which (a) applies the machine's regional settings for separator and date order, (b) infers each column's type from a sample of the first rows, and (c) offers no per-column control. MemberID 000418227 looks numeric, so the leading zeros go; 03/12/2024 is read as M/D/YYYY on a US-locale machine; 0123 becomes 123; (1,240.50) becomes text or a mangled number depending on the locale. Saving to .xlsx then bakes the damage in - the original text is gone and cannot be recovered from the file.

Reliable procedure (Power Query):

Data ▸ Get Data ▸ From File ▸ From Text/CSV. Do not open the file.
Set File Origin (65001: Unicode UTF-8) and the correct delimiter; check the preview before loading.
Choose Transform Data, then set each column type explicitly:

MemberID → Text
PostalCode → Text
DateOfBirth → Date, using Using Locale → English (United Kingdom) to force D/M/YYYY
Amount → Decimal Number, using Using Locale to handle thousands separators and parentheses

Remove the trailing empty column and rename headers to remove spaces.
Close & Load To… a table or the Data Model on its own sheet, keeping the query separate from any reporting sheet.
Run the validation checks, then publish. Never type corrections into the loaded table - a refresh overwrites them; fix the query step instead so the repair is repeatable next month.

2. Design the post-load validation that would have caught all four column faults plus duplicates, and state how failures should be handled.

Read the worked answer

Validation must run on every load, before the data reaches a report. Cell-level data validation rules only constrain manual typing; they do nothing to an imported range.

Control totals first, from the bureau: row count (12,480), sum of Amount ($1,284,933.10), and count of distinct MemberID. If any of the three disagrees, stop the load - do not proceed and reconcile later.

Per-column checks, as flag columns or a check query:

Check	Test	Expected
MemberID present and well-formed	`=AND(LEN(A2)=9,ISNUMBER(--A2))` on the text value	TRUE for all
MemberID unique per period	`=COUNTIFS($A:$A,A2,$G:$G,G2)>1`	FALSE
Date valid and plausible	`=AND(ISNUMBER(C2),C2>=DATE(1900,1,1),C2<=TODAY())`	TRUE
Postal code pattern	`=AND(LEN(D2)>=4,LEN(D2)<=5,ISNUMBER(--D2))`	TRUE
Amount numeric	`=ISNUMBER(E2)` - catches `(1,240.50)` left as text	TRUE
Referential integrity	`COUNTIF(MemberMaster[MemberID],A2)=0`	no orphans

Handling: publish an exception count on the load sheet and fail the load if the count exceeds an agreed threshold - any control-total mismatch, or more than 0.1% malformed IDs. Route exceptions back to the bureau rather than patching them locally, because the next file will carry the same fault. Keep last month's accepted file so a sudden jump in exceptions is visible, and keep the check formulas in the query file rather than in a scratch column, so they cannot be deleted during a busy month.

3. Reconcile the $1,240 gap between the bureau's control total and your loaded total. Give an ordered procedure and explain why a plug row is unacceptable.

Read the worked answer

Do not add a plug row. An adjustment of −$1,240 makes the total agree while leaving the bad record in the data, and next month's reconciliation starts from a corrupted baseline. It also converts a visible error into an invisible one.

Ordered procedure:

Row count. If the loaded count is not 12,480, rows were dropped (a filter, a delimiter clash, an embedded line break). That is a bigger finding than the money and should be resolved first.
Narrow by dimension. Pivot both the bureau's monthly subtotals and the loaded data by month; eleven months matched, so isolate the month that did not and the records inside it.
Test for text-vs-number. =SUMPRODUCT(--ISTEXT(AmountRange)) and compare SUM with =SUMPRODUCT(--AmountRange). Parenthesised negatives and amounts separated by a non-breaking space are the usual suspects and are silently excluded from SUM.
Test for duplicates and drops. COUNTIFS on MemberID + period > 1; compare distinct MemberID counts against the bureau's.
Match the residual to a document. $1,240 is small enough to be one credit note, one refund, or one row lost to a merged cell. Find the transaction, not a formula.
Fix the transform, not the output. Correct the locale and column-type steps, reload from the original file, re-run the control totals and the validation checks.

Record the outcome, cause and corrected total in a reconciliation log signed off by the bureau. The deliverable is a repeatable import plus a documented explanation - not a workbook that happens to add up.