iTechGuides is reader-supported. When you buy through links on our site, we may earn an affiliate commission. As an Amazon Associate I earn from qualifying purchases. Learn more
A CSV can be valid text and still look different in Excel: characters may display incorrectly, identifiers may lose leading zeros, dates may be read according to an unexpected locale, and timestamps may be assigned a type you did not intend. The safest approach is to import the file with explicit encoding, locale, and column types rather than relying on a double-click to preserve meaning.
Why Excel can change what a CSV appears to contain
CSV stores rows and fields as text; it does not reliably carry spreadsheet column types. When Excel or Power Query opens a file, it may infer that values are dates or numbers and convert them. Encoding recognition is a separate issue: a file can be encoded as UTF-8 yet not be identified as UTF-8 by Excel’s direct-open route.
Keep three different concerns distinct: encoding determines how bytes represent characters; CSV quoting determines where fields begin and end; type inference determines whether Excel treats a field as text, a number, a date, or another type. Fixing one does not automatically fix the others.
The Tool Desk
Outbyte Driver Updater FREEScan for outdated or missing drivers - takes under a minuteDriver Scan →Outbyte PC Repair FREERepair Windows errors before they cause bigger problemsFix Now →How to open a UTF-8 CSV without guessing its encoding
Microsoft says, “You can open a CSV file encoded with UTF-8 normally if it was saved with BOM (Byte Order Mark).” A BOM can act as a compatibility signal for Excel’s direct-opening behavior; UTF-8 itself does not require one. Microsoft’s guidance for a UTF-8 CSV without a BOM is to import it rather than assume a double-click will identify the encoding correctly. Microsoft’s Excel guidance
#1 Best Overall
- Used Book in Good Condition
Use Power Query when you need control
- In Excel, choose Data > Get Data > From File > From Text/CSV. The exact labels or availability can vary by Excel version.
- In the import preview, check the detected file origin or encoding and confirm that the characters display as intended.
- Inspect the inferred column types before loading. Set columns that must remain unchanged to text, and assign deliberate types to columns that should be interpreted as dates or numbers.
- Load the data only after checking the preview and any locale-sensitive values.
Use the Text Import Wizard where available
Microsoft also documents Get Data From Text, which opens the Text Import Wizard. This is another import route for a UTF-8 CSV without a BOM; its availability and interface depend on the Excel version. Use the wizard’s import controls to check the character set and treat identifier columns as text rather than accepting assumptions made during direct opening.
Adding a BOM may help Excel recognize UTF-8 during direct opening, but it does not repair bad CSV quoting, convert a file from another encoding to UTF-8, or guarantee identical results in every application. If the CSV is intended for users of varied software, an explicit import workflow gives them a way to choose settings instead of relying on the BOM alone.
Rank #2
How to prevent identifiers and numbers from changing
Power Query can infer types from values in unstructured sources such as CSV, and type detection is enabled by default. Excel also has automatic conversions that can remove leading zeros, retain only the first 15 digits of long numbers, interpret digits around “E” as scientific notation, or convert some letter-and-number strings to dates. Microsoft documents settings to disable these conversions individually or globally for data loading; the precise controls may vary by version. Microsoft’s guidance on automatic data conversions
Do these 3 things before closing this tab:
1Repair Windows errors before they cause bigger problems2Fix the driver behind crashes, sound loss and screen glitches3Clear out junk files and repair common Windows errorsImport labels as text
When digits are identifiers rather than quantities, preserve them as text. Examples include account codes, postal codes, and long IDs. A value such as 001234 may be a label whose leading zeros matter, not a number to calculate with. Likewise, a long numeric-looking ID should not be imported as a number if every digit must remain intact.
For exact source preservation, import sensitive columns as text first, then convert only the columns whose meaning and required format are clear. Check the preview and the loaded result: changing the display format after Excel has already converted a value cannot restore digits or zeros that were lost.
How locale affects ambiguous dates and values
Locale changes interpretation, not just how an already-understood value is displayed. For example, 03/04/2026 can mean March 4 or 3 April depending on the date convention. Microsoft notes that Power Query’s locale can affect text, numeric, and date/time values. In Power Query, the precedence is the Change Type setting first, then the Power Query locale, then the operating-system locale. A new workbook’s queries use the operating-system locale by default, while workbook settings and a per-operation Using Locale choice can specify another locale. Microsoft’s Power Query locale guidance
Rank #4
- In the Power Query Editor, select the column to interpret.
- Choose Change Type > Using Locale when the column needs a particular regional interpretation.
- Select the intended data type and locale, then confirm the preview matches the source’s meaning.
For dates whose meaning is ambiguous, choose a locale or an explicit conversion rather than assuming the recipient’s regional settings will match yours.
How to handle timestamps that include a timezone offset
A timestamp with an offset can identify an instant, but the offset is meaningful only if it is preserved or interpreted deliberately. ISO’s overview of date and time representation describes an ordered form using year, month, day, hour, minutes, seconds, and milliseconds, including local time with an offset to UTC. ISO’s date and time overview
Power Query distinguishes Date/Time from Date/Time/TimeZone; Microsoft describes the latter as a UTC date/time with a timezone offset. Power Query’s automatic type detection also examines CSV values, so inspect both the inferred type and the resulting values instead of assuming an offset-bearing string remained untouched. Microsoft’s Power Query data types reference
Choose the type based on what the timestamp means
- If it represents a particular instant, use a consistent representation with an explicit offset and verify how the import route interprets it.
- If you need the exact original text preserved, retain a raw copy or import the timestamp column as text before converting it.
- If it represents a local wall-clock time rather than a particular instant, document that meaning separately; an offset should not be assumed where none is supplied.
Excel’s direct-open behavior is not established as identical for every version, platform, and timestamp format. Do not rely on a double-click to preserve or convert offsets in a particular way without checking the imported values.
Independent reader supportYour contribution helps us test, update, and keep practical guides available for everyone.What valid CSV quoting does—and does not—protect
RFC 4180 documents a common CSV format and registers the text/csv media type, while explicitly not defining an Internet standard. It describes records with comma-separated fields and double-quoted escaping for fields containing commas, quotation marks, or line breaks. RFC 4180
What’s actually slowing this PC down?
Pick the symptom - the matching free tool is one click away.
Correct quoting protects the structure of fields; it does not tell Excel that a field is an identifier, prevent date inference, or establish character encoding. A correctly quoted value can still be interpreted as a date or number after import.
What a CSV export keeps—and what it drops
Excel’s CSV export saves the text and values displayed in cells from the active worksheet. It does not preserve formatting, graphics, objects, or other workbook features. If formulas, multiple sheets, formatting, or other Excel-specific features matter, retain a workbook copy as well as the CSV export. Microsoft’s guidance on saving a workbook as text or CSV
Quick Recap
Product prices and availability are accurate as of the date/time indicated and are subject to change. Any price and availability information displayed on Amazon at the time of purchase will apply.
