How to clean a CSV file without losing data

Decide what should change before cleaning

A useful cleanup starts with a specific destination. A product import might need an exact set of headers. A reporting file might need fewer columns. A spreadsheet might need product codes treated as text. Decide what the receiving system expects before removing or converting anything.

Keep an untouched copy of the original export. Then separate unwanted formatting from meaningful information: spaces around a product name may be accidental, while spaces within the name belong there. Two rows with the same item ID may still represent different records.

A CSV file holds a table as text. It does not carry a spreadsheet’s column-type settings. Cleaning the text and importing it correctly are two separate checks; the workflow below covers both.

Start with a small catalog example

Download the fictional catalog CSV and load it into the CSV Cleaner & Converter. These invented stationery products are practice data, not a client export. The file has five columns and seven data rows, including one empty row.

• IDs: values such as 00123 need their leading zeros.
• Whitespace: the first product name has spaces around it; its later duplicate does not.
• Quoted text: a note contains a comma, and another contains the quoted label "A6".
• A literal plus sign: + gift sleeve is a product note that will trigger the export’s formula safeguard.
• Distinct records: the two Desk Pad rows share an item ID but have different internal_ref values.

Commas and quotes inside notes are not errors. In the common CSV format documented by RFC 4180, fields containing commas, quotes or line breaks are enclosed in double quotes. A quote within a quoted field is doubled. Spaces are part of field values, so trimming is a deliberate change.

Do not remove quotation marks with a search-and-replace operation. Some of them separate fields correctly; others are part of the actual note.

Clean the table and check the counts

1. Choose “Upload a file.” Select the original sample. Set “Input separator” to “Comma (,)” and keep “First row contains headers” selected.
2. Preview before making changes. Leave the three optional cleanup boxes unchecked and select “Clean & preview.” You should see seven data rows and five columns.
3. Select the intended cleanup. Enable “Trim spaces around values,” “Remove blank rows” and “Remove duplicate rows,” then select “Clean & preview” again.
4. Compare the result with the original. The table should now have five rows. The summary should report one value trimmed, one blank row removed and one duplicate row removed.

Duplicate removal compares every cell after trimming when trimming is enabled. The first matching row stays in place. In this example, trimming makes the two Field Notes Journal rows identical, so the later occurrence is removed. The Desk Pad rows stay because their internal references differ.

That is useful for exact duplicates, but it is not a rule for merging customer or product records. A repeated ID with different details needs a decision about which record is correct. This tool does not make that decision for you.

The cleanup options also apply differently: trimming affects headers and values, while blank-row removal applies to data rows. The original text remains in the input so you can change the options and run a new preview.

Prepare the columns for the destination

Open “Choose, rename & reorder columns.” For this example, rename the name header to product_name, exclude source column 5 (internal_ref) and keep the remaining order. The prepared table should show four included columns and one renamed header.

The two Desk Pad rows now look identical. Both still appear because duplicate cleanup ran against all original columns before the column selection. Excluding a distinguishing column does not silently delete another row. If those references matter to the receiving system, keep the column instead.

For a real import, use the destination’s required header spelling and order. “Move earlier” and “Move later” change export order, and “Reset columns” restores the selected cleanup result’s original columns, names and order.

Choose an export and review its changes

Use “CSV · Comma-separated” for this walkthrough. Keep “Prefix formula-like cells with an apostrophe” selected and leave the optional UTF-8 BOM unchecked to match the downloadable comparison file. The export should report one formula-like cell prefixed.

The literal note + gift sleeve becomes '+ gift sleeve in the CSV. This is intentional: a spreadsheet can interpret some values beginning with characters such as = or + as formulas. The safeguard can therefore change ordinary text as well as suspicious input.

Review that change instead of treating the setting as a universal guarantee. OWASP’s CSV injection guidance explains that behavior varies between spreadsheet applications and that saving and reopening a file can remove protections. Check how the actual receiving application handles the values, particularly when working with untrusted form submissions or exports.

Select “Download CSV” to save the result. You can compare it with the prepared example CSV. The export quotes every field, so its formatting differs from the original even where the values have not changed.

If an application needs JSON, choose “JSON · Objects with column names.” Each row becomes an object using the included headers; every value remains a string. The prepared example JSON keeps 00123, 12.00 and the original plus-sign note as strings without the CSV apostrophe. JSON does not make those values safe for every later use.

JSON objects require unique, nonempty headers. JSON arrays are another option and include the header row when selected. The tool also exports tab-, semicolon- and pipe-separated text; choose the format your destination supports.

Check the import, not just the download

Open the exported CSV in a plain-text editor to confirm that IDs and notes are intact. Then use the receiving application’s import preview before accepting the file. A successful download only shows that a file was created; it does not establish how another application will read it.

For Excel, use its text/CSV import workflow and set identifier columns to Text before loading the data. Microsoft’s leading-zero guidance explains the relevant import and conversion options. Double-clicking a CSV can allow automatic conversions, and adding a display format afterward does not recover information already removed.

The optional UTF-8 BOM can help spreadsheet software recognize the file’s encoding. It does not assign column types or preserve leading zeros by itself. Confirm both the accented Café Planner name and the 00123 ID in the destination.

• Count records: confirm five data rows, with no header imported as a product.
• Check text: inspect the comma in the first note, the quotes around A6 and the accented product name.
• Review conversions: confirm ID handling, decide how prices should be typed and inspect the prefixed note.
• Test a small import: use a preview or test environment when the destination would update existing records.

Keep a repeatable cleanup record

Save the original, the prepared file and a short note listing the chosen options. For this example: trim outer whitespace, remove blank and exact duplicate rows, rename the product header, omit the internal reference, retain both Desk Pad records and use the default CSV formula safeguard.

Plainview’s cleaner works in the browser tab; it does not upload or save the file content. Download the result before leaving. It accepts UTF-8 delimited text up to 2 MiB, 10,000 data rows, 100 columns and 200,000 cells including headers, with an 8 MiB export limit. It does not import Excel workbooks or JSON.

Start with the CSV Cleaner & Converter when those limits fit the task. If a recurring export needs record-matching rules or a connection to another system, that calls for a defined workflow; Plainview’s forms and workflows service can help scope that work.