# Clean the CSV. Keep every change explainable. Reviewed: 2026-09-12 Canonical: https://clankercloud.ai/templates/data-cleaning-checklist Use this starter to clean a CSV under explicit rules while preserving the original. Apply only agreed transformations, keep ambiguous values visible, and reconcile row counts and totals. The fictional sample includes deliberate errors with a hand-checked expected result. ## What you are working toward A cleaned CSV copy and an audit note explaining every change and unresolved value. ## Start with - An original CSV retained unchanged - Column meanings, date rules, and allowed transformations - Expected row counts and totals that a reviewer can independently check ## Define the data rules before changing values The sample treats record_id as text, amount as a decimal value in the stated currency, and event_date as an ISO date only when it is valid and unambiguous. The leading zeros in 001 and 002 are part of the identifier. Losing them during import is a change to the data even if the table still looks readable. Keep the original file and write the result to a new name. Ask for the transformations and unresolved issues in a separate note. The goal is a reproducible cleaning pass, not a claim that the business data is complete or correct simply because it can be parsed. ## Distinguish exact repeats from conflicting records Two rows for 002 are byte-for-byte copies in the sample, so the stated rule allows one copy to be removed. That changes eight source rows into seven output rows. Trimming whitespace in customer_name changes the retained Birch Works value but should not create a new customer or alter its identifier. Do not generalize this rule to duplicate IDs with different amounts, dates, or names. Those could represent corrections or distinct events, depending on the data model. If such a conflict appears in real input, preserve both source records in the issue register and ask for a resolution rule before deciding which is authoritative. ## Keep blanks separate from zero Record 003 has no email and record 004 has no amount. A missing amount is not zero; replacing it with zero would make arithmetic look complete while hiding the missing information. Keep the blank value and explain how it affects any total. Validate what you can actually establish. An address ending in .example is fictional sample data, and a syntactically plausible address is not proof of deliverability or ownership. The task does not require contacting anyone, enriching identities, or guessing the missing address from a company name. ## Reject impossible dates and preserve ambiguity The date 09/06/2026 could mean September 6 or June 9. Without a declared locale, neither interpretation is justified. The string 2026-09-31 is invalid because September has thirty days. Mark both values as issues; do not silently convert either to a guessed date. One workable output convention is to preserve event_date exactly and add normalized_event_date plus date_status. Valid ISO dates can be copied to the normalized field; ambiguous or invalid values leave it blank with the appropriate status. This keeps the evidence available while making downstream filtering safer. ## Check row counts and money by currency After the exact duplicate is removed, the sample has seven rows. The known numeric USD amounts sum to 330.00: 120, 80, 45, 60, and 25. One additional USD row has a blank amount. EUR totals 100.00 in its own currency. These are known-amount totals across retained rows, not revenue for a validated reporting period. Do not combine USD and EUR without a specified conversion rate and date. Date problems also prevent confidently allocating all amounts to a calendar period. State the inclusion rule for totals and keep the missing amount visible next to the number so the reviewer does not mistake a partial total for a complete one. ## Open the saved files and replay the explanation Review the cleaned CSV itself, not only the Agent’s summary. Confirm the header, seven retained rows, text identifiers, unchanged original values, and added date columns. Open the issue note and verify that every deliberate error in the sample is represented. Check that the saved files exist before relying on a completion message. If a processing step fails or the result cannot be opened, inspect the state before rerunning a workflow that might overwrite outputs. For the next real dataset, revise the cleaning contract and expected checks rather than assuming this sample’s rules fit it. Different uses may require preserving duplicates or excluding unresolved rows from downstream analysis. ## Sample files Fictional inputs. Download and add to your Project. - [data-cleaning-sample.csv](https://clankercloud.ai/templates/data-cleaning-checklist-files/data-cleaning-sample.csv): Eight fictional rows with an exact duplicate, whitespace, blanks, two currencies, and two date issues. ## Try this prompt Copy into a Workspace Session. Opening Workspace does not send it automatically. ```text Clean the fictional CSV below into cleaned-records.csv and save cleaning-review.md in this Project. Preserve the original input. Treat record_id as text and keep leading zeros. Remove only exact duplicate rows, trim surrounding whitespace in customer_name, and never fill missing email or amount values. Keep original event_date. Add normalized_event_date and date_status: valid ISO dates copy to normalized_event_date with status Valid; ambiguous dates keep normalized blank with status Ambiguous; impossible dates keep normalized blank with status Invalid. Do not guess the locale. Report source rows, output rows, removed duplicates, changed fields, and unresolved issues. Total known numeric amounts across retained rows separately for USD and EUR; report blank amounts and clarify that these are not period-validated totals. Do not use exchange rates, contact customers, or overwrite a source file. record_id,customer_name,email,amount,currency,event_date 001,Aster Studio,team@aster.example,120.00,USD,2026-09-01 002," Birch Works ",hello@birch.example,80.00,USD,2026-09-02 002," Birch Works ",hello@birch.example,80.00,USD,2026-09-02 003,Cedar Lab,,45.00,USD,2026-09-03 004,Dune Group,ops@dune.example,,USD,2026-09-04 005,Elm Studio,hi@elm.example,100.00,EUR,2026-09-05 006,Fern Works,team@fern.example,60.00,USD,09/06/2026 007,Grove Lab,hello@grove.example,25.00,USD,2026-09-31 ``` ## Illustrative result Fictional sample; your result depends on your inputs and needs review. ```text Fictional expected reconciliation Source rows: 8. Retained rows: 7. Exact duplicates removed: 1 (record 002). Whitespace: retained 002 customer_name becomes Birch Works. Missing email: 003. Missing amount: 004; remains blank. Date issues: 006 Ambiguous; 007 Invalid. Their normalized dates remain blank and original strings remain present. Known amounts: USD 330.00, plus one unresolved USD amount; EUR 100.00. No combined currency total. These totals include retained rows with date issues and are not validated totals for a reporting period. ``` ## Before using the result - Eight input rows reconcile to seven retained rows and one exact duplicate removal. - Leading zeros survive and the original file is unchanged. - The blank amount remains blank; no zero or email was invented. - Both date issues are visible and original date strings are preserved. - Known amounts are USD 330.00 and EUR 100.00, with the missing USD amount disclosed. ## Common questions ### What can I bring into a Project? Upload documents, spreadsheets, PDFs, images, and presentations, or attach files and paste images in a Session. Add Project instructions to explain the audience, preferred format, and facts the Agent should use. [Read more in the FAQ](https://clankercloud.ai/faq#source-files) ### Where do I find and review the finished files? Open your Project’s Files tab or General → Files. In a Session, Preview opens a side panel where you can select a file. Use page, sheet, or slide controls where available, check the contents, and download the result. [Read more in the FAQ](https://clankercloud.ai/faq#review-files) ## Sources - [Workspace guide: inspect and download files](https://workspace.clankercloud.ai/#page=faq&tab=articles&article=files-and-knowledge) - [FAQ: spreadsheet and Project inputs](https://clankercloud.ai/faq#source-files) ## Related - [Clean a CSV without losing what it means.](https://clankercloud.ai/learn/clean-csv-data) - [A spreadsheet in. A useful App out.](https://clankercloud.ai/learn/build-an-app-from-a-spreadsheet) - [Customer feedback with the evidence intact.](https://clankercloud.ai/templates/customer-feedback-analysis) [Open Workspace](https://workspace.clankercloud.ai) [Current pricing and usage](https://clankercloud.ai/pricing)