BlueprintLab Deep Guide
CSV Complete Guide — Encoding, delimiters, quoting, and safe Excel workflows
CSV is not a lightweight Excel file. Separate structure, encoding, and application interpretation, and most CSV problems become much easier to diagnose.
You open one CSV and the Japanese text turns into garbage.
Another file is readable, but every field lands in column A.
A third file looks perfectly normal—until you notice that 00123 has quietly become 123.
They all feel like “CSV problems,” but they are not the same problem.
Here’s the useful way to split them up:
- Structure — where rows and fields begin and end
- Encoding — how bytes become characters
- Interpretation — what Excel or another app decides a value means
Once you separate those three layers, CSV troubleshooting becomes much less mysterious.
Start by looking at a CSV as text, not as a spreadsheet
Excel makes CSV files look like spreadsheets. The file itself is much simpler.
product_id,name,price
00123,Blue mug,1980
00124,"Large, red mug",2480
There are characters, separators, and line breaks.
That simplicity is one of CSV’s strengths, but it also explains a lot of the trouble.
A CSV file does not normally carry rich type information saying “this is a product ID” or “this is a date.” It just contains text such as 00123.
The app that opens the file decides what that text means.
That is the first “aha” moment with CSV: the file can preserve a value while the program opening it changes the meaning.
Readable text but everything is in one column? Check the delimiter first
If the characters look fine but the whole row appears in column A, changing UTF-8 to Shift_JIS probably will not help.
The reader may simply be looking for the wrong separator.
A typical comma-separated file looks like this:
name,age,city
Alice,35,Tokyo
But real systems also use tabs and semicolons:
name;age;city
Alice;35;Tokyo
If Excel expects commas and the file uses semicolons, there is nothing for Excel to split on. One line becomes one cell.
In Excel, Data → From Text/CSV is useful because you can change the delimiter in the preview before loading the data.
If only some rows shift, look at quoting—not just commas
Suppose a product name contains a comma:
Large, Red Mug
This is ambiguous if you write it directly into a comma-separated file:
id,name,price
A001,Large, Red Mug,1980
A CSV parser sees an extra field.
The usual fix is to quote the entire value:
id,name,price
A001,"Large, Red Mug",1980
Now the comma belongs to the value instead of acting as a separator.
If the value itself contains a double quote, that quote is normally doubled:
id,note
A001,"Choose the ""Large"" size"
This looks a little odd to a human, but it is how the parser knows which quotes are data and which quotes define the field boundary.
A field can contain a newline and still be one field
Free-text notes and addresses sometimes contain line breaks.
id,note,status
A001,"Line one
Line two",ok
To a text editor, that looks like two physical lines. To a CSV parser that understands quoting, it can still be one record with one multi-line field.
This is why “one physical line equals one CSV row” is not always a safe assumption.
Mojibake is a different layer: the bytes may be fine, but the decoder is wrong
If the columns are in the right places but Japanese text is unreadable, look at encoding.
A file stores bytes. UTF-8 and Shift_JIS-family encodings define how those bytes map to characters.
If a file was written as UTF-8 but opened as Shift_JIS, the reader can turn valid bytes into the wrong characters.
The file can look broken without the underlying bytes having changed.
That is why rewriting the file should not be your first reaction. Try reading it correctly first.
UTF-8 vs Shift_JIS is not a “newer is always better” contest
UTF-8 is the default across much of the modern web, APIs, and JSON.
But older Japanese business systems still sometimes require Shift_JIS-family encodings.
The right answer is the encoding your receiving system expects.
A .csv extension does not tell you the encoding, so not knowing it from the filename is completely normal.
If the source or destination specification says UTF-8 with BOM, Shift_JIS, Windows-31J, or similar, follow that requirement. If the specification is missing, preview the file and test rather than guessing.
BOM has a funny name in UTF-8
A UTF-8 file can begin with the bytes EF BB BF. That is the UTF-8 representation of the BOM.
BOM means Byte Order Mark, but UTF-8 does not have the same byte-order problem as UTF-16 or UTF-32.
The Unicode Consortium explains that, for UTF-8, a BOM is used as an encoding signature—not to choose little-endian or big-endian order.
So “UTF-8” and “UTF-8 with BOM” are not two different character sets. The second one simply carries an extra signature at the beginning.
And more BOM is not automatically more compatible. Some consumers do not expect it. Follow the receiving system’s specification.
The quietest failures happen when Excel opens the file just fine
Mojibake is obvious. One-column imports are obvious.
Automatic conversion is not.
Imagine a product code stored as:
00123
If Excel treats it as a number, the value may become 123.
Mathematically that is fine. As an identifier, it can be wrong.
Long numeric IDs can also be treated as numbers, and date-like text can be interpreted as dates.
CSV did not tell Excel “this is a product ID.” Excel inferred a type from the shape of the text.
That is why a CSV that looks normal can be more dangerous than one that obviously looks broken.
For important CSV files, think “import” instead of “double-click”
Open Excel first, then use Data → From Text/CSV.
Before the data enters the sheet, check:
- encoding
- delimiter
- whether fields split correctly
- identifiers that must stay as text
- date-like values that should not be guessed
Current versions of Excel also expose Automatic Data Conversions settings for behaviors such as leading-zero removal, long numeric strings, E notation, and date-like conversions.
The key question is not “does this contain only digits?” It is “is this value meant to be calculated?”
A SKU, postal code, membership number, or order ID may be all digits and still be text.
RFC 4180 is useful as a baseline, not as a law of nature
CSV has a long history and many implementations.
RFC 4180 documents a common format and registers the text/csv media type. It is an Informational RFC, not a rule that every CSV producer must follow.
It gives you a practical baseline for things such as:
- comma-separated fields
- quoted values
- quoting fields that contain commas, line breaks, or quotes
- doubling quotes inside quoted fields
If a receiving system defines its own CSV rules, those rules win in practice.
If it does not, RFC 4180 gives everyone a useful common language.
A safe CSV workflow checks the file again after saving
The spreadsheet you are looking at is not the final artifact. The file you export is.
For important data, use this flow:
- Keep an untouched copy of the original CSV.
- Confirm encoding and delimiter.
- Identify IDs, codes, and ambiguous dates that must not be auto-converted.
- Import the file deliberately.
- Edit it.
- Export to a new CSV instead of overwriting the original.
- Inspect the exported CSV itself.
- Test a small import before sending thousands of rows.
That last check is easy to skip, and it catches a surprising number of problems.
Think of it as a round trip: did the data come back out with the same meaning it had when it went in?
Diagnose by symptom
Unreadable characters
→ Check encoding.
Readable text, but everything is in one column
→ Check the delimiter.
Only some rows shift
→ Check commas, embedded newlines, and quoting.
00123 becomes 123
→ Check Excel’s type inference and automatic conversions.
The file saves but the target system rejects it
→ Compare the exported file against the target schema: encoding, headers, field count, and critical values.
You do not need to memorize the whole CSV ecosystem.
Structure, encoding, interpretation. First identify which layer is failing.