To clean a CSV file, work in a fixed order: confirm the delimiter and encoding so the file parses into the right columns, tidy the headers, trim whitespace and standardise case so the same value is always written the same way, convert dates to one format, decide what to do about empty cells and duplicate rows, and only then export — with any cell that starts with =, +, - or @ neutralised so a spreadsheet cannot run it as a formula. Order matters because each step changes what the next step sees: a duplicate hidden by stray spaces and a different date format is invisible until you have trimmed and standardised first. Our CSV Cleaning Studio applies those steps in your browser; it accepts files up to 20 MB or 200,000 rows, which are the limits stated here so you can plan around them rather than discover them mid-task.

What a CSV file actually is

A CSV file is plain text: rows separated by line breaks, fields within a row separated by a delimiter — a comma in the classic form described by RFC 4180, but very often a semicolon or a tab in files exported from European locales and other systems. A field that itself contains the delimiter, a quote or a line break must be wrapped in double quotes, and a quote inside a quoted field is doubled. That is the whole format. There are no types, no formulas and no formatting in the file itself; a column of amounts is just text that a program may interpret as numbers. The W3C's model for tabular data makes the same point from the other direction: a CSV becomes a table of rows and columns only once a parser has decided on a delimiter, an encoding and a header row. Cleaning, therefore, is mostly the work of making those decisions explicit and then making the values inside the table consistent.

Step 1: Get the parse right — delimiter, encoding, headers

If the delimiter is wrong, nothing else can be right: a semicolon file read as comma-separated becomes one wide column, and every later check passes vacuously on nonsense. Let the parser detect the delimiter, then eyeball the result — the column count should be constant and the headers should sit one per column.

Method for the worked example in this article: we wrote a deliberately messy 12-row fixture file, semicolon-delimited, and parsed its text in Node using the same PapaParse build the studio ships, with delimiter detection left on. The parser reported delimiter ; with 0 parse errors, read the header row as Order ID, Customer, City, Order Date, Category, Amount, Note, and returned 12 data rows. The studio's type detection, run on the parsed columns, labelled them Order ID integer, Customer text, City text, Order Date date, Category text, Amount float and Note text — a useful first profile: if a column you expect to be numeric is detected as text, a stray character somewhere in it is usually why.

Encoding deserves the same thirty seconds. A file saved as UTF-8 with a byte-order mark, or as a legacy Windows encoding, can produce a mangled first header or accented characters turned into replacement symbols. Fix encoding at load time; no amount of later editing repairs characters that were decoded wrongly.

Step 2: Trim, standardise case, standardise dates

These three transforms look cosmetic and are actually structural. In the fixture, one customer arrived as Alice Smith with leading and trailing spaces, cities arrived as london, PARIS, berlin and madrid, and dates arrived in four forms side by side: 2024-01-05, 05/01/2024, 15 Jan 2024 and Jan 20, 2024. Grouping that file by city would count london and a capitalised variant as different cities; sorting by date as text would scatter the months.

Method: we called the applyOperations function exported by the studio's csv-studio.js on the parsed fixture table, with trimming on, title case for text columns, and dates standardised to ISO format, and with missing-value handling and deduplication switched off for this stage. The function reported 12 rows in, 12 rows out and 23 cells changed. After that stage, london, PARIS and the rest read London, Paris, Berlin and Madrid; the padded name read Alice Smith; and every date read in ISO form — 05/01/2024 became 2024-01-05 (the studio reads ambiguous slash dates day-first, so 02/03/2024 on another row became 2024-03-02, the 2nd of March, not February 3rd — a convention to check against your own source before you trust it), 15 Jan 2024 became 2024-01-15, and Jan 20, 2024 became 2024-01-20.

Step 3: Decide about missing values and duplicates — do not drift into a decision

Two kinds of problem were planted in the fixture, and both are covered in depth in How to Remove Duplicates and Handle Missing Data: two rows had an empty Amount, one row had an empty City, one order appeared twice as an exact copy once the formatting was standardised, and one order number appeared twice with conflicting amounts (36.00 first, 99.00 later). The point for this workflow is only that the decision comes here, after standardisation, and that it is a decision: drop the incomplete rows, fill the gaps with a stated value or a statistic, keep the first occurrence of a duplicate or the last. Each choice changes the totals a later chart will show, so record which one you made.

