Inspect the export before opening it
A spreadsheet can change a value before you notice: order 000101 becomes 101, a phone loses its first zero, or a date is interpreted using the wrong regional convention. Start with the original file and a copy. The sample here contains 14 data records and 11 columns. A note contains a line break inside quotes, so counting physical lines in a text editor gives a misleading record count.
Work with the example
Original order CSV ↓Import edge cases ↓Worked Excel workbook ↓All records are fictional. Import CSV identifier columns as Text. Quoted CSV fields do not define spreadsheet column types.
Open the CSV import diagnostic, choose Load sample, and run the inspection. It identifies values that need attention. It does not decide whether an order is legitimate or repair a damaged identifier.
Choose types before loading the data
For Excel's Power Query import, start from Data → From Text/CSV and choose Transform Data. Labels and availability vary by Excel version and platform. In the editor, check the delimiter and review any automatically created Changed Type step before it converts your identifiers.
| Columns | Intended type | Reason |
|---|---|---|
| order_id, line_id, customer_code, phone, sku | Text | These identify records. Arithmetic is not their purpose. |
| order_date | Date, using the source locale | This example uses day/month/year. |
| quantity | Whole number | Used in line-value calculations. |
| unit_price_rm, discount_rm | Decimal after removing RM | The original strings contain a currency prefix. |
| status, note | Text | Keep the note as one field even when it contains a comma or newline. |
If a type-conversion step has already removed zeros, delete or revise that step and re-read the original source. Formatting a damaged value cannot recover information that was lost. Microsoft documents both automatic conversions and text-typed imports.
Check three records against the source
Verify the first order ID is exactly 000101 and its phone is 0123456789. Check that 01/11/2026 means November 1, not January 11. Check the note on order 000105: “Line one” and “Line two” belong to one cell.
Use the edge-case file for an additional check. Its long reference 123456789012345678 must remain text. Its =1+1 note is deliberately formula-like text. Inspect it through a text import; the browser tool displays it as plain text. A CSV file cannot store an Excel Text type, even if every field is quoted.
Keep import validation separate from reporting
The import is complete when the source values and record boundaries survive. There are still 14 source records. A later cleanup step removes the exact repeated record, and reporting excludes records outside the week or with ineligible statuses. A total produced immediately after import mixes all of those cases.
Continue with duplicate records versus order lines, then build the verified weekly report. Keep the original CSV unchanged so each transformation can be checked against it.
Sources & further reading
The sample data and worked calculations are original instructional examples. Spreadsheet menus and supported features depend on your installed version. See the verification method for the checks performed.
Continue the workflow