CSV files are deceptively simple. They look like just text with commas. But they break imports, trigger system errors, and corrupt data in predictable ways — the same errors, over and over, in file after file. Here's the definitive list.
Error 1: Delimiter Confusion
What it is: A CSV that uses semicolons, tabs, or pipes as delimiters instead of commas. Or a CSV where values contain commas that weren't properly quoted, causing fields to be split incorrectly.
How to spot it: Open the file in a text editor. Look for lines that appear to have the wrong number of columns.
How to fix it: In Excel, use Data > Text to Columns and specify the correct delimiter. Or open with a tool that lets you specify the delimiter before parsing.
Error 2: Encoding Issues
What it is: Special characters (accented letters, currency symbols, quotes, em dashes) that display as garbled characters (é, â€, ’). Usually caused by a UTF-8 vs. Latin-1 encoding mismatch.
Sohovi profiles your datasets for quality issues in minutes — see what's broken before it breaks your pipeline — try Sohovi free.
How to spot it: Open the file in a text editor and look for unexpected character sequences.
How to fix it: Save or convert the file explicitly to UTF-8 encoding. Most modern tools handle UTF-8 correctly.
Error 3: Extra Rows at the Top or Bottom
What it is: Summary rows, report headers, or blank rows at the top or bottom of the file that aren't data — but get imported as if they are.
How to spot it: Open the file and check whether the first row is truly the column header and the last row is truly the last data row.
How to fix it: Delete non-data rows before importing.
Sohovi validates your dataset before it enters the warehouse — catching format errors, nulls, and duplicates at the source.
Error 4: Mixed Date Formats
What it is: A date column where some rows use MM/DD/YYYY, some use DD/MM/YYYY, some use YYYY-MM-DD, and some use written-out dates. Most systems require consistent formatting.
How to spot it: Profile the date column and look for multiple distinct patterns.
How to fix it: Standardize all dates to one format (ISO 8601: YYYY-MM-DD is most universally compatible) before importing.
Error 5: Quoted Fields with Embedded Commas
What it is: A company name like "Smith, Jones & Associates" contains a comma. If it's not properly quoted in the CSV, the import treats it as two separate fields.
How to spot it: Rows with company names, descriptions, or free-text fields that contain commas will show an unexpected number of columns.
How to fix it: Ensure all fields containing commas are wrapped in double quotes. A properly formatted CSV handles this automatically.
Error 6: Trailing Spaces and Invisible Characters
What it is: Extra spaces at the end of values, invisible characters (zero-width spaces, byte order marks), or line endings that differ across operating systems.
How to spot it: Values that look identical but don't match in lookups or joins are often affected by trailing spaces.
How to fix it: Use TRIM() in Excel to remove leading/trailing spaces. Use a text editor with visible whitespace to identify invisible characters.
Sohovi detects many of these errors automatically when you upload a CSV — flagging encoding issues, format inconsistencies, and suspicious patterns before you import the file into any system.
