You have received an order export with inconsistent labels, a repeated order, and four records that cannot be settled from the file alone. Your job is to prepare a reviewable list, not to make every cell look complete. This exercise includes the raw data, the answer, and the decisions needed to explain the difference.
Practice context: Paper Lantern is an invented stationery shop. The records are fictional. They demonstrate a method; they are not customer data or evidence of paid work.
The assignment and the rules
Keep the raw file unchanged. Produce a cleaned table with the source-row reference, one record per unique order, and a separate data-quality label. The allowed products are Everyday A5, Pocket Dot and Weekly Planner. The allowed payment statuses are Paid, Pending and Cancelled. Prices use PKR.
| Field | Allowed action | Stop and ask when |
|---|---|---|
| Product/status | Trim surrounding spaces; map known case variants | A label is not in the supplied list |
| Date | Preserve valid YYYY-MM-DD dates | Day and month order is ambiguous, or the date is impossible |
| Quantity | Convert an unambiguous non-negative whole number | Missing, negative or fractional quantity |
| Unit price | Convert an unambiguous non-negative number | Blank, text or a negative amount |
| Order ID | Remove a repeated row only when all business fields match | The same ID contains conflicting information |
A zero quantity or price is valid under this exercise's rules. That is different from a blank. In a real project, the client may define different rules; obtain them before editing.
Import without changing the evidence
Import the CSV using your spreadsheet application's text/CSV import workflow. Set Raw date to text so 09/10/2026 stays exactly as supplied. Set Order ID to text too. Check the seven column headings before continuing. If the entire line appears in one cell, select a comma separator and double quotes as the text qualifier.
Create a working sheet, then normalize product names and statuses with the allowed-value list. Do not sort one column independently: select the whole table so an order ID remains attached to its quantity and price. Keep Source row throughout the process, including after sorting.
For a simple label check, TRIM removes ordinary surrounding spaces, but it is not a complete cleaning policy. It does not decide whether an unfamiliar product is a spelling error or a genuinely different product. Record that decision separately.
The four records you should leave unresolved
| Order | Evidence | Cleaned answer | Question to send |
|---|---|---|---|
| PL-1007 | Date is 09/10/2026 | Date blank; other usable fields retained | Is this 9 October or 10 September? |
| PL-1011 | Quantity blank | Quantity and amount blank | What was the ordered quantity? |
| PL-1014 | Quantity is -2 | Quantity and amount blank | Is this a return, correction or entry error? |
| PL-1018 | Price is “ask” | Price and amount blank | What unit price applies to this order? |
Source row 20 repeats PL-1004 with the same business values as row 4. Removing it leaves 19 unique records. The source-row number itself is a trace reference, so it is not part of the duplicate comparison.
Try the cleaning rules in your browser
Load the sample, inspect the result, then change one fictional value and run it again. The checker implements only the rules in this article. It cannot decide what an ambiguous source was meant to say. The checker does not save or upload entries; reloading clears unsaved work. Use fictional data on this page, which also loads third-party advertising. Download anything you want to keep.
Check and clean a fictional order CSV
Use the sample schema and fictional data. Up to 200 rows / 100 KB. Download the results before closing the tab.
No file loaded. Ambiguous values will remain unresolved.
Reconcile records before adding amounts
Start with counts and IDs: 20 source rows, one duplicate removed, 19 output rows, and four rows needing clarification. Then compare each corrected field with the answer file. A matching grand total is weaker evidence because two mistakes can cancel each other out.
The answer contains PKR 13,800 of calculable line amounts. This includes pending and cancelled records; it is not collected revenue. Paid-labelled rows with calculable amounts sum to PKR 9,450, including PL-1007, whose date is unresolved. Paid-labelled rows with every field ready sum to PKR 8,550. Even that figure only describes this fictional file; a Paid label does not reconcile a real bank account.
For the workbook's cleaned table, =IF(AND(ISNUMBER(E2),ISNUMBER(F2)),E2*F2,"") illustrates the line-amount check. It keeps missing inputs blank while preserving a valid zero. The supplied answer table contains documented values, not an automatic refresh pipeline.
Troubleshooting and a useful handoff
If your answer has 18 rows, inspect whether you accidentally deleted an unresolved record. If it has 20, inspect PL-1004. If PL-1007 acquired a date, re-import the original as text. If a total treats blanks as zero, keep an unresolved-count control beside it.
Deliver the raw file, cleaned file, issue log, and a note saying exactly which four orders need confirmation. A suitable portfolio description is: “Fictional 20-row cleaning exercise; retained source references, removed one duplicate and documented unresolved values.” Include your own changes and reasoning when adapting the exercise.
Inspect the actual files and cleaning run
These views were captured from the supplied workbook and this page’s working CSV checker on 24 October 2026. The data is fictional; the files and tool output are real. They document this exercise, not a client engagement.
The cleaned-workbook excerpt above is the fourth view. Reproduce the sequence with Raw CSV → Check and clean → issue log → worked answer. Open each full-size image if a column is difficult to read on a phone.
Practice work and illustrative amounts are labelled in the article. See how we prepare and correct guides and how the resource checks work. Report a specific error or broken step.