Data Analysis · CSV Cleaning

To clean CSV data, first save a copy of the original file and inspect its columns, rows, and formatting. Check for duplicates, missing values, extra spaces, inconsistent labels, incorrect data types, and malformed records. Decide which issues require correction, then apply changes using a CSV cleaning tool, Excel, Google Sheets, or Python. Preserve identifiers and valid zero values, and do not automatically delete unusual observations. Finally, compare the cleaned file with the original, verify row counts and data integrity, and export a valid CSV.

This guide covers the full workflow, from understanding your raw file through export and documentation. Whether you use a spreadsheet, a no-code tool, or Python, the same principles apply. This article is part of the Data Analysis guides on AmriData.

Seven-Step CSV Cleaning Workflow

Step Action Why it matters
1Back up the source fileMakes every change reversible
2Inspect the CSVReveals structure, delimiter, and encoding
3Profile data qualityIdentifies actual problems before touching anything
4Choose cleaning rulesPrevents assumptions that corrupt data
5Apply correctionsStandardizes confirmed issues
6Validate resultsCatches accidental changes before they spread
7Export and documentProduces a reusable, auditable output
Clean Your CSV Online

What Is CSV Data?

CSV stands for Comma-Separated Values. It is a plain-text format for exchanging tabular data: each line typically represents one record, and the fields within a record are separated by a delimiter character, most often a comma.

order_id,customer,amount
001,Alice,120.50
002,Bob,85.00
003,Carol,65.25

The first line is the header row. Each subsequent row is one record, and the columns are separated by the delimiter. That structure looks clean until real-world files arrive with regional characters, address fields containing commas, or quote marks inside quoted names.

CSV is not the same as an Excel workbook. Saving a file as .csv discards multiple sheets, formulas as executable spreadsheet objects, cell styles, and workbook metadata. What remains is the text that importing software then interprets. That interpretation is where problems begin.

Quoted fields can contain commas, line breaks, and doubled quotation marks. This means the physical line count in a file is not always the same as its logical record count, and splitting on every comma will parse embedded commas as extra columns. A proper CSV parser handles this; a manual text-split operation does not.

Common CSV Data Problems

Most cleaning jobs involve the same category of issues. Knowing what you are looking for before you start editing makes it much harder to introduce new errors.

Problem Example Appropriate next step
Extra spaces" Alice "Consider trimming leading/trailing; check internal
Duplicate recordIdentical repeated rowVerify it is accidental before removing
Missing valueBlank cellInvestigate what the blank means
Inconsistent categorynorth, NORTHStandardise only if meanings are identical
Leading-zero ID00123Preserve as text — do not convert to a number
Ambiguous date03/04/2026Confirm convention before converting
Wrong delimiterSemicolons read as one columnRe-import with the correct separator
Placeholder missing tokenN/A, NULLDefine which tokens mean missing; treat explicitly
Number stored as text'85.00Convert after confirming the field is numeric
Potential outlierUnusually large purchase amountVerify against source record before any action

Notice that "potential outlier" does not mean error. An amount of $4,800 may be unusual in a dataset of $50 purchases, or it may be a perfectly valid bulk order. The source record resolves that question; the magnitude alone does not. For deeper investigation of distributions and unusual values, the exploratory data analysis guide covers those steps separately.

Complete Worked Example

Throughout this guide we use one fictional customer orders dataset. The numbers and names are invented for teaching purposes.

order_id,customer,region,amount,order_date
001,"  Alice  Smith  ",north,120.50,2026-10-01
002,Bob,North,,2026-10-02
002,Bob,North,,2026-10-02
003,"Carol, Jr.",SOUTH,0,03/10/2026
004,Dan,,N/A,2026-10-05
005,"Quoted ""Name""",East,50.00,2026-10-06
006,Eve," east ",75.25,2026-10-07
007,Sam,West,29.99,2026-10-08

