BlueprintLab Deep Guide
Why Excel changes CSV values — Leading zeros, long IDs, E notation, and dates
The most dangerous CSV is often the one that opens normally. Excel can make values look reasonable while silently changing identifiers and codes.
The CSV opens cleanly. No mojibake. The columns look right.
You make a small edit, save the file, and move on.
Later you discover that 00123 became 123, a long identifier changed at the end, or a code was interpreted as a date.
This is one of the harder CSV failures to notice because the sheet can look perfectly normal while the meaning of the data has changed.
Excel is not doing this to be malicious—it is trying to be helpful
CSV does not normally say “this column is a product ID” or “this column is text.”
Excel looks at the characters and infers a type.
123 looks numeric. A date-shaped value looks like a date.
That is helpful for calculations. It is risky for identifiers.
00123 and 123 are the same number, but not the same identifier
A SKU, postal code, account code, or membership number may contain only digits without being a number you ever want to calculate.
If Excel treats 00123 as numeric, the leading zeros can disappear.
You can sometimes make the cell look like 00123 again with formatting, but that is not the same as preserving the original text value.
For IDs, the safer goal is to keep the value as text from the start.
Long IDs can lose information, not just change how they look
Excel’s numeric precision is 15 digits.
If a 16+ digit identifier is imported as a number, digits beyond that precision may not survive exactly.
So a value such as:
123456789012345678
should not be treated as a large number just because every character is a digit.
If its business meaning is “identifier,” treat it as text.
Codes such as 123E5 can look like scientific notation
The risk is not limited to digit-only strings.
123E5 can look like scientific notation. Other letter/number combinations can look date-like to a spreadsheet.
Excel does not know whether a string is a laboratory label, SKU, gene name, or financial number. It only sees the shape of the text.
That is why “check numeric columns” is too narrow. Check code columns that resemble other data types as well.
Dates are dangerous when the source convention is ambiguous
Consider:
03/04/2026
Is that March 4 or April 3?
If the source convention is unknown, a successful date conversion does not prove that Excel chose the intended meaning.
For ambiguous dates, confirm the source format before normalizing anything.
Current Excel versions expose controls for automatic data conversion
Microsoft documents Automatic Data Conversions settings in current Excel releases, including controls for behaviors such as:
- removing leading zeros
- converting long digit strings
- interpreting strings containing
Eas scientific notation - converting letter/number patterns into dates
That is useful context: these are not vague “Excel quirks.” They are documented conversion behaviors with user-facing controls.
Feature availability depends on the Excel version, so check the version you are actually using.
For important files, import instead of relying on a double-click
Open Excel first and use Data → From Text/CSV.
The preview gives you a moment to inspect the data before it becomes worksheet values.
Look for:
- IDs and codes that must remain text
- long digit strings
- date-like text
E-like codes- delimiter and encoding issues
With Power Query, you can explicitly keep selected columns as text before loading.
Type profiling helps you find risky columns, but it does not decide business meaning
A profiler can tell you that a column looks like integers or dates.
That is useful for triage.
It does not mean you should convert the column to that type.
A thousand rows of 00123-style values may look numeric, but a column named sku is still an identifier.
A good rule is: use the shape of the data to find candidates; use business meaning to decide the type.
The most dangerous step is saving after an unnoticed conversion
If Excel converts a value and you overwrite the original CSV, the converted value can become your new “source of truth.”
Use a safer round trip:
- Keep the original CSV untouched.
- Import deliberately.
- Protect IDs, long numeric strings, and ambiguous dates.
- Edit.
- Export to a new CSV.
- Inspect the exported CSV itself.
- Test a small import.
The spreadsheet on screen is not what the next system receives. The exported file is.
Four values worth stopping for
00123
123456789012345678
123E5
03/04/2026
They represent four common failure patterns:
- leading-zero identifier
- long identifier
- code that resembles scientific notation
- ambiguous date
Checking one representative value from each risky column can save you from reviewing thousands of ordinary rows.