How to Open a CSV File in Excel Without Losing Numbers (5 Proven Methods)

You exported a CSV from Shopify, Salesforce, or your company's internal system. You double-clicked it. And now half your data is wrong — order IDs are missing their leading zeros, tracking numbers display as "2.35E+13", and someone's address got turned into a date.

This happens because Excel applies automatic type inference the moment it opens a CSV file. It scans your data, decides what "looks like" a number or date, and converts it — silently, irreversibly if you save.

Here are five ways to open CSV files in Excel without losing a single digit. Pick the one that fits your Excel version and workflow.

5 Ways to Open CSV Files in Excel Without Corrupting Your Data

Method 1 — Turn Off Automatic Data Conversion in Microsoft 365

This is the only method that fixes the problem once and never requires you to think about it again. Microsoft added these controls in a 2023 update, and they apply globally to every CSV you open afterward.

Steps:

1. Open Excel and go to File → Options
2. Click Data in the left sidebar
3. Under Automatic Data Conversion, uncheck all three boxes:
   • "Remove leading zeros and convert to a number"
   • "Keep the first 15 digits of long numbers and display in scientific notation"
   • "Convert consecutive letters and numbers to a date"
4. Click OK

That's it. From now on, double-clicking a CSV will open it with your data intact — no leading zeros stripped, no scientific notation, no surprise dates.

One caveat: slash-based patterns like "1/2" may still be interpreted as dates by Excel's core parser, even with these settings off. If your data contains those, use Method 2 instead.

Version check: If the Automatic Data Conversion section doesn't appear under Data in your Options dialog, your Excel version doesn't support this feature — use Method 2 or 3 instead. You can check your version under File → Account.

Method 2 — Import Through the Data Tab with Power Query

The most versatile approach, available in Excel 2016 and later. Instead of opening the CSV directly, you import it — which gives you control over how each column is interpreted.

Steps:

1. Open a blank workbook in Excel (do not open the CSV file)
2. Go to Data → From Text/CSV
3. Select your CSV file and click Import
4. A preview window appears. If you see mangled data, click Transform Data
5. In the Power Query Editor, right-click any column header that should stay as text → Change Type → Text
6. Click Close & Load

The key difference from double-clicking: Power Query doesn't apply type inference until you tell it to. You get a preview first, and you choose the types.

For files where every column should be treated as text, click the "Detect Data Type" button in the toolbar and change it to "Don't Detect Data Types". This imports the entire file as raw strings.

Method 3 — Rename .csv to .txt and Use the Text Import Wizard

If you're on an older version of Excel without Power Query, this method forces the classic Text Import Wizard to appear — which gives you the same column-level type control.

Steps:

1. Right-click the CSV file in File Explorer → Rename → change .csv to .txt
2. Open Excel, then File → Open → select the renamed .txt file
3. The Text Import Wizard launches automatically
4. Step 1: Select "Delimited" → click Next
5. Step 2: Check Comma as the delimiter → click Next
6. Step 3: Click on each column that contains IDs, phone numbers, or codes. Set the column data format to Text → click Finish

Tip: In Step 3, hold Ctrl and click multiple columns to set them all to Text at once. Target any column where values start with zero or exceed 11 digits.

Method 4 — Use an Online CSV-to-Excel Converter

When you can't change Excel settings (locked-down corporate laptop) or don't want to deal with import wizards, a browser-based converter handles the type-preservation for you.

Upload your CSV, download an .xlsx file. The output has each column's data type embedded in the file, so Excel doesn't need to guess.

What to look for in a converter:

• Browser-only processing — your data shouldn't be uploaded to a remote server. Verify that the tool processes files client-side before using it with sensitive data
• Encoding support — handles UTF-8, UTF-16, and Shift-JIS without garbled characters
• Large file handling — can process files with tens of thousands of rows

LeapRows (disclosure: built by the author) is one option that processes files client-side in the browser without uploading them to a server. Verify that any converter you use meets all three criteria above before routing sensitive data through it.

This approach is particularly useful for teams: instead of training everyone on Power Query, you can set a simple rule — "convert CSVs with this tool before opening in Excel."

Method 5 — Open in Google Sheets Instead

Google Sheets doesn't apply the same auto-conversion that Excel does. It preserves leading zeros and doesn't convert long numbers to scientific notation by default.

Steps:

1. Go to Google Sheets and create a new spreadsheet
2. File → Import → Upload → select your CSV
3. Your data appears with formatting intact
4. If you need the data in Excel: File → Download → Microsoft Excel (.xlsx)

This works well as a one-off workaround. The downloaded .xlsx will preserve the types assigned by Google Sheets, so Excel won't re-interpret them.

Trade-off: Your file is uploaded to Google's servers, so this isn't appropriate for confidential data unless your organization uses Google Workspace and has the necessary data agreements in place. Also requires a Google account and an internet connection.