This dataset has 8 data records and 5 columns. Before writing a single rule, here is what the profile shows:

  • One exact duplicate (data records 2 and 3 are identical), assumed confirmed as an accidental repeat for this teaching example
  • Two blank amount cells plus one N/A token, giving three missing-like entries under the policy we will define
  • One blank region for Dan (data record 5)
  • Inconsistent region capitalisation: north, North, SOUTH, east (with surrounding spaces)
  • All order IDs have leading zeros that must not be lost
  • One embedded comma in a customer name (Carol, Jr.) and one embedded quote (Quoted ""Name"")
  • One ambiguous date: 03/10/2026 could be March 10 or October 3
  • One numeric zero for Carol's amount — a valid recorded value, not a missing value

Before touching anything, we define the cleaning rules explicitly:

  1. Trim leading and trailing spaces in customer and region.
  2. Normalise region capitalisation to title case.
  3. Treat the exact token N/A as missing in the amount column.
  4. Preserve numeric zero as zero.
  5. Remove the confirmed exact duplicate row.
  6. Preserve all order IDs as text.
  7. Leave the ambiguous date unchanged until the source convention is confirmed.

After removing one duplicate: 7 records remain, all 5 columns are retained, the amount column has 2 missing entries (one blank that arrived with Bob, and the N/A for Dan), the zero for Carol stays zero, and order 001 still reads as 001.

FieldBeforeAfterRule applied
customer" Alice Smith ""Alice Smith"Outer-space trim only
regionnorthNorthTitle-case normalisation
regionSOUTHSouthTitle-case normalisation
region" east "EastTrim then title case
amountN/AMissingExplicit token policy
amount00Preserved as valid zero
order_id001001Preserved as string
order_date03/10/2026UnchangedAmbiguous — pending verification

Trimming outer spaces on " Alice Smith " produces "Alice Smith". The internal double space is not automatically collapsed — that requires a separate internal-whitespace rule, which has not been selected for this example.

The result is not perfect data. It is data where the documented formatting issues and the one confirmed duplicate have been addressed. The remaining missing values and the ambiguous date still need business-context verification before any further action.

Step 1: Save the Original File

This step sounds obvious, yet it is the one people skip. Destructive edits can remove records, silently change data types, overwrite identifiers, and alter calculations. Once the only copy is saved over, the original values cannot be reconstructed from the output alone.

Keep two files from the start. A suggested naming pattern:

orders-original.csv   ← never edited
orders-cleaned.csv    ← your working output

The source file is your audit trail. Everything that follows depends on being able to compare the cleaned result against what you started with.

Step 2: Inspect Rows, Columns, and Delimiters

Open the file in a plain-text editor before opening it in a spreadsheet. What you see there is what the file actually contains. Spreadsheet software may interpret values before you have a chance to control how.

Check these things before touching a single value:

  • Row count — how many records does the file claim to have?
  • Column count — does every row have the same number of fields?
  • Delimiter — is it really a comma, or a semicolon, tab, or pipe?
  • Header presence — is the first row a header, or data?
  • Duplicate headers — are any column names repeated or blank?
  • Encoding — does the file open cleanly, or do characters appear garbled?
  • Record definition — what does one row represent? One customer, one order, one order line item, one survey response?

The last point matters more than it seems. You cannot safely deduplicate a dataset until you know what one record represents. If one order can contain multiple products, rows with the same order_id are expected, not errors.

Step 3: Profile Data Quality

Before choosing corrections, measure what you have. A quick quality summary covers:

CheckWhat to measureExample for our dataset
Record countTotal rows (excluding header)8 records
Column countNumber of named fields5 columns
Exact duplicatesRows where all fields match1 duplicate group
Missing — amountBlank + N/A under the selected policy3 entries (37.5%)
Missing — regionBlank cells1 entry (12.5%)
Category variantsUnique values in regionnorth, North, SOUTH, East, " east ", West

The missing percentage formula is: missing entries ÷ relevant records × 100. Always state the denominator alongside the percentage — a 5% missing rate on a 20-row test file is very different from 5% on a 200,000-row production dataset. And even a small percentage can matter if the missing values concentrate in one date range, one region, or one customer type.

Step 4: Decide What Should Be Changed

