The Problem Is Upstream
When a client sends you a CSV, that file didn't emerge from a vacuum. It was probably exported from some system — a CRM, an accounting tool, an old database, a combination of all three — by someone who may not have understood the export settings.
Data quality problems in CSV files aren't random. They're predictable, and they come from predictable sources.
The Most Common Pre-Opening Problems
Encoding issues: The file might be UTF-16 instead of UTF-8, or it might have a BOM (byte order mark) at the start. When you open it, characters like é, ñ, ü will appear as garbage: "café" instead of "café". This is invisible until you look.
Inconsistent line endings: Windows uses CRLF (carriage return + line feed). Mac and Linux use LF. Some exports mix them. Tools that expect one will choke on the other.
Quoted fields with unescaped commas: If a field value contains a comma and wasn't properly quoted, the CSV parser will split it into multiple columns. "Smith, John" becomes two fields: "Smith" and " John".
Upload your CSV and see exactly what's wrong — Sohovi profiles quality in seconds — try Sohovi free.
Trailing whitespace: Spaces after values that are invisible in a spreadsheet but cause VLOOKUP and join operations to fail silently. "Smith " ≠ "Smith".
Headers with special characters: Column names like "#ID" or "Amount ($)" may cause import errors in databases and data tools that don't expect special characters in field names.
What to Do the Moment the File Lands
1. Open in a text editor first Before Excel or Sheets, open the CSV in a text editor (Notepad++, VS Code, TextEdit). You'll see the raw data — encoding issues, line ending problems, and header issues are immediately visible.
2. Check the encoding Your text editor will show the file encoding in the status bar. UTF-8 is correct. If it shows UTF-16, ANSI, or Latin-1, convert it to UTF-8 before proceeding.
3. Count the columns in the header vs. a data row In the raw text, count the commas in the first row (header) and the second row (first data row). If they're different, something's wrong — often a quoted comma or a missing quote.
4. Search for trailing spaces A quick find for " ," (space then comma) reveals trailing whitespace in many files.
5. Check for line breaks within fields A cell value containing a newline character will split what should be one row into two rows. This is especially common in address fields. In the raw text, you'll see a row that starts mid-sentence.
Setting Client Expectations
When you find pre-opening issues, document them and reach out immediately:
"I received your file. Before I start work, I wanted to flag a few things I found in the raw data that could affect accuracy…"
Sohovi scores your dataset against your own accuracy standards and highlights the columns and rows where values fall outside expected ranges.
This conversation is far easier to have at the start of a project than at the end, when you're trying to explain why the analysis produced unexpected results.
