← Back to Error Directory

CSV Import Errors in Google Sheets — Complete Fix

Lost leading zeros, garbled characters, misaligned columns — CSV's simplicity is also exactly why it causes these problems.

Table of Contents

1. Why CSV Files Cause These Problems in the First Place

A CSV file is, by design, the simplest possible tabular format — plain text, values separated by commas, with absolutely no formatting metadata of any kind. There's no way for a CSV file to specify "this column should stay as text" or "this value has a leading zero that matters" — every one of these decisions is made after the fact, by whatever application imports the file, based on its own automatic pattern detection. This is the root cause of nearly every CSV import problem.

2. Interactive Demo: Leading Zero Loss Simulator

See exactly how automatic type detection silently strips meaningful leading zeros during import.

3. Cause 1: Leading Zeros Disappearing

This is by far the most common CSV import complaint, especially for ZIP codes, product IDs, or account numbers that happen to start with zero. Sheets' automatic type detection sees a column of digit-only values and converts them to true numbers by default — and numbers, mathematically, have no concept of a "leading zero" (007 and 7 are the same number), so it's silently dropped.

4. Cause 2: Character Encoding / Garbled Text

If imported text shows strange characters in place of accented letters, currency symbols, or other special characters, this almost always indicates a character encoding mismatch — the source CSV file was saved using a different text encoding (like Windows-1252 or ISO-8859-1) than the UTF-8 encoding Sheets expects by default. Re-saving the original CSV file with UTF-8 encoding explicitly selected (available in most text editors and export tools) before importing resolves this in the vast majority of cases.

5. Cause 3: Wrong Delimiter Detection

While "CSV" technically stands for comma-separated values, some regions and systems generate files using semicolons or tabs as the actual delimiter instead, particularly in locales where the comma is used as a decimal separator. If Sheets misdetects the delimiter, an entire row's worth of data can be crammed into a single column rather than properly separated. During import, explicitly select the correct separator character in the import dialog rather than relying entirely on automatic detection.

6. Cause 4: Dates Interpreted in the Wrong Format

A date written as "03/04/2026" is genuinely ambiguous without additional locale context — is it March 4th or April 3rd? Sheets applies its own locale assumption during import, which may not match the assumption the CSV's original source system used, silently swapping day and month for any date where this ambiguity is possible (any day number 12 or below). Review imported date columns carefully for swapped day/month values, especially when importing data generated in a different region than your Sheets file's own locale setting.

7. Cause 5: Large Numbers Converting to Scientific Notation

Very long numeric strings — like a 16-digit credit card number, a long barcode, or a large ID number — can be automatically converted to scientific notation (e.g., 1.23E+15) during import, losing the exact original digit sequence in the process, since scientific notation only preserves a limited number of significant digits by default. This is functionally similar to the leading-zeros problem and has the same underlying cause: automatic numeric type detection applied to a value that was actually meant to be treated as an identifier, not a mathematical quantity.

8. Using Import Settings Correctly

  1. Before importing, format the destination columns most at risk (ID numbers, ZIP codes, phone numbers) as Plain Text (select the columns, then Format → Number → Plain Text) if you're pasting data directly into an existing sheet.
  2. When using File → Import with a CSV file, review the import dialog's separator type setting explicitly rather than trusting "Detect automatically" for files from unfamiliar sources.
  3. For persistently problematic leading-zero or long-ID columns, consider prefixing each value with a single apostrophe directly within the source CSV file before import, which forces text interpretation regardless of the destination column's format setting.

9. Frequently Asked Questions

Why do leading zeros disappear when I import a CSV into Google Sheets?

Google Sheets automatically detects column data types during CSV import, and a column of numbers with leading zeros, like ZIP codes or ID numbers, gets converted to a true number, which drops leading zeros since they have no mathematical meaning in a numeric value.

Why does my imported CSV show garbled or strange characters?

This typically indicates a character encoding mismatch, most often when a CSV file was saved with an encoding other than UTF-8. Re-saving the source CSV file specifically as UTF-8 encoded before importing usually resolves garbled special characters and accented letters.

How do I import a CSV without losing leading zeros?

Format the destination column as Plain Text before pasting or importing the data, or prefix each value with an apostrophe in the source CSV to force text interpretation, since both approaches prevent Sheets from auto-converting the values to numbers.

Related Guides

Editorial Disclaimer: This guide is developed to the best of our domain knowledge and tested against Excel 2019+, Microsoft 365, and Google Sheets. Content is updated continuously as spreadsheet calculation engines evolve.