Build Spreadsheets That Preserve Real-World Data

The total balances. The evidence does not.

Imagine receiving a returns register whose total agrees with the sender’s email. Four rows, five net units, 33.00 USD. It looks ready for a report. Yet an identifier has lost its first two characters and an April transaction has moved into March. Neither error necessarily changes the money.

This is a hypothetical import investigation, using invented records small enough to inspect completely. The task is to preserve identifiers, interpret dates under a declared convention and make quantities genuinely numeric. A matching total will be one piece of evidence, rather than permission to stop looking.

The procedure targets Excel desktop for Windows with Power Query, such as Microsoft 365 or Excel 2024. Ribbon arrangements vary. Excel for Mac and the web have different import capabilities; the clicks below are not a verified procedure for those editions.

Exhibit A: the file before Excel touches it

In a plain-text editor, save these lines as returns-evidence.csv. Preserve that file and save the workbook separately. Avoid creating the source by typing into Excel and exporting: that could introduce the conversions this investigation is meant to catch.

OrderID,Booked,Units,UnitUSD,Note
00420,03/04/2026,3,12.50,counter sale
00421,13/04/2026,2,4.00,mail order
00422,14/04/2026,-1,12.50,returned item
00423,14/04/2026,1,0.00,warranty replacement

The source contract is explicit: OrderID is a five-character text key; Booked uses day/month/year; Units is a signed whole number; UnitUSD is a decimal-point price in US dollars; Note is text. The negative quantity represents a return at a positive unit price. The zero price represents an agreed warranty replacement. There are no taxes, delivery charges or discounts.

Thus 03/04/2026 means 3 April. The 13 April record provides an additional clue because thirteen cannot be a month. It does not establish the convention by itself: a real mixed-source file could contain multiple conventions and would need clarification from its owner.

Opening a CSV directly invites Excel to interpret its columns using defaults. Microsoft documents both automatic loss of leading zeros and the need to preserve identifier columns as text. A custom display format can make 420 look like 00420, but it cannot establish which characters the source actually contained. Keep the original file as the comparison.

Reconstruct the import in six moves

Start in an empty workbook. The aim is to leave a visible sequence of interpretations, including an untouched date string beside its parsed date.

1. Enter through the connector. Select Data > Get Data > From File > From Text/CSV; some ribbons expose Data > From Text/CSV directly. Select the preserved file. In the preview, choose comma as the delimiter if necessary. Expect five columns with the named headers. A single column means the delimiter needs attention; shifted fields mean the source needs inspection before loading.
2. Prevent the first guess. If the preview offers Data Type Detection, select the option to skip detection, leaving columns as Text, then choose Transform Data. In Power Query Editor, inspect Query Settings > Applied Steps. If an automatic Changed Type step exists, remove that step in this new practice query before assigning types. Retain the header-promotion step. Confirm that OrderID again contains 00420, not 420. Converting an already shortened number to Text will preserve the shortened value.
3. Keep the source date. Set OrderID, Booked and Note to Text using each header’s type selector. Right-click Booked and choose Duplicate Column. Rename the duplicate BookedParsed and position it after Note, leaving the five source columns in their original order. The original Booked column remains the literal date evidence.
4. Apply the declared date convention. Right-click BookedParsed and select Change Type > Using Locale. Choose Date and English (United Kingdom). Expect four valid April dates. An Error value or a March interpretation requires returning to the preceding text values and inspecting earlier conversion steps. Changing the worksheet’s date display later cannot correct a mistaken interpretation.
5. Make arithmetic columns numeric. Through Change Type > Using Locale, set Units to Whole Number and UnitUSD to Decimal Number, using English (United Kingdom) for these decimal-point source values. Confirm the return remains −1 and the warranty price remains zero. Do not remove error rows or replace errors with zero to obtain a tidy preview.
6. Load a traceable result. Use Home > Close & Load to load to a worksheet. Name the sheet Imported. For the checks below, headers should occupy A1:F1, with four records in rows 2–5; BookedParsed belongs in F. If the destination differs, use Data > Queries & Connections, right-click the query and choose Load To to select a clear destination, or adjust the audit references to match.

Microsoft’s Text/CSV connector documentation describes disabling type inference. Its locale documentation confirms that a conversion can specify its own region. This avoids changing the computer’s regional settings merely to interpret one supplier’s file.

Cross-examine the cells before accepting 33 dollars

Create a separate sheet named Audit for checks. The formulas below use English function names. Inspect all four imported records and confirm there are no additional records beneath row 5; a formula restricted to rows 2–5 cannot discover an unexpected fifth record.

ISTEXT tests the stored value’s type. Appearance and alignment are weaker evidence. Likewise, COUNT counts numeric cells in a referenced range, so a text price can be exposed even when it looks normal. These checks supplement inspection: they do not prove that a numeric value is the correct one.

On Imported, label G1 LineUSD. Enter =C2*D2 in G2 and fill through G5. If Excel fills the table column automatically, inspect all four results. In source order they must be 37.50, 8.00, −12.50 and 0.00 USD. On Audit, =SUM(Imported!G2:G5) must return 33. Format monetary results to two decimal places for reading.

The independent arithmetic is 3 × 12.50 + 2 × 4.00 − 1 × 12.50 + 1 × 0.00 = 33.00. Net units are 3 + 2 − 1 + 1 = 5. Check the individual lines: offsetting errors could leave the grand total unchanged. A swapped identifier or wrong date could also pass every money check above.

A minus sign is not a security verdict

Our −1 is a legitimate quantity under the declared schema. Untrusted free text deserves different treatment: spreadsheet programs can interpret formula-like cell contents as executable expressions. OWASP’s CSV injection guidance identifies leading characters such as equals, plus, minus and at-sign, and warns that quoting or escaping may fail after saving and reopening.

For incoming notes, preserve explicit text handling and inspect the loaded cells and formula bar before using the file. Do not enable external content to satisfy an unexpected prompt. For exports containing user-supplied text, require the exporter to provide handling appropriate to the receiving application, ideally typed text cells in a workbook, and verify the delivered artifact. CSV quotation marks separate fields; they do not declare safe cell types. This import procedure is not a universal sanitizer, and stripping every minus sign would corrupt valid returns.

The next opening is another import

Save the working file as .xlsx to retain its query, audit sheet and calculations. Keep the preserved CSV alongside it. If a recipient needs CSV, export a separate copy of the intended sheet: the text file will not carry the workbook’s audit structure or type declarations.

Inspect that copy in a plain-text editor, then have the recipient import it under the agreed column rules. Verify the keys, the April date and the signed return again. If the receiving workflow turns 00420 into 420, the handoff is still unresolved, however reassuring its 33-dollar total looks.