CSV File Problems (And How to Fix Every One)

Why CSV Survives Despite Everything

Monday morning. You export 12,000 customers from Shopify, open the file to sanity-check it, and the first row reads François Dupont. ZIP code 02134 is now 2134. A product code that used to say 1-15 now says 15-Jan. You haven't imported anything yet. You just opened the file.

This is the CSV experience. And yet the format is not going anywhere, because a CSV is just text. You can open one in Notepad. No license, no schema, no version negotiation. Every spreadsheet app, every database, every CRM, every analytics tool on earth can read and write one. It predates Excel by more than a decade and it will outlive most of the tools currently trying to replace it.

That universality is real value. When you need to move records out of one system and into another that has never heard of it, CSV is the common ground. It is the handshake format between things that share nothing else.

The cost is that CSV enforces nothing. There is no place in the file to say which encoding it uses, which delimiter separates fields, what type each column holds, or what a blank cell means. Every one of those things has to be guessed by whatever opens the file next. Most csv file problems are not really problems with your data. They are problems with guessing.

Encoding: UTF-8, BOM, and Mojibake

A text file is a string of numbers. An encoding is the lookup table that turns those numbers back into letters. UTF-8 is the modern standard and covers essentially every character in use. Older systems often default to Latin-1 or Windows-1252, which cover a few hundred.

When a file written in one encoding is read as another, you get mojibake: José becomes José, Müller becomes Müller, a curly apostrophe becomes ’, and the euro sign turns into a small pile of punctuation. The maddening part is that the file looks perfect on the machine that made it. Corruption only appears downstream, which is why the person who exported it will genuinely believe you.

There is a second, quieter version of this: the BOM. A byte order mark is three invisible bytes at the very start of a UTF-8 file that some Microsoft tools add to announce the encoding. Most readers strip it. Some do not, and the first column header silently becomes email instead of email. Your import then fails with "column 'email' not found" while you stare at a header that clearly says email.

The fixes, in order of how much control you have:

• Set the export encoding explicitly to UTF-8 wherever the source system lets you. Shopify, HubSpot, Klaviyo, Mailchimp and Salesforce all export UTF-8 by default, but a hand-rolled report from an internal database often does not.
• If you must open a file in Excel first, use Data, then Get Data, then From Text/CSV, and choose 65001: Unicode (UTF-8) in the encoding dropdown. Double-clicking the file skips that choice entirely.
• When you receive mojibake and cannot re-export, do not find-and-replace your way out. é is not one bad character, it is a symptom of a whole-file misread. Re-decode the file as a unit, or run it through a tool that detects the original encoding and converts it once, cleanly.
• If an import complains that a column it can see does not exist, suspect a BOM before you suspect anything else. Save the file as UTF-8 without BOM and try again.

Delimiters, Quoting, and Embedded Newlines

Comma-separated values, right up until your data contains a comma. "123 Main Street, Suite 400" is one address and two fields, depending on who is reading. The standard answer is quoting: wrap any field containing the delimiter in double quotes. The standard answer for a field containing a double quote is to double it up (""). Plenty of export tools implement half of this and none of them tell you.

Here is what a shifted row looks like in practice. Correct: 4471,"123 Main Street, Suite 400",Boston,MA,02134. Broken: 4471,123 Main Street, Suite 400,Boston,MA,02134. The second version has six fields where the header has five, so the importer either rejects the row or slides everything one column left. Now Boston is your state and MA is your ZIP. Multiply by the 400 customers who have a suite number.

Then there is the delimiter itself. In much of Europe the comma is the decimal separator, so European exports commonly use semicolons: 1234;Müller;19,99. Feed that to an American importer expecting commas and every row arrives as a single giant field. Tab-separated files have the same issue in reverse, and a tab pasted into a text field from a web form will quietly split a row in two.

Embedded newlines are the sneakiest of the three. A multi-line shipping address or a product description with paragraph breaks is legal inside a quoted field. It is also the exact thing naive readers get wrong, turning one record into three malformed ones. If your row count is higher than your record count and the extra rows are mostly blank at the front, this is why.

