How to Open a CSV File in Excel Without Formatting Issues

Double-clicking a CSV file in Windows often opens it in Excel with problems: leading zeros stripped from phone numbers and ZIP codes, dates reformatted, or special characters showing as garbled text. Here is how to import CSV correctly.

The Problem With Double-Clicking CSV Files

When you double-click a CSV, Excel opens it using auto-detection for data types. This causes:

• Leading zeros stripped — "01234" becomes "1234" (phone numbers, ZIP codes, product IDs)
• Dates reformatted — "12/11" becomes "Dec-11" or "12/11/2024"
• Encoding issues — UTF-8 characters appear as "???" or "é" (mojibake)

Fix: Use the Text Import Wizard

Excel 2010–2019:

1. Open Excel → Blank workbook
2. Data → From Text → browse to your CSV file
3. In the wizard, Step 1: set File Origin to 65001 : Unicode (UTF-8) (for international characters) → click Next
4. Step 2: Select Comma as the delimiter → click Next
5. Step 3: Click each column to set its data type:
   • For phone numbers, ZIP codes, IDs: select Text
   • For dates: select Date and set format (MDY, DMY, etc.)
6. Click Finish → specify where to place data

Excel 2016+:

1. Data → Get Data → From File → From Text/CSV
2. Change File Origin to Unicode (UTF-8)
3. Check the preview — columns should look correct
4. Click Transform Data to set column types manually
5. Click Close & Load

Prevent Leading Zeros From Being Dropped

For columns that should remain as text (phone numbers, codes, IDs):

• In the Text Import Wizard Step 3, click the column → select Text data type
• In Power Query: right-click the column header → Change Type → Text

Alternative: Open in Google Sheets

Google Sheets handles CSV encoding correctly without the Excel quirk:

1. Go to sheets.google.com → Blank spreadsheet
2. File → Import → upload your CSV
3. Set Separator type → Comma, Convert text to numbers... → No (to preserve leading zeros)