Eight things that are actually wrong with your messy CSV

The signature is unmistakable. You open the file, every record occupies column A, and you can see the commas or semicolons inside the cell.

The file is fine. Your reader guessed the wrong separator. The usual cause is a European export: in locales where the comma is the decimal separator, exports use a semicolon instead, and a reader configured for commas finds nothing to split on. Tab-separated files saved with a .csv extension do the same thing.

Open the file in a plain text editor and look at the first line: whatever sits between the field names is your delimiter.

Then stop double-clicking the file. In Excel, use Data → From Text/CSV, which shows a preview with a delimiter dropdown you can change before anything is committed. In Google Sheets, use File → Import, then Custom under separator type. Both show you the result before you accept it. Double-clicking does not.

There is a longer version of this diagnosis in why my CSV opens in one column, including the case where the file has a mix of delimiters because two exports were concatenated.

That exact pattern is the tell. é where é should be. ’ where a curly apostrophe should be. £ sitting in front of every pound sign. It means the file is UTF-8 and something read it as Windows-1252 or Latin-1.

Re-read the file, don't repair it. Excel's Data → From Text/CSV has a File Origin dropdown: set it to 65001: Unicode (UTF-8). VS Code shows the current encoding in the status bar and offers Reopen with Encoding. Google Sheets assumes UTF-8 and usually gets this right on import.

Find-and-replace is a trap here. The mapping back is not one-to-one, so you will fix the four sequences you noticed and leave the rest. Go back to the source file.

One thing you cannot fix: if you see ` (the replacement character) rather than é`, the original bytes have already been discarded by whatever wrote the file. No amount of re-importing brings them back. Re-export.

Spreadsheets right-align numbers and left-align text by default. A column of figures hugging the left edge of the cell is text that looks like a number. The confirming test is =SUM() on the column: if it returns zero, or a total that is obviously too small, some or all of the values are not numeric.

The causes are boring and specific: a thousands separator the locale doesn't expect, a currency symbol inside the value, a trailing space, a negative written as (1,200) in accounting brackets, a footnote marker, or a non-breaking space that came in from a web page.

The fastest fix in Excel is select the column → Data → Text to Columns → Finish. You change nothing in the wizard; re-parsing the column is what coerces the values. Verify with =ISNUMBER(A2) in a helper column. Anything returning FALSE still needs work.

Watch for the non-breaking space specifically. TRIM() does not remove it, because it is character 160, not character 32. Use =SUBSTITUTE(A2,CHAR(160),"") first, then TRIM(). This one wastes more time than the rest of the list combined, and it is covered in numbers stored as text.

The signature is a column of identifiers or postcodes where some values are shorter than others. Run =LEN(A2) down the column. If it should be a fixed width and it isn't, zeros have been stripped.

This happens at read time, not in the file. The CSV contains 01234; the reader decided it was the number 1234 and discarded the zero. Excel does this on double-click without warning.

Prevent it on import. In Excel's From Text/CSV preview, click Transform Data, select the column, and set its type to Text before loading. In Google Sheets, File → Import has a setting called Convert text to numbers, dates and formulas. Set it to No.

If the damage is already done and you know the correct width, =TEXT(A2,"00000") will pad it back. If you do not know the width, as with mixed-length account numbers, the information is genuinely gone and no formula will recover it. Re-export the file with the column quoted or typed as text. This is one of two problems on this list you can lose data to permanently.

Dates are the most expensive item on this list, because the failure is silent and the result is still a valid date.

The signature: sort the column ascending and check whether the order is actually chronological. A second test is =ISNUMBER(A2) down the column. Mixed TRUE and FALSE means the parser confidently converted the rows it could read as MM/DD and gave up on the rows where the day exceeded 12. Those rows are now text sitting among real dates, and every one below day 13 has been silently reversed.

The fix is to state the input order explicitly rather than let anything guess. Import the column as Text, then use Data → Text to Columns, and on the third step choose Date with the correct DMY or MDY order from the dropdown. That dropdown is the only place Excel lets you declare which convention the source used.

If the file has no row with a day above 12 anywhere, there is nothing in the data that distinguishes the two readings. You have to ask whoever produced it. How to fix dates in a spreadsheet covers the mixed-format case and the two-digit-year case as well.

Count the lines in the file and compare with the number of records you believe it holds. On macOS or Linux, wc -l file.csv. If the line count is higher, you have line breaks inside quoted fields, almost always in a comments or address column where somebody pressed Enter.

That is legal CSV. A reader that honours quoting handles it correctly. A reader that splits on newline does not, and the result is a set of half-rows: records where the last few columns are empty, followed by a row that starts mid-sentence with everything shifted left.

Use a reader that follows the quoting rules: Excel's From Text/CSV import, Sheets import and Python's csv module all do. If a naive tool has already mangled the file, the split rows cannot be reliably rejoined, so go back to the export. To prevent it next time, strip the breaks at source, or in Excel with =SUBSTITUTE(A2,CHAR(10)," "). More on what the format does and does not guarantee under CSV as a file type.

Acme Ltd, Acme Ltd and ACME LTD are three customers to a pivot table and one customer to you. The signature is a distinct count that is higher than you know the true number to be, or a grouped report with near-identical rows sitting next to each other.

Test it directly. Add a helper column of =TRIM(LOWER(A2)), count the distinct values in that, and compare with the distinct count of the raw column. The gap is your phantom duplicate count.

To clean: TRIM() for ordinary spaces, SUBSTITUTE(A2,CHAR(160),"") for non-breaking ones, CLEAN() for non-printing characters, and LOWER() for a canonical case. Keep the original column; the normalised one is only for matching, and you will want the real spelling back when you present the result. How to find duplicates in Excel goes further into near-matches that differ by more than whitespace.

Exports built for humans rather than machines put a report title in row 1, a blank in row 2, a group label spanning several columns in row 3, and the real field names in row 4. Merged cells do not survive the trip to CSV, so the group label lands in the first column of its group and the rest are empty strings.

Flatten it to one row. Carry the group label rightwards across its empty cells, then join it to the field name beneath: Q1 Revenue, Q1 Units, Q2 Revenue. Delete the title and blank rows, and do it before anything else touches the file, every tool downstream assumes row 1 is the header.

Check for repeated headers in the middle of the file too. Filter the first column for the value of the header itself; a concatenated export will show one hit per source file, and each of those rows will be counted as data by every total you compute afterwards.

When the file is small enough to look at, you are the only person who will use the result, and you will not have to do it again. At that size everything above is a twenty-minute job.

It stops being the right call in three situations. When the export recurs, because you will clean it slightly differently each month and the months will not be comparable. When the file is large enough that you cannot eyeball the result, which in practice starts somewhere around ten thousand rows. And when someone will ask you to defend a number, because a manual clean leaves no record of what you changed, which rows you dropped, or why.

That last one is the real threshold. Not file size. Whether you will need to show your working.

Data Analysis App reads the file as-is and returns the cleaned version along with an account of what it changed. The mechanism is worth knowing before you trust it: a language model may decide what to compute, but it never decides what the answer is. Every figure is computed twice by two independent engines, and anything the two disagree about is dropped rather than published. If you only want the cleaning step, clean Excel data is the narrower tool.