Step 4: The full run, with its real change summary

Method: a second call to the same applyOperations function, this time on the original parsed fixture, with trimming on, title case on, ISO dates, missing values kept as they were, and deduplication keyed on the Order ID column only. The returned statistics were: rows before 12, rows after 10, duplicates removed 2, and the note "Removed 2 duplicate rows using key column: 'Order ID'." Both repeats of an order number were removed under that key — including the conflicting 99.00 row, because key-based deduplication keeps the first occurrence and discards later ones regardless of their other values. That is correct behaviour for "one row per order," and it is exactly why the key must be chosen deliberately: keyed on all columns instead, only the one exact duplicate goes.

One number in that summary needs an honest footnote. The function also reported cellsChanged: 74, far more than the 23 cells the standardisation stage changed. Its counter compares tables cell-by-cell by position, so once two rows are removed, every row below them shifts up and counts as "changed" against whatever used to sit in that position. Treat the figure as a positional difference between the input and output tables — the studio shows it the same way — not as a count of cells a human edited. The trustworthy audit numbers in the summary are the row counts, the duplicates removed and the written notes.

Step 5: Export safely — the formula-injection check

The fixture's Note column contained the value =2+5, typed into the orders data as a test. In a CSV file that is inert text. Opened in a spreadsheet, a cell beginning with = is evaluated as a formula — and OWASP's CSV injection guidance explains how crafted cells beginning with =, +, - or @ can go further than arithmetic, pulling in external content or abusing the spreadsheet's features. Export is therefore a security step, not just a file-format step.

Method: we called the formulaSafeCell function exported by csv-studio.js directly on sample values. It returned '=2+5 for the input =2+5 and '@SUM(A1:A9) for @SUM(A1:A9) — an apostrophe prefixed, which makes a spreadsheet display the text literally instead of executing it — and returned web unchanged. It also prefixed -10 to '-10, a deliberate trade-off the function makes: a leading minus is on OWASP's list of formula starters, so a genuine negative number exported this way arrives as text and must be converted back if you need it numeric. Knowing that trade-off exists is part of exporting safely; discovering it in a finance file afterwards is not.

Quote and delimit the export consistently (quote fields that contain the delimiter, a quote or a line break, and double internal quotes), keep the delimiter your recipient expects, and save as UTF-8. Then, before you analyse anything, read the cleaned file back in and check the row count and a few known values against your change summary. The natural next step for a cleaned orders file like this one is aggregation — How to Turn CSV Data Into Useful Charts continues with this exact fixture, and How to Choose the Right Chart for Your Data covers what to do once the numbers are grouped.

Frequently asked questions

Why does my CSV open as one wide column in a spreadsheet?

The file's delimiter is not the comma your spreadsheet assumed — semicolon-delimited exports are common from European locales. Tell the import wizard the actual delimiter, or convert the file in the CSV Cleaning Studio, which detects the delimiter automatically; our test fixture was detected as semicolon-delimited with no parse errors.

What order should I clean in, and why does it matter?

Parse first (delimiter, encoding, headers), then trim and standardise case and dates, then handle missing values and duplicates, then export safely. Reversing the middle steps hides problems: duplicates that differ only by spaces, capitalisation or date format will not match until those differences are removed.

Is 05/01/2024 the 5th of January or the 1st of May?

It depends on the source, which is the problem. The CSV Cleaning Studio reads ambiguous slash dates day-first, so it produced 2024-01-05 for that value in our test. If your file came from a US system, the same characters mean May 1st. Check one date whose meaning you know before standardising a whole column.

Does trimming and title-casing change my data?

Trimming removes leading and trailing spaces, which are almost never meaningful in exported data. Case standardisation does change the values — that is its purpose, so that london and LONDON group together — but it will also rewrite values where case carries meaning, such as codes and passwords. The studio applies case changes to columns it detects as text, and you choose the columns and the mode before applying anything.

Why did a negative number gain an apostrophe in my export?

Because the safe-export rule prefixes any cell whose first character is =, +, - or @, following OWASP's list of possible formula starters. A negative number is collateral: it arrives in the spreadsheet as the text -10 rather than the number. If the column must stay numeric, convert it back in the spreadsheet, and document that you did.

References

Related reading: Chart Builder.