This is the step most guides skip, and it is where data gets damaged. The decision sequence is:

Find → Investigate → Confirm → Change.

Do not apply a transformation because software offers it or because a rule sounds sensible in the abstract. Every cleaning decision should answer: what was wrong, what is the correct value, and how do I know?

Worth remembering: Inconsistent capitalisation is a technical issue you can resolve by choosing a standard. Whether Pending and In Progress mean the same thing in your system is a business question, not a formatting one. Mixing up the two kinds of decisions is a common source of data corruption.

Step 5: Apply Data Cleaning Rules

With confirmed rules in hand, the corrections themselves are usually mechanical. The specific operations depend on your tool, but the targets are the same across all of them.

Whitespace

Trim leading and trailing spaces from selected text columns. Decide separately whether to collapse repeated internal spaces — that is a different operation with different risk. Do not apply whitespace operations blindly to every column; a notes field may contain meaningful indentation.

Duplicates

Remove only confirmed exact duplicates where the business context explains the repetition as accidental. For key-based deduplication, you need an explicit rule about which columns define the key and which row to keep.

Missing values

Apply only the placeholder-token policy you defined in Step 4. Blank cells and text tokens like N/A, NULL, or None may need different treatment. Do not replace missing values with zero unless zero is the correct value for that specific field and row.

Category normalisation

Apply consistent capitalisation or spelling only when the variant labels refer to the same real-world category. Map NY, New York, and New York State to a single label only if the field definition confirms they represent the same thing.

Data types

Convert text to numbers, dates, or booleans only after confirming the field's purpose. Never convert an ID field to a numeric type, even if every value looks like a number. See the data types section below for details.

Step 6: Validate the Cleaned CSV

Cleaning is not complete until you have checked the output. The minimum validation checklist:

  • Row count: original minus intentionally removed rows equals the expected cleaned count (8 − 1 = 7 in this example).
  • Column count: no columns were accidentally dropped or added.
  • Identifiers: leading zeros are intact; no numeric conversion occurred.
  • Zero values: genuine zeros have not been replaced by missing values.
  • Missing count: matches the expected count under your defined policy.
  • Category values: all expected labels appear after normalisation; no unexpected values were introduced.
  • Dates: ambiguous dates are still in their original form if unresolved.

If the row count is 6 instead of 7, investigate the missing record before doing anything else. A correct result requires a traceable explanation for every row that was removed.

Step 7: Export and Document the Cleaned CSV

When saving the cleaned file, check that the output is valid CSV: proper delimiter, quoted fields where needed, consistent encoding (UTF-8 is safest for most uses), and no worksheet formulas or formatting carried over from a spreadsheet.

Document what changed in a cleaning log alongside the output file:

OperationFieldReasonAffected records
Trim outer spacescustomer, regionInconsistent leading/trailing whitespace2
Title-case normalisationregionEquivalent labels in different cases3
Normalise missing tokenamountSource-defined placeholder N/A1
Remove confirmed duplicateFull rowAccidental repeated export (rows 2 and 3 identical)1

A log like this costs five minutes to write and saves hours of confusion when someone asks why the row counts changed, or why a category that was in the original data does not appear in a later report.

How to Remove Duplicate Rows From CSV

Three types of repetition look similar but need different responses.

Exact duplicates

Every field in two or more rows matches. If both represent the same accidentally repeated record — from a double export, a form submission glitch, or a merge error — one row may be removed. But the decision belongs to the source context, not the pattern alone.

Key-based duplicates

The identifier field repeats, but other columns differ. An order with two line items will have the same order_id in both rows. Deleting one row because order_id repeats removes a real product from the order. This is data loss dressed up as data cleaning.

Semantic duplicates

Two records describe the same real entity but differ in spelling, spacing, or format. These require investigation and a merge decision grounded in source documentation, not an automated rule.

One more thing: cleaning order matters. The values north and " North " will not match as duplicates before normalisation. After trimming and lowercasing, they might. Run your normalisation rules before counting duplicates, and recount after to see what changed.

How to Handle Missing Values in CSV Data

