How to Fix CSV Delimiter and Encoding Problems on Windows

A CSV file can look “broken” for several different reasons. Sometimes the delimiter is wrong. Sometimes the text encoding is wrong. Sometimes the file is structurally inconsistent. These problems can look similar in a spreadsheet, but they need different fixes.

The safest workflow is to inspect the source first, decide how it should be interpreted, and export a new reviewed copy rather than editing the original blindly. CSV Doctor is built around that approach and works locally on Windows.

First identify what is actually wrong

A file with all values in one column often points to a delimiter mismatch. A file with strange characters such as Ã, Â, replacement symbols or broken accents often points to an encoding mismatch. A file where some rows have more columns than others can indicate quoting or structural problems.

Do not treat all three as “CSV corruption.” The same visible symptom can have different causes.

Common separators include:

• comma ,
• semicolon ;
• tab
• pipe |

The file extension does not guarantee which one is used. A .csv created in one regional environment may use semicolons while another uses commas.

Check the delimiter before changing anything

Open the file in a text-aware tool or inspect it in CSV Doctor. Look at several rows, not just the header.

If a row looks like this:

name;city;postal_code

then semicolon is the likely delimiter. If it looks like:

name,city,postal_code

then comma is a likely candidate.

But quoting complicates this. A comma inside a quoted field is data, not a separator. For example:

"Smith, John",London,SW1A

A parser must understand quoting rules rather than simply splitting every line on commas.

CSV Doctor detects common delimiters and surfaces ambiguity instead of silently guessing when the structure is unclear. If more than one interpretation seems plausible, confirm the source format before exporting.

Distinguish delimiter problems from encoding problems

Encoding controls how bytes become characters. Delimiters control where fields end. Fixing one does not fix the other.

Typical encodings you may encounter include:

• UTF-8
• UTF-8 with BOM
• UTF-16
• Windows-1252

If accented characters display incorrectly, the delimiter may still be correct. Likewise, a file can use UTF-8 perfectly while Excel still places everything in one column because it expected a different separator.

CSV Doctor can identify several common encodings and lets you review the detected interpretation. Treat detection as evidence to review, not magic. If the source system documents its export encoding, prefer that information.

Why opening directly in Excel can hide the cause

Double-clicking a CSV asks Excel to make several decisions automatically. Those decisions can depend on regional settings and the content of the file.

That means a file can be structurally valid but still open badly because Excel chooses the wrong delimiter or reinterprets values. It can also make troubleshooting harder because the spreadsheet view hides the raw separators and quoting.

For a problematic file, inspect it before the first spreadsheet import. If you need Excel, use an import workflow that lets you choose the delimiter and column types rather than relying on a double-click.

Check for inconsistent rows

A correct delimiter does not guarantee a healthy file. One malformed row can create shifted columns for every value after the error.

Look for:

• rows with too many or too few fields;
• unclosed quoted fields;
• quotes used inconsistently;
• blank rows in unexpected places;
• empty or duplicate headers;
• line breaks embedded inside quoted fields;
• mixed delimiter styles from concatenated exports.

CSV Doctor reports inconsistent column counts and other structural findings. Review the source row before applying any repair. A tool should not silently delete data just to force every row into the same shape.

Work on a copy and decide the target format

Before exporting, decide what you actually need the output to be.

For example:

• delimiter: comma or semicolon;
• encoding: UTF-8 or UTF-8 with BOM;
• line endings: CRLF or LF;
• handling of formula-risk cells;
• whether identifier-like values should remain text.

CSV Doctor exports to a new destination and creates an adjacent JSON report describing findings and applied changes. Existing destination files are refused rather than overwritten silently.

That makes the transformation reviewable. You can compare the source with the proposed result before saving.

Example: semicolon CSV opens in one Excel column

Suppose the source is:

id;name;city

001;Ana;Bucuresti

002;Mihai;Focsani

The file may be completely valid, but a spreadsheet expecting commas can display each full line in one column.

The fix is not to replace every semicolon manually without inspection. First confirm that semicolons are truly delimiters and not ordinary data inside quoted fields. Then import with semicolon selected or export a reviewed copy using the target delimiter you need.

Also protect values such as 001. A spreadsheet can change them to 1 if it decides the column is numeric.

Example: accents display incorrectly

Suppose București appears as garbled text. That points to encoding interpretation rather than delimiter selection.

Determine the source encoding, then decode and export using an encoding appropriate for the receiving application. UTF-8 is usually a practical target, but the receiving workflow still needs to interpret it correctly.

Do not repeatedly open and save the damaged-looking version. If the application already decoded the bytes incorrectly and then saved them, you can make recovery harder.

Return to the untouched source when possible.

Be careful with automatic “repair”

A CSV repair can be destructive if the software guesses wrong. Automatically deleting rows, merging columns or stripping quotes can turn an ambiguous source into a clean-looking but incorrect table.

Prefer repairs that are explicit and previewable. CSV Doctor blocks export when parsing remains ambiguous and asks for an explicit interpretation rather than fabricating a confident result.

The original remains unchanged.

Reopen the exported copy

After saving a repaired copy:

1. reopen the output in CSV Doctor or another parser;
2. confirm the expected row and column counts;
3. inspect several known values;
4. check accents and other non-ASCII characters;
5. check identifiers, dates and long numbers;
6. import into the target spreadsheet using deliberate column-type choices when necessary.

A successful export only proves that a file was written. Verification proves that it still represents the data you intended.

Know the limits

CSV Doctor supports common CSV, TSV and delimited text workflows within documented size and column limits. It does not make Excel stop reinterpreting data after import. It also cannot infer the business meaning of every column.

If a field is an identifier, postal code, product code or account-like value, treat it as text even if it contains only digits. If a malformed row is genuinely ambiguous, confirm it against the source system rather than guessing.

The best CSV repair workflow is therefore: preserve the source, identify delimiter and encoding separately, inspect structural findings, preview the proposed transformation, export a new copy, and reopen it before using it downstream.