Cleaning messy CSV data in the browser

A CSV export from a CRM, a webshop, or a spreadsheet is rarely clean. Rows repeat, columns are in the wrong order, blank lines appear in the middle, and numbers arrive as text. Cleanup follows a predictable order, and every step can happen in your browser, which matters when the file contains customer names, emails, or order histories. This guide walks through that order with a small example.

The example

Name,Team,Score,Active
Ada,Design,92,true
Lin,Engineering,84,true
Sam,Design,76,false
Ada,Design,92,true
Jo,Engineering,88,true

Five data rows, one of them an exact duplicate (Ada), and four columns. Real files have thousands of rows, but the steps are the same.

Step 1: understand the shape

Open the file in the spreadsheet editor and check the basics: one header row, the same number of fields in every row, and a delimiter that matches the content. Files from European software often use semicolons, because the comma is the decimal separator there. A row with too few or too many fields usually means a delimiter or quote was misread, and the tools report it instead of shifting data into the wrong columns.

Watch for fields that contain the delimiter, quotes, or line breaks. They must be wrapped in double quotes, with inner quotes doubled, as in "Smith, John" and "He said ""hi""". The CSV tools parse these correctly. Splitting on commas by hand does not.

Step 2: keep only the columns you need

Exports often carry dozens of columns, some containing personal data you do not need to share. The CSV column picker selects and reorders columns in one step. Picking Score and then Name produces a two-column file in that order. Fewer columns mean fewer places for errors, and sharing less data is good practice under privacy laws such as the GDPR.

Step 3: remove duplicates by a key

A duplicate is only a duplicate with respect to some key. Comparing all columns, remove duplicate CSV rows removes the second Ada and keeps four rows. Comparing only the Team column answers a different question, “one row per team”, and keeps just Ada and Lin. For contact lists, the email address is usually the right key, and “keep last” retains the most recent row when the file is sorted by date.

Values must match exactly, so [email protected] and [email protected] count as different. Normalize case and trim spaces first if near-duplicates should be merged. The text cleanup guide covers the invisible characters that cause false mismatches.

Step 4: filter out noise

Filter CSV rows keeps rows matching a condition on one column. “Team equals Design” keeps three rows, and “Score greater than 80” keeps four. Text conditions (contains, equals, starts with, ends with) and numeric conditions (greater than, less than) are separate, because 100 is greater than 80 as a number but sorts before it as text. Numeric filters need a number in every cell of the column. Blanks and thousands separators are reported rather than silently dropped. Run the filter twice to combine two conditions.

Step 5: sort deliberately

Sort CSV sorts by one column as text or as numbers. The difference matters: 9, 10, and 100 sort as 10, 100, 9 in text mode. Dates sort correctly as text only in ISO format (2026-09-24). Sorting is stable, so to sort by two columns, sort by the secondary column first and then by the primary.

Step 6: reshape if needed

Reports laid out for reading, with months across the top and metrics down the side, need to be flipped for charting or database import. Transpose CSV swaps rows and columns, so Metric,Jan,Feb becomes a column of months.

Step 7: convert only after cleaning

Cleaning first makes conversion predictable. CSV to JSON produces an array of objects keyed by the header, or an array of arrays. Values stay strings by default, so IDs keep their digits and leading zeros. With value conversion on, 92 becomes a number and true a boolean, but 01234 stays a string, because converting it would lose the zero. Going the other way, JSON to CSV collects every key into columns and writes nested values as JSON text.

Excel and the CSV traps

• Leading zeros: Excel turns 01234 into 1234. Import the file with the column set to Text instead of double-clicking it.
• Long numbers: numbers with more than 15 digits, such as card-like IDs, are rounded or shown in scientific notation.
• Dates: values like 3-4 or MARCH1 become dates. This is famous for corrupting gene names in scientific data.
• CSV injection: a cell starting with =, +, -, or @ can run as a formula when opened. Enable formula escaping when exporting CSV that others will open in a spreadsheet.
• Byte-order mark: files saved as “CSV UTF-8” start with an invisible character that can stick to the first header name.

Limits

The CSV tools accept up to 500,000 characters of input, and the workspace handles up to 10,000 rows and 100 columns so the browser stays responsive. For bigger files, split them or use a database or scripting tool. All processing happens locally: the data never leaves your device.