Missing data is not a uniform problem. A blank cell might mean the value was never collected, the question was not applicable, the import failed, or the field genuinely has no value for this record. The appropriate response depends on the reason, and the reason is not visible in the file.

Common missing-value representations in CSV files include empty cells, whitespace-only cells, NULL, N/A, None, and application-specific tokens like - or 9999. Not every NA is a missing value — it may be a country code or a product abbreviation. Define which exact tokens mean missing for each specific column before applying any rule.

The difference between amount = 0 and amount = blank is fundamental. A zero-dollar amount could represent a free transaction, a refund that cancelled out, or a promotional order. Replacing a blank with zero changes the data's meaning and every calculation that follows.

Missing-value decisions

The right response depends on context:

  • Leave unchanged — when the source is unclear and assumptions are dangerous.
  • Verify at source — when the original system can supply the missing value.
  • Mark as missing — flag it explicitly and include it in counts without guessing.
  • Fill with a known value — only when the correct value is available and confirmed.
  • Impute using a documented method — mean or median substitution can be used for specific analytical purposes, but it changes distributions, may underestimate variability, and should be documented as an analytical choice rather than a data-cleaning default.
  • Exclude records — only when justified by the analysis goal, with the exclusion logged.

Deleting every row that contains a blank is almost never the right default. If 30% of your records have a blank in one column, deleting them removes a third of your data, and the remaining 70% may no longer represent the original population fairly.

How to Remove Extra Spaces From CSV Data

Whitespace is one of the most common sources of invisible inconsistency in CSV files. Three separate things go by the name "extra spaces," and they call for separate decisions.

Leading spaces appear before the first visible character: " Alice". Trailing spaces appear after the last: "Alice ". Repeated internal spaces appear within the value: "Alice Smith". A trim operation on the outer edges removes the first two types; it does not collapse the internal double space.

Case normalisation works similarly. The values north, NORTH, and North can be standardised to a single form — but only if they represent the same category. Product names, company names, acronyms, and case-sensitive identifiers should not be altered automatically.

Watch out for substring replacement. Replacing east with East as a whole-cell operation is safe. Running it as a substring replacement across a column could change words inside longer values — turning "Northeast" into "NorthEast" or "Mideast" into "MidEast".

How to Fix Data Types in CSV Files

The question to ask about any field is not "does it look like a number?" but "is it a measurement, or is it an identifier?"

A measurement — sales revenue, delivery time, temperature — can reasonably be averaged, summed, or compared by magnitude. An identifier — order ID, postal code, product code — exists to label a record uniquely. 00123 and 123 are different identifiers. Averaging order IDs produces a meaningless result. Converting them to integers silently drops the leading zeros, and those zeros cannot be recovered from the output.

Large numeric identifiers carry a different risk. A value like 12345678901234567890 may exceed the precision of floating-point representation, producing rounding that changes the ID and makes records impossible to match against the source system.

Dates

Dates are the most common source of silent data corruption. The value 03/04/2026 is March 4 in the United States and April 3 in much of Europe and the United Kingdom. Software that guesses the locale will be correct most of the time and silently wrong when it is not. Do not convert a date field until you have confirmed the original format from source documentation or the person who produced the file.

Once the format is confirmed, YYYY-MM-DD is the clearest option for storage because it sorts correctly as text. If timestamps are involved, confirm whether they are local time, UTC, or carry an explicit timezone offset before any conversion.

Currency and numbers

A value like $1,250.00 requires careful handling. Stripping the currency symbol and thousands separator seems simple, but 1,234 means one thousand two hundred thirty-four in a locale that uses commas as thousands separators, and one point two three four in a locale that uses commas as decimal separators. Confirm which convention the file uses before converting.

How to Fix CSV Formatting Errors

Some problems are not data values — they are structural issues with the file itself that prevent correct parsing.

Wrong delimiter. A file exported from a European system may use semicolons as the separator. Opening it in software configured for commas will parse every row as a single column. The fix is to re-import with the correct delimiter, not to replace all semicolons in the data.