What Excel Actually Does to Your CSV Numbers

Understanding the mechanism helps you predict which columns will break before you even open the file.

Leading zeros get stripped

When Excel sees 00154, it interprets it as the number 154. The zeros are "unnecessary" for a number, so they're dropped. This hits:

• ZIP/postal codes: 01234 → 1234
• Phone numbers: 09012345678 → 9012345678
• Account/product IDs: 00089 → 89

The critical detail: this happens at the data level, not just the display level. If you change the cell format to "Text" after opening, the cell shows 154 — not 00154. The zeros are already gone from Excel's internal storage.

Long numbers become scientific notation

Numbers exceeding 11 digits trigger Excel's scientific notation display. The behavior varies by length, and the damage escalates with digit count:

A 16-digit tracking number like 1234567890123456 becomes 1234567890123450 in Excel's memory. The last digit is gone forever, even if you format the cell correctly afterward.

Hyphens and slashes trigger date conversion

Excel interprets patterns that resemble dates:

• 1-2 → January 2
• 3/4 → March 4
• 2024-01-15 → a date serial number

Product codes (A-1, B-2), version numbers (2.1-3), and address components (1-3-5) are common casualties. Once converted to a date serial, the original string is lost.

What to Do If You Already Saved the Corrupted File

The fix depends entirely on whether you've hit the save button yet.

Haven't saved yet — close without saving

If you just opened the CSV and noticed the corruption, close the file immediately. When Excel asks "Do you want to save?", click Don't Save. The original CSV on disk is still untouched.

Now re-open it using any of the five methods above. Your data is fine — Excel only corrupted its in-memory copy.

Already saved — re-export from the source

Once you save a corrupted CSV, the mangled values are written back to the file. 00154 is now permanently 154 in the file content. There's no undo.

Your options:

1. Re-download from the source system. Most SaaS platforms (Shopify, Salesforce, HubSpot) keep export history. Download a fresh copy and open it correctly this time
2. Query the database directly if you have access. The source data is intact — only the CSV copy was corrupted
3. Check for backups. Cloud storage (OneDrive, Dropbox) may have a previous version of the file

Why changing cell format after the fact doesn't work

This is the most common mistake. The sequence looks reasonable:

1. Open CSV (data gets corrupted)
2. Select the column → Format Cells → Text
3. Expect the original values to reappear

It doesn't work because Excel has already converted the internal value. Formatting only changes how the value is displayed, not what the value is. The number 154 displayed as Text is still 154, not 00154. The zeros were discarded at parse time, before any formatting could intervene.

The same applies to scientific notation: if Excel converted 123456789012345678 to 1.23457E+17, the internal value is 123457000000000000. Formatting it as a number just shows you the already-rounded value.

How to Prevent This Problem Permanently

If you deal with CSVs regularly, a one-time fix beats a per-file workaround every time.

Set Microsoft 365 options once and forget

Method 1 from earlier is the cleanest permanent solution for M365 users. Three checkboxes, and the problem disappears for every CSV you open from that point forward — including files you open by double-clicking.

Worth repeating the one limitation: Excel's core date parser can still convert slash-separated values like 1/2 into dates, even with the "Convert consecutive letters and numbers to a date" option disabled. This specific pattern bypasses the newer toggle. For files with those patterns, combine the M365 setting with Method 2 (Power Query import) for full protection.

Ask your source system for xlsx exports

The most overlooked solution: if the system exporting your data supports .xlsx output, use that instead of CSV. An xlsx file embeds column types directly — Excel doesn't need to guess, and nothing gets converted.

Check your export settings in whatever tool generates the CSV. Many platforms (Shopify, QuickBooks, Jira, most CRMs) offer xlsx as an alternative. If the option exists, switch to it and this entire problem goes away.

Standardize your team's CSV workflow

In a team setting, the "someone opened it wrong and uploaded corrupted data" scenario is almost inevitable unless you have a standard process.

Options that work:

• Bookmark a browser-based converter (LeapRows — disclosure: built by the author — is one option) and make it the team default for CSV-to-Excel conversion
• Create a shared Power Query template in Excel that imports CSVs with all columns set to Text
• Add a note to your internal docs: "Don't double-click a CSV file unless you've disabled auto-conversion in Excel settings. Import through the Data tab or use a converter tool instead"

The specific tool matters less than having a process. One corrupted upload can break reports, misroute shipments, or trigger wrong billing — and the person who opened the CSV usually won't notice until the damage propagates downstream.

---

If you're on Microsoft 365, disable auto-conversion in settings once and you can open CSVs normally from that point on. Everyone else should import through the Data tab or use a converter tool — and never save before checking that the numbers look right. If you've already saved a corrupted file, re-export from the source — Excel can't undo what it did to your numbers.