Clean a CSV by inspecting it first, choosing column-specific fixes and reviewing the impact before exporting a separate file. A consistent-looking dataset is useful, but it still needs to meet the destination system's requirements.
Start with the source and the destination
Keep the original file unchanged and work on a separate dataset. Record the starting row count and identify the fields the destination expects. A customer ID, postal code or other identifier may look numeric without representing a quantity; inspect it before applying any formatting rule.
For Excel input, identify the sheet containing the records you actually intend to prepare. A CSV output is a table, not a replacement for an entire workbook with its formatting and multiple sheets.
- Check headers and the number of columns.
- Inspect representative rows, including missing and unusually long values.
- Identify fields that must retain their exact values.
Trim surrounding whitespace, not meaningful content
Spaces before or after a value can interfere with matching. For example, a Company value of ' Acme Inc. ' can be trimmed to 'Acme Inc.' without changing the internal space. Do not assume that removing all spaces is safe: spaces inside names, addresses and descriptions may be meaningful.
Preview a few affected rows and the total count before applying the operation. Check the chosen columns rather than applying a text transformation simply because it is available.
Distinguish blank rows from incomplete records
A completely blank row contains no useful field values and can be removed from a tabular import file. A row missing only an email address may still contain an important customer record. Do not treat these two cases as the same cleanup operation.
Placeholder values need their own decision. A token such as N/A may mean missing data in one column and a valid code in another. Configure which tokens to clear and where they apply, then review the proposed replacements.
Standardize values with business meaning in mind
Choose casing and value mappings for specific columns. Lowercasing email values is different from title-casing every description. Similarly, replacing 'Trade show' with 'Event' changes the vocabulary of a field and should reflect an agreed convention.
Make a short list of approved mappings before applying them. Avoid guessing that two labels have the same meaning. Cleanup cannot infer missing business information or prove that a populated value is accurate.
Review the changes and export a separate CSV
Check the before-and-after values, removed rows and applied operations. Reconcile row counts: if 500 starting rows include 8 completely blank rows and no other removals, the result should contain 492 rows. Value edits do not by themselves reduce the row count.
Save the prepared file separately, then verify its headers, identifiers and required fields against the target importer. Duplicate review and comparison with existing records are separate decisions; a cleanly formatted file may still contain both kinds of overlap.
- Confirm the scope of every operation.
- Check that unexpected rows were not removed.
- Inspect the exported file before handing it off.