Inconsistent field counts. If the header has five fields and a data row has six, the extra value usually means an unquoted comma, a missing quotation mark, or corruption. Shifting values sideways across columns to "fix" the count creates new errors; investigate the specific row first.

Encoding problems. UTF-8 is the broadest-compatible encoding for CSV files that contain non-ASCII characters. A file saved in a regional encoding and opened as UTF-8, or vice versa, will display garbled characters or replacement symbols. The correct fix is to identify and re-save with the appropriate encoding — not to delete the affected characters, which would destroy valid multilingual content. Names in Arabic, Urdu, or accented Latin characters are real data, not anomalies to be removed.

CSV formula injection. A cell value that begins with =, +, -, or @ may be interpreted as a spreadsheet formula when the file is opened in Excel or Google Sheets. This is not a CSV parsing issue — it is a spreadsheet behaviour. It has security implications when CSV files contain user-generated content that others will open. CSV quoting alone does not prevent a formula from executing once imported into a spreadsheet. Application-level handling is needed for this case; the right solution depends on the context and destination of the file.

Method 1: Clean CSV Data Online With AmriData

Open the AmriData CSV Data Cleaner for a no-code cleaning workflow. Review the operations available in the tool, choose only those supported by your cleaning rules, and compare the exported file with the original.

Start with the fictional dataset in this guide. Check that the exported result preserves IDs, zero values and quoted names before using the same rules on your own file.

Clean Your CSV Data Online

When you upload sensitive data to any online tool, check your organisation's data-handling guidelines. Verify what the tool does with uploaded files before proceeding.

Method 2: Clean CSV Data in Excel

Excel is familiar to most people working with data, but it introduces risks that are specific to CSV files. The most important is automatic type conversion on open: if you double-click a CSV, Excel may immediately convert 001 to 1, interpret a date in the wrong format, or parse a large identifier as a floating-point number. By the time you see the data, the displayed worksheet may already differ from the source file; keep that source unchanged.

Use Data → From Text/CSV and check the delimiter in the preview. In the Power Query workflow, choose Transform Data, remove an automatically added Changed Type step if it converted identifiers, then set ID columns to Text before loading. In the legacy Text Import Wizard, select Text for those columns. If values have already been altered, re-import the unchanged original; changing the final cell format cannot restore the original IDs.

Removing duplicates in Excel

Select your data range, then use Data → Remove Duplicates. The dialog shows which columns to compare. Selecting all columns removes exact duplicates across every field. Selecting only key columns removes rows where those columns match, regardless of what the other fields contain — a very different operation. Confirming the dialog changes the active dataset, so work on a copy.

Whitespace and cleanup formulas

=TRIM(A2) removes leading and trailing spaces and collapses repeated internal spaces to a single space. That internal collapsing is different from a simple outer-edge trim; confirm it is appropriate for your data. =CLEAN(A2) removes certain nonprinting characters. The combination =TRIM(CLEAN(A2)) handles both, but neither removes every possible Unicode whitespace or control character.

For case normalisation: =LOWER(A2), =UPPER(A2), and =PROPER(A2) exist in Excel. PROPER capitalises the first letter of every word, which can produce unexpected results with hyphenated names, acronyms, or single-letter middle initials.

Power Query for repeatable cleaning

Power Query (available via Data → Get Data → From Text/CSV in recent Excel versions) lets you build a transformation sequence that can be refreshed when the source file updates. It handles column type assignment, trim and clean operations, value replacement, and duplicate removal through a graphical interface. Once built, the steps are recorded and replayable — more reliable than rebuilding the same manual operations each time. The clean data in Excel guide covers the full Power Query workflow.

When saving from Excel back to CSV, the output represents one worksheet of data. Worksheet-specific features — multiple sheets, formulas as computed values, cell styles — do not carry into the CSV. Check ID and date columns after saving to confirm they exported correctly.

Method 3: Clean CSV Data in Google Sheets

Google Sheets handles CSV import through File → Import, where you can choose the separator and review the available automatic-conversion settings. Disabling automatic conversion for the import gives you more control over what arrives in the cells.

The cleaning functions available in Sheets parallel those in Excel: TRIM, LOWER, UPPER, and PROPER all work the same way, with the same considerations for internal spaces and acronyms. For removing duplicates, use Data → Data cleanup → Remove duplicates; the dialog lets you choose which columns to compare.

Google Sheets is cloud-based. When you import a file, its contents move to Google's servers. This is appropriate for many datasets and unsuitable for others. If you are working with personally identifiable information, confidential records, or data subject to regulatory constraints, check your organisation's policies before using a cloud tool. This is not a limitation of Sheets specifically — it applies to any cloud service.

Exporting back to CSV uses File → Download → Comma-Separated Values. The download represents the active sheet. Formatting, formulas as results rather than expressions, and additional sheets do not carry into the CSV.

Method 4: Clean CSV Data With Python and pandas

Python with pandas is the right choice when you need a repeatable, documented, version-controlled cleaning process — the same script can run against next month's export with the same logic applied consistently. The tradeoff is that incorrect defaults can silently change data; the examples below make the choices explicit.

Install pandas if you have not already:

pip install pandas

Import with type control

import pandas as pd

df = pd.read_csv(
    "orders-original.csv",
    dtype="string",                  # preserve raw text in every column
    keep_default_na=False,           # do not auto-convert default missing tokens
    encoding="utf-8-sig"             # handles UTF-8 BOM from some exports
)

The dtype="string" argument tells pandas to import every column as text, so 001 remains 001. The keep_default_na=False argument prevents pandas from converting its default list of missing tokens at import time — you decide which tokens are missing in the cleaning step. This is important because N/A is on that default list, and you may want to handle it explicitly rather than have it silently disappear during import.

Inspect before changing anything

print(df.shape)             # (rows, columns)
print(df.head(8))           # first 8 rows
print(df.dtypes)            # inferred or assigned types
print(df.duplicated().sum()) # exact duplicate count

Clean column names

df.columns = df.columns.str.strip()
assert df.columns.is_unique, "Duplicate column names after trimming"

Stripping column names can create duplicate names. Check df.columns.is_unique afterwards. Also inspect the original header: pandas can rename duplicate input headers during import, so this check alone does not prove the source header was unique.

Trim text columns

df["customer"] = df["customer"].str.strip()
df["region"]   = df["region"].str.strip()

Applying .str methods to a mixed-type column or a column with errors can cause unexpected behaviour. Target specific columns.

Normalise region to title case

df["region"] = df["region"].str.title()

This is appropriate for this fictional example where all values are geography labels. For columns containing product names, legal entities, or acronyms, title case may produce wrong results.

Handle missing amount values explicitly

df["amount"] = (
    df["amount"]
    .astype("string")
    .str.strip()
    .replace({"": pd.NA, "N/A": pd.NA})
)

This converts the column to string, trims any surrounding whitespace, then replaces the empty-string and N/A tokens with pandas NA. It is an explicit policy for this column, not a general blanket rule.

Convert amount to numeric

df["amount"] = pd.to_numeric(
    df["amount"],
    errors="raise"   # surface unexpected values — do not silently discard
)

Using errors="raise" means the conversion will fail if any non-numeric, non-NA value remains in the column. In a controlled teaching example, that surfaces problems immediately. In production, investigate any failure before deciding how to handle it.

Remove confirmed exact duplicates

df = df.drop_duplicates(keep="first").copy()

This example assumes the repeated Bob record has been confirmed as accidental. Normalisation can make previously distinct rows match; review any newly matching records before removing them. Without a subset argument, this checks all columns. Key-based deduplication requires specifying the relevant columns in subset=["order_id"], which has very different implications — it would keep only the first row per order ID and remove everything else with that ID, including legitimate order items.

Leave the ambiguous date unchanged

The value 03/10/2026 stays as text until the date convention is confirmed from the source. Adding an automatic date parser here would silently choose between March 10 and October 3 without a reliable basis for the choice.

Validate before exporting

# Row reconciliation
assert len(df) == 7, f"Expected 7 rows, got {len(df)}"