• Quote every field on export, not just the risky ones. Slightly bigger files, dramatically fewer failures.
• Open a suspect file in a plain text editor before you open it in a spreadsheet. Look at the first three lines. You will see the real delimiter and the real quoting in about four seconds.
• If you control both ends and the data is full of commas, switch to tab-separated and say so in the filename. Explicit beats clever.
• Strip line breaks out of free-text fields at export time if the destination does not need them. A description field rarely loses meaning when its paragraph breaks become spaces.

Excel's Silent Edits: Dates, Leading Zeros, Scientific Notation

Excel is the most common thing that touches a CSV between export and import, and it is the most destructive. Not because it is buggy, but because it is confident. It reads each cell, decides what you probably meant, and saves that instead. The original string is not stored anywhere. There is no undo once the file is saved and closed.

Three flavors of damage, all of them common:

Dates. Product code 1-15 becomes 15-Jan, then 1/15/2025 when the column is widened. Gene names MARCH1 and SEPT2 become dates, a problem so pervasive that naming committees renamed actual human genes to escape it. Worse, a date that was already a date can be re-interpreted: 01/02/2025 is January 2nd in the US and February 1st nearly everywhere else, and once Excel picks a reading, both are indistinguishable in the saved file.

Leading zeros. ZIP code 02134 becomes 2134. A SKU of 0045-A survives because of the letter, but 004500 becomes 4500. Any identifier that is numeric-looking but not actually a number is at risk, which includes most ZIP codes, many account numbers, and nearly all phone numbers stored without punctuation.

Scientific notation. A 16-digit order ID like 4019283746501928 becomes 4.01928E+15. This one is not cosmetic. Excel stores 15 significant digits, so the trailing digit is genuinely gone. Reformat the cell as a number and you get 4019283746501920, which is a different order.

The fix on the way in is to never double-click the file. Use Data, then Get Data, then From Text/CSV, and in the preview set every identifier column to Text before loading. The fix on the way out is to stop routing CSVs through Excel at all when you are only moving data from A to B. If you are not editing the file, opening it in a spreadsheet adds risk and nothing else. And if you need to inspect it, a text editor shows you the truth.

Header and Column Drift Between Exports

This one has no visible symptom, which is what makes it expensive. Last quarter's export had 14 columns. This quarter's has 15, because someone added a custom field in HubSpot, and it landed in position 6 rather than at the end. Your import mapping still says column 6 is phone. It is now lifecycle_stage. Twelve thousand phone numbers just became the word "Customer".

Drift comes in several shapes. Columns get added or removed. They get renamed, usually cosmetically: Email Address becomes Email, or first_name becomes First Name. They get reordered. And units change without warning, so a price column that held dollars starts holding cents, or a weight column switches from pounds to grams. Nothing in the file announces any of it.

The defense is to map by header name, never by position, and to compare headers against the last known-good file before every import. If your process involves a person eyeballing the first row, that person will miss the fifteenth column at 4pm on a Friday, and it will not be their fault.

This is also where anomaly checks earn their keep. A price column whose average jumps 100x between exports is a unit change, not a very good quarter. In CleanSmart, LogicGuard flags exactly that kind of shift: values that are individually plausible but collectively wrong. It is the difference between catching a units bug in the file and catching it in a customer invoice.

• Keep a reference copy of the header row from your last successful import. A one-line text file is enough.
• Diff the new header against it. Same names, same count, same order, or you stop and look.
• Never let an importer silently accept a row with the wrong field count. Reject and report.
• Watch column-level statistics, not just individual cells: row count, distinct count, min, max, mean, and percent blank.

Build a Pre-Import Validation Pass

Almost every CSV disaster is recoverable if you catch it before the import commits. Almost none are recoverable afterward, because the destination system now has 12,000 wrong records mixed in with the right ones and no memory of which were which. So the whole game is a checkpoint between export and import.

Here is a validation pass that takes ten minutes and catches the overwhelming majority of failures. Run it every time, in this order, because each step assumes the previous one passed.

Ten minutes feels like a lot when you have a file and a deadline. It is roughly one percent of the time you will spend untangling a bad import, and it is the difference between fixing a file and fixing a database. If you want the full rule-by-rule version, including the specific checks to run per field type, our post on CSV validation rules goes deeper on each one.

