Double-click a CSV and Excel guesses every column type. That guesswork is what causes csv opens in one column excel, strips leading zeros, converts long identifiers to scientific notation, and silently rewrites dates. Use Excel’s built-in import tool instead. It hands you control over delimiters, encoding, and column types before a single row loads. This guide walks you through the silent data loss and how to stop it.
What Happens When You Double-Click a CSV
Automatic Formatting Damage
Excel treats a double-clicked CSV as a new workbook and applies automatic formatting. It will:
- Drop leading zeros. 00123 becomes 123.
- Convert text that looks like a date, such as 05/04, into a date value.
- Display long identifiers (credit card digits, product codes) in scientific notation and truncate everything after the 15th significant digit.
- Stack every field in column A if your system uses a semicolon as the list separator, common in many European locales, while the file uses commas.
These changes happen silently. You might not catch them until you check the data later. The original CSV stays untouched, but the view inside Excel is altered.
The Correct Way to Import a CSV Into Excel
Use the Import Tool, Not a Double-Click
Go to the Data tab and click From Text/CSV (or Get Data > From Text/CSV in newer versions). This gives you full control. Here are the steps:
- Select your CSV file. A preview window appears.
- Set the File Origin to the correct encoding. Pick UTF-8 for files with non-English characters.
- Choose the Delimiter: comma, semicolon, or a custom character.
- For each column, click the drop-down arrow and set the data type. Use Text for IDs and any value with leading zeros. Use Number for numeric values. Use Date for dates.
- Click Load.
This method preserves leading zeros, prevents date conversion, and keeps long identifiers intact.
Common CSV Import Problems and Fixes
CSV Opens in One Column
If every field lands in column A, the delimiter is wrong. Excel defaults to your system’s list separator. In many European locales that separator is a semicolon. When your file uses commas, change the delimiter in the import preview to a comma. Or save the CSV with semicolons to match your system.
Leading Zeros Disappear
Set the column type to Text in the import wizard before loading. That keeps the zeros.
Long Identifiers Switch to Scientific Notation
Excel stores a limited number of significant digits. Anything longer loses the trailing digits and may display in scientific notation. Import the column as Text to preserve the full value as a string.
Unwanted Date Conversion
Excel may interpret text like 01/02 as a date. Set the column type to Text in the import wizard to keep the original format.
Handling Special Characters and UTF-8 Encoding
If your CSV contains non-English characters, set File Origin to UTF-8 in the import preview. When saving a file from Excel, choose CSV UTF-8 (Comma delimited) to preserve those characters. Without the right encoding, accented letters and symbols turn into garbled text.
Limits to Know When Working with CSV Files
Worksheet Boundaries That Cut Your Data
Excel worksheets enforce hard limits on CSV imports:
- Maximum rows: 1,048,576. Rows beyond this are not loaded.
- Maximum columns: 16,384. Extra columns are ignored.
- Maximum characters per cell: 32,767. Longer text is truncated.
If your CSV exceeds these limits, split the file into smaller parts or review the data in a text editor first.
What You Lose When Saving a Workbook as CSV
Saving an Excel workbook as CSV keeps only the active sheet’s values. Formulas, formatting, multiple sheets, and charts are all discarded. The CSV format stores plain text and nothing else. If you need to preserve formulas or formatting, save in Excel format (.xlsx) instead.
A Quick Alternative for Recovery
If you already have a corrupted Excel file or a CSV that opened incorrectly, upload it to an online repair tool like xls.repair. It recovers corrupted spreadsheets free, with no watermark. Download a repaired copy straight away.