# Column count
assert df.shape[1] == 5, "Column count changed"

# Leading zeros preserved
assert df.loc[df["order_id"] == "001", "order_id"].iloc[0] == "001"

# Zero amount preserved
assert (df["amount"] == 0).any(), "Zero amount was removed or changed"

# Missing count: 2 (Bob's blank + Dan's N/A, after duplicate removal)
assert df["amount"].isna().sum() == 2, f"Unexpected missing count: {df['amount'].isna().sum()}"

print("All validation checks passed.")

Export

df.to_csv(
    "orders-cleaned.csv",
    index=False,
    encoding="utf-8-sig"
)

The index=False argument omits the pandas row-number index from the output. The utf-8-sig encoding adds a byte-order mark that helps Excel identify the encoding when the file is opened.

Exporting preserves the text representation of the values in the DataFrame. Spreadsheet software that re-opens the cleaned CSV may still apply automatic type conversion to identifiers and dates. The solution is to import with type controls on the receiving end, not to fight the export format.

Common pandas errors

ErrorLikely causeWhat to check
FileNotFoundErrorWrong path or working directoryConfirm the file path with os.getcwd()
UnicodeDecodeErrorFile encoding does not match encoding argumentConfirm the source encoding before decoding; a decoder that accepts the bytes may still produce wrong characters
ParserErrorInconsistent field counts or unmatched quotesInspect the specific row; do not skip it automatically
KeyErrorColumn name not found after importCheck for leading/trailing spaces in header row
Unexpected ValueError from to_numericNon-numeric characters still in columnInspect the offending values; decide how to handle each

Avoid on_bad_lines="skip" as a default resolution for parser errors. It silently discards records, and the records it drops are often the ones with the underlying problem that needs investigation.

Choosing a Cleaning Method

If you are cleaning a file once and your team works in spreadsheets, Excel or Google Sheets fits naturally. If the same cleaning runs monthly against updated exports, writing it in Python once and running it on each new file costs less time overall. The AmriData CSV Cleaner is the fastest path for standalone files when no code or spreadsheet setup is needed.

How to Check Whether CSV Data Is Clean

This step distinguishes a cleaning workflow from a set of operations applied and forgotten. Many articles explain how to remove duplicates but not how to confirm the result is still valid.

Run through this checklist after any cleaning pass:

  • Row count: original rows minus intentionally removed rows equals expected cleaned rows.
  • Column count: same number of columns as the original, in the same order.
  • Identifiers: spot-check that leading zeros, long IDs, and expected unique values are all present.
  • Valid zeros: confirm that zero values were not converted to missing or removed.
  • Missing counts: recount under your defined policy and compare to the pre-cleaning profile.
  • Category labels: all expected values appear; no unexpected variants were introduced by normalisation.
  • Dates: dates that were left unresolved are still in their original form.
  • Round-trip test: if practical, export, re-import, and compare to the cleaned DataFrame directly.

If the row count is off by one in the wrong direction, that missing record may have been deleted by an overly broad deduplication rule, a filter that was not removed, or an export that cut off the last row. Track it down before moving on.

Common CSV Data Cleaning Mistakes

MistakeWhy it mattersBetter approach
Overwriting the only copyNo recovery path if cleaning goes wrongSave -original and -cleaned as separate files
Splitting CSV on every commaDestroys embedded comma fieldsUse a proper CSV parser
Removing all repeated IDsLegitimate order items share IDsConfirm the row definition before deduplication
Replacing missing with zeroChanges meaning and every downstream calculationInvestigate what the blank represents first
Dropping every row with a blankCan remove a large fraction of valid recordsDecide per column and per business context
Converting IDs to numbersDestroys leading zeros and risks precision lossTreat identifiers as strings throughout
Guessing ambiguous datesSilent errors in every date-dependent analysisConfirm format from source documentation
Title-casing all columnsBreaks product codes, acronyms, and namesApply case rules to categories only, deliberately
Deleting potential outliers automaticallyA large value may be a valid bulk purchaseVerify against the source record
Ignoring encoding issuesGarbled characters corrupt names and addressesIdentify and re-save with the correct encoding
Skipping row count validationData loss goes undetectedAlways reconcile rows before and after
Opening CSV in Excel without import controlsAutomatic type conversion changes data on openUse Data → From Text/CSV and set column types manually

Documenting Your Cleaned Data

Once the file is clean, consider documenting what each field contains using a data dictionary. A data dictionary records field names, observed types, missingness rates, uniqueness, and field descriptions. It makes the cleaned output usable by others who were not present for the cleaning process, and it catches the kind of inconsistency that re-appears when new exports arrive.

The AmriData Data Dictionary Generator can help with this step.

Frequently Asked Questions

How do you clean CSV data?

Save a copy of the original file, inspect its structure, profile data quality issues, choose specific corrections, apply them, validate the row count and column integrity, and export a documented output. The tool — a no-code cleaner, Excel, Google Sheets, or Python — does not change the underlying process.

Can I clean CSV data without coding?

Yes. The AmriData CSV Data Cleaner handles common operations through a visual interface. Excel and Google Sheets also offer no-code cleaning with formulas and menu-based tools.

How do I remove duplicate rows from a CSV?

First confirm what one record represents in your dataset. Then remove only rows where all relevant columns match and the repetition is confirmed to be accidental. In Excel, use Data → Remove Duplicates. In pandas, use df.drop_duplicates(). Removing rows based on a single key column has very different implications from removing full-row duplicates.

Should I replace blank cells with zero?

No, not automatically. A blank cell indicates missing or unspecified information; its precise meaning depends on the source. A zero is a real numeric value. Replacing blank with zero changes the data's meaning and affects every average, sum, or count that uses the column. Investigate what the blank represents before deciding how to treat it.

How do I preserve leading zeros in a CSV?

Treat identifier columns as text from the start. In pandas, use dtype={"order_id": "string"} in read_csv. In Excel, import via Data → From Text/CSV and set the column type to Text. Once a spreadsheet application has converted 001 to 1, the original value cannot be recovered from the file.

Why did Excel change my IDs when I opened the CSV?

Excel applies automatic type conversion when a file is opened by double-clicking. It interprets numeric-looking values as numbers, which drops leading zeros and may round large identifiers. Use Data → From Text/CSV and assign affected columns the Text data type during import to prevent this.

How do I fix CSV date formatting?

Confirm the original date format from the source system or the person who produced the file. An ambiguous value like 03/04/2026 should stay unchanged until you know whether it means March 4 or April 3. Once confirmed, convert to YYYY-MM-DD for unambiguous storage.

How do I fix CSV delimiter problems?

If columns are misaligned, check whether the delimiter is different from what your tool expects. A file exported from a European system often uses semicolons. Re-import with the correct separator rather than searching and replacing the delimiter character inside the data.

How do I fix CSV encoding errors?

Open the file in a text editor to see whether characters are garbled. If so, the encoding used to save the file differs from the encoding used to read it. UTF-8 is the most compatible option. In pandas, use the encoding parameter in read_csv to specify the correct encoding.

How do I validate a cleaned CSV?

Compare row counts before and after, confirm column counts are unchanged, spot-check identifier values for leading zeros, verify that zero values were not converted to missing, recount missing values under your defined policy, and check that category labels normalised correctly. A cleaning log alongside the file makes this reproducible.

What is CSV formula injection?

A cell value beginning with =, +, -, or @ may be treated as a spreadsheet formula when the CSV is opened in Excel or Google Sheets. This can execute unintended calculations or, in cases involving user-generated content, run malicious commands. CSV quoting alone does not prevent this. See OWASP's CSV injection guidance for application-level approaches.

What is the difference between duplicate rows and duplicate keys?

A duplicate row is a record where every field matches another row exactly. A duplicate key is a record where one or more identifier fields match another row, while other fields differ. An order with two products has a duplicate order ID but is not a duplicate row. Knowing the difference prevents accidentally deleting legitimate data.

Sources and References

← Back to Data Analysis