• Open it in a text editor. Confirm the delimiter, the quoting style, and that the first line looks like a header and not like something.
• Check the encoding. Search the raw text for à and â€. Either one means the file was decoded wrong and needs re-decoding, not repairing.
• Count the fields per row. Every row should match the header count. Any that do not are quoting failures.
• Diff the header against your last good export. Names, count, order.
• Spot-check the identifier columns. ZIP codes still five characters? Order IDs still full-length integers rather than 4.01E+15? SKUs still SKUs and not dates?
• Look at date columns for both formats. If you see 03/04/2025 and 13/04/2025 in the same column, the file mixes US and international ordering and you cannot trust any of it until you know which rows came from where.
• Check completeness on required fields. An import that needs email will reject or, worse, half-create every record missing one.
• Look for duplicates before you import, not after. Two rows with the same email and different phone numbers is a decision you want to make deliberately.

When to Stop Using CSV

CSV is the right answer for flat, tabular, one-time data movement between systems that do not otherwise speak. It stops being the right answer in a few specific situations, and recognizing them saves a lot of pain.

Stop reaching for CSV when your data is not flat. Orders with line items, contacts with multiple addresses, anything with a one-to-many relationship inside it: you will end up either repeating the parent row per child (and creating duplicates on import) or cramming JSON into a single cell (and discovering that the JSON contains commas). Use two related files with a shared key, or use a format that handles nesting.

Stop when exact data types matter and the file has to survive multiple hops. CSV has no type information, so every reader re-guesses. Three hops means three chances to turn an ID into a float.

And stop when the movement is recurring rather than one-time. If you are exporting the same file from Shopify every Monday and hand-loading it into Klaviyo, the CSV is not the problem, the manual step is. That is what a direct connection is for. In CleanSmart, DataBridge connects Mailchimp, Shopify, Klaviyo, HubSpot and Salesforce directly, so the export-clean-import loop stops being a file you babysit.

For everything else, which is most things, CSV is fine. It just needs a checkpoint.

What a Clean Handoff Actually Looks Like

The goal is not a perfect file. It is a file you can defend. You know its encoding, you know its delimiter, you know the header matches what the destination expects, and you know the identifier columns still contain identifiers.

This is what CleanSmart does to a CSV before it ever reaches your CRM. AutoFormat standardizes the messy columns: dates to one unambiguous format, states from 'CA', 'Calif', 'California' and one brave 'Cali' down to a single value, phone numbers to one shape. SmartMatch finds the duplicates that survived three previous exports, including the ones spelled slightly differently. SmartFill closes the gaps it can close from what is already in your data. LogicGuard flags the values that look wrong at the column level, like the unit change nobody announced.

Then you get a Clarity Score, broken into Completeness, Consistency, Uniqueness, and Validity, so you can see which of the four is actually holding the file back. It is recalculated after each cleaning run, and a run takes a few minutes, so you clean, you look, you decide whether to clean again.

You do not have to take any of that on faith. Upload a CSV and see what we catch, free, in the self-serve demo. Use a real file, ideally the ugly one you have been avoiding. That is the one with something to find.

Frequently Asked Questions

How can I tell if my CSV has an encoding problem?

Search the raw text for the sequences à and â€. If you find them, the file was written in UTF-8 and read as something else, which is why José shows up as José and a curly apostrophe shows up as ’. Fix it by re-decoding the whole file as UTF-8 rather than replacing the bad characters individually.

Can I stop Excel from reformatting my product codes and ZIP codes?

Yes, but only if you import the file instead of double-clicking it. Go to Data, then Get Data, then From Text/CSV, and in the preview set every identifier column to Text before loading, which stops Excel from reading 1-15 as a date or dropping the leading zero from 02134. If you double-click the file, the guessing happens instantly and the original values are gone once you save.

When should I use something other than CSV?

When your data has nested or one-to-many structure, when exact data types have to survive several hops between systems, or when the same transfer happens on a schedule. For a one-time move of flat, tabular data between two tools that share nothing else, CSV is still the most practical option available.