DoItAl

Practical guide

Clean a CSV for Import: A Worked Customer-List Example

Prepare an invented customer list from source CSV to an import layout: preserve leading zeros and quoted commas, remove one duplicate, select paid customers, and check every output.

Prepared by DoItAl · Worked examples checked . How examples are checked and corrected

The job: keep two customers, then build the receiving layout

Imagine that a receiving system needs Customer ID, Email and Notes, in that order. It should receive only customers whose status is paid and amount is greater than 100. This example is an invented five-record customer list; all email addresses use example.com. It is safe to experiment with the sample instead of using your customer data.

The source includes one exact duplicate, one identifier with surrounding spaces and a company name containing a comma. The finished import should contain two records, keep 00127 as five characters, and contain an intentionally blank Notes field. The checklist below gives you expected counts at each stage so a plausible-looking preview is not your only check.

customer_id,email,status,amount,company
00127,ava@example.com,paid,150,"North, Studio"
00128,ben@example.com,pending,200,East Shop
00127,ava@example.com,paid,150,"North, Studio"
 00129 ,cy@example.com,paid,80,West Shop
00130,dee@example.com,paid,125,South Shop

Step 1: inspect the structure before changing values

Open the sample in CSV Cleaner. Use a comma delimiter and confirm that the first row contains headers. Every record has five fields: North, Studio is one company value because the comma is inside double quotes. Do not repair this by splitting the line at every comma. RFC 4180 documents this quoting convention, including doubled quotes inside a quoted field.

Before a real import, obtain the destination's header spelling, required fields, encoding, delimiter and row limit. This guide's three-column layout is an example, not a promise about a particular CRM. Keep the original export. A valid CSV can still fail an import if its column names or accepted values do not match the receiving system.

Step 2: trim intentionally and remove the exact duplicate

Enable trimming and exact duplicate-row removal. Keep internal-space collapsing, missing-value normalization and irregular-row repair off for this example. Trimming changes the spaced identifier to 00129; removing the exact duplicate leaves four data records. The source has no empty records or columns, so their removal settings do not change the expected result.

CSV spaces are data. Trimming is a chosen transformation, not an inherent CSV parsing rule. Do not apply it blindly to passwords, fixed-width identifiers or values whose leading spaces matter. Similarly, do not deduplicate only by customer ID unless you have decided what to do with legitimate repeat orders. This example removes one identical row, not every repeated customer.

Expected cleanup checkpoint
CheckBeforeAfter
Data records54
Fields per record55
Exact duplicate records1 extra copy0 extra copies
Spaced identifier 00129 00129

Step 3: select records with two AND conditions

Download the cleaned CSV and open it in CSV Filter & Extract Rows. Set status equals paid and amount greater than 100. Choose AND / all conditions and keep matching rows. IDs 00127 and 00130 remain. The pending 00128 fails the status condition; the paid 00129 fails the amount condition. Kept and excluded counts should both be two.

Changing AND to OR would keep all four records here: 00128 has amount 200, and 00129 has status paid. Changing greater than to greater than or equal to matters for a value exactly at 100. Numeric conditions do not interpret formatted amounts such as USD 150 as plain numbers. Review excluded records instead of assuming that every exclusion is wanted.

Expected record selection
IDStatusAmountAND result
00127paid150Keep
00128pending200Exclude
00129paid80Exclude
00130paid125Keep

Step 4: map the import columns without guessing

Open the kept CSV in CSV Column Mapper. Define Customer ID from customer_id, Email from email, and Notes as an explicit blank. Omit status, amount and company because this destination does not accept them. The mapper should keep two records and produce three columns. It changes the layout, not the cell types or record order.

Use exact source header identities. An empty or duplicate source header should be fixed before mapping. If a required field is missing, stop and investigate; choosing a similarly positioned column can silently send the wrong data to the destination. An explicit blank is appropriate only when the destination permits it.

Customer ID,Email,Notes
00127,ava@example.com,
00130,dee@example.com,

Step 5: verify a small import and keep the audit trail

Compare your output with the expected file as CSV text: two records, three headers, five-character IDs and blank Notes. Then validate a small import in the receiving system before replacing or merging production records. If you open the CSV in a spreadsheet, import Customer ID as text; automatic number conversion can turn 00127 into 127 even when the exported CSV preserved it.

Keep the source, cleaned, kept, excluded and mapped files until the destination check passes. The tool's 50-row preview is a bounded view, not a truncated download. Large files still have browser-memory limits. Merge & Join adds another decision about matching keys; Split & Partition can meet a destination row limit. Neither is needed for this small example. All sample records are synthetic, and the expected files are checked against the same processing engines used by the tools.

Sources and further reading

The workflow and sample files are original DoItAl examples. References explain the underlying format or calculation; they do not endorse this site.

Material update: