Summary

Every payroll, CRM and billing system exports a CSV, and Excel rewrites it the moment you double-click. ZIP codes lose their leading zeros, sixteen-digit account numbers turn into 1.23457E+15, and accented names arrive as é. This article shows you how to import a CSV with the column types under your control, how to fix encoding and delimiter problems in the same dialog, and what can and cannot be recovered once a damaged file has been saved.

Symptoms

• ZIP codes arrive as 2134 instead of 02134, and 007 becomes 7.
• Long account, order or credit card numbers display as 1.23457E+14, and widening the column reveals trailing zeros where real digits used to be.
• Names and addresses show é, ü, ’ or â€" where accented letters, umlauts and curly quotes belong.
• Every row lands entirely in column A, with semicolons visible between the values.
• Dates flip: 03/04/2026 was 3 April in the export and reads as 4 March on screen, or the other way round.
• Product codes such as SEP12 or MAR15 display as dates.
• A value that starts = or + shows a formula error instead of the text.
• Re-opening the same file next week breaks it in exactly the same way, even after you fixed the cells and saved.

In this guide

• 1. Understand Why Double-Clicking the File Is the Problem
• 2. Import the File with Data > From Text/CSV
• 3. Set Each Column's Type Yourself in Power Query
• 4. Fix Accented Characters Showing as é or ’
• 5. Fix a File Where Every Row Lands in One Column
• 6. Rescue a File That Has Already Been Saved Damaged

Before You Start

• The original CSV file, unmodified. Everything in this article depends on having the source to re-import from.
• Excel for Windows or Mac. The import preview described here is not available in Excel for the web.
• No admin rights required.

Instructions

1. Understand Why Double-Clicking the File Is the Problem

Thirty seconds here saves you from repeating the damage.

A CSV is a plain text file. It holds values and separators and nothing else — no formats, no types, no instruction that says 02134 is a postal code rather than a quantity. When you double-click one, Excel opens it with no dialog at all, and to fill the gap it inspects each column and decides for itself:

• A column of digits becomes a number, and numbers do not carry leading zeros. 02134 is stored as 2134.
• A number longer than 15 significant digits is truncated to 15, with zeros filling the rest. This is the one that hurts: the missing digits are not hidden, they are gone.
• Anything shaped like a date becomes a date, using your computer's regional format to decide whether 03/04/2026 is March or April.
• Text is decoded using your system's legacy code page rather than UTF-8, which is what turns é into é.

None of these decisions is announced. The file looks fine at a glance, which is why the damage usually surfaces a week later when a lookup returns nothing.

Note: Excel for Microsoft 365 on Windows has switches under File > Options > Data > Automatic Data Conversion that stop some of this. They help, but they cover only leading zeros, long numbers, scientific notation and letter-and-digit codes. Import is still the route that gives you per-column control. See How to Stop Excel Changing Numbers into Dates for that settings page in detail.

2. Import the File with Data > From Text/CSV

This is the fix. Open Excel first, then bring the file in.

1. Open Excel and create a blank workbook, or open the workbook the data belongs in.
2. Open the Data tab on the ribbon.
3. In the Get & Transform Data group at the far left, click From Text/CSV.
4. Browse to your file, select it, and click Import. A preview window opens with the file name as its title, showing the first 200 rows in a grid.
5. Look at the three dropdowns along the top of that window:
   • File Origin — the character encoding. Covered in Step 4.
   • Delimiter — the separator character. Covered in Step 5.
   • Data Type Detection — Excel's guessing behaviour.
6. Open Data Type Detection and choose Do not detect data types. The preview redraws, and every column header now carries an ABC symbol meaning text.
7. Click Load at the bottom-right of the window.

The data lands on a new sheet as an Excel table, with 02134 intact, 1234567890123456 showing all sixteen digits, and SEP12 still reading SEP12.

Verification: Click a ZIP code cell. The value sits against the left edge of the cell and the formula bar shows the full code including its leading zero. Left alignment is Excel telling you it is holding text.

Note: Every column arrives as text with this setting, which means =SUM() over a genuine amounts column returns 0. If you need to calculate on some columns, use Step 3 instead of this shortcut.

Tip: The import is stored with the workbook as a query. When the source file is updated, click any cell in the imported table and choose Data > Refresh All. Your column types are reapplied every time, so the file cannot re-break itself.

3. Set Each Column's Type Yourself in Power Query

Use this when the file mixes identifiers that must stay as text with amounts and dates you need to calculate on.

1. Follow Step 2 as far as the preview window, but click Transform Data at the bottom instead of Load. The Power Query Editor opens in its own window, with the data in the middle and an Applied Steps list down the right side.
2. Look at the small symbol to the left of each column heading. 123 means whole number, ABC means text, ABC/123 means undetermined, and a calendar symbol means date.
3. Right-click the heading of a column that must stay exactly as written — account number, ZIP code, phone number, product code — point to Change Type, and select Text.
4. If a Change Column Type dialog appears asking whether to replace or add, click Replace current. This matters: Add new step converts the column twice and the damage from the first conversion is baked in.
5. Repeat for every column that needs a specific type. Choose Decimal Number for amounts, Whole Number for counts, and Date for dates.
6. For a date column whose order is ambiguous, right-click the heading, choose Change Type > Using Locale, set Data Type to Date, choose the locale that matches the export (for example English (United Kingdom) for day-first dates), and click OK.
7. Click Close & Load at the far left of the Home tab. The editor closes and the data lands in the workbook.

Note: If you spot a mistake after loading, you do not have to start over. Click any cell in the table, open the Query tab that appears on the ribbon, and click Edit to reopen the Power Query Editor. Change the step and reload.

Tip: Delete the automatic Changed Type entry at the top of Applied Steps before adding your own. Power Query often inserts one that has already guessed wrong, and your later steps then work on data that was mangled a step earlier.

That editor does far more than set column types, and every step you add there reruns itself when you drop next month's file into the same folder — How to Use Power Query in Excel walks one file from import to one-click refresh.

4. Fix Accented Characters Showing as é or ’

é where é belongs is an encoding mismatch, not corruption. The bytes in the file are correct; Excel is decoding them with the wrong alphabet.

1. Repeat Step 2 up to the preview window.
2. Open the File Origin dropdown at the top-left of the window.
3. Select 65001: Unicode (UTF-8). The preview redraws immediately and the accented characters appear correctly.
4. Set Data Type Detection as in Step 2 or 3, then click Load.

If 65001: Unicode (UTF-8) still shows scrambled text, work through the alternatives in the same dropdown:

• 1200: Unicode — for files exported from older Windows applications, often twice the expected size on disk.
• 1252: Western European (Windows) — for legacy exports from accounting and payroll systems.

Note: é, ’ and â€" are the signature of UTF-8 read as Windows-1252. A single é is stored as two bytes in UTF-8, and reading those two bytes as separate Western European characters produces exactly that pair. Seeing it means the source file is fine and the encoding choice is the only thing to change.

Tip: When you export back out, use File > Save As and choose CSV UTF-8 (Comma delimited) rather than plain CSV (Comma delimited). The plain version writes the legacy code page and reintroduces the same damage for whoever opens it next.

5. Fix a File Where Every Row Lands in One Column

Every value crammed into column A with ; between them means the file uses a separator your Excel is not expecting. Exports generated in Germany, France, Spain, Italy and much of continental Europe use a semicolon, because the comma is the decimal separator there.

1. Repeat Step 2 up to the preview window.
2. Open the Delimiter dropdown at the top of the window.
3. Select the separator the file actually uses: Semicolon, Tab, Comma, Space, or Colon. The preview splits into proper columns as soon as you pick the right one.
4. For anything unusual — a pipe character, for instance — choose Custom and type the character into the box that appears.
5. Set Data Type Detection, then click Load.

Note: Excel decides the default separator from the Windows list separator, not from the file. To check or change yours, open Control Panel, set View by to Small icons, click Region, click Additional settings... on the Formats tab, and read the List separator box. Changing it affects every application on the computer, so the import dropdown is the safer place to solve a one-off file.

Tip: For a quick look at a file whose structure you do not trust, right-click it in File Explorer, choose Open with > Notepad, and read the first two lines. You will see the real separator, whether values are wrapped in quotation marks, and whether the header row is where you expect.

6. Rescue a File That Has Already Been Saved Damaged

Be honest about what is recoverable, because the answer differs by column.

Recoverable from the workbook you have:

• Lost leading zeros. The number underneath is intact. Select the column, press Ctrl + 1, choose Custom at the bottom of the Category list, and type 00000 in the Type box — one zero per digit. That fixes the display; to make the zeros part of the value, build a helper column with =TEXT(A2,"00000") and paste the result back as values.
• Dates read in the wrong order where every date is unambiguous (a day above 12 appears somewhere in the column). Rebuild them with =DATE(YEAR(A2),DAY(A2),MONTH(A2)).

Not recoverable:

• Long numbers turned into scientific notation. Excel stores 15 significant digits. A sixteen-digit value was truncated as it was read, and the workbook now holds 1234567890123450 where the file held 1234567890123456. There is no copy of the missing digit anywhere in the workbook. Widening the column, changing the format, and increasing decimal places all display the truncated number.
• Text decoded with the wrong encoding, then saved. The two-byte pairs are collapsed into single wrong characters at save time.
• Values Excel converted to dates. SEP12 is now a date serial number and the original characters are discarded.

The route back for all three is the same: go to the original download and import it again with Step 2 or Step 3. If the source system still holds the data, re-export rather than re-using a file that has been through Excel.

1. Close the damaged workbook without saving anything further.
2. Locate the original file. Check your Downloads folder, the email attachment, or the export screen in the source system.
3. Import it with Data > From Text/CSV, setting the types before loading.
4. Rebuild any formulas or formatting on top of the fresh import.

Note: If the original file is genuinely gone and the source system cannot re-export, tell IT exactly what you need: "I need the original CSV export re-run for the account-number field — Excel truncated it to 15 digits on import and the last digits are unrecoverable." Naming the field and the cause gets you a re-export instead of a support conversation.

Troubleshooting

DANGER

Saving a workbook after a bad CSV open makes the damage permanent for the columns Excel truncated or re-decoded. Trailing digits of long numbers and mis-decoded accented characters are not hidden by formatting — they no longer exist in the file. Keep the original download until you have confirmed every column survived, and re-import rather than repairing in the sheet.

Symptom / Error	Potential Cause	Solution
ZIP codes show 2134 instead of 02134	Column read as a number	Re-import with Do not detect data types (Step 2), or type the column as Text in Power Query (Step 3).
Account numbers show 1.23457E+14	Number truncated to 15 digits	Not repairable in place. Re-import from the original with the column typed as Text (Step 3).
Names show é and ’	UTF-8 decoded as Western European	Set File Origin to 65001: Unicode (UTF-8) in the import preview (Step 4).
Whole rows sit in column A	File uses semicolons or tabs	Set the Delimiter dropdown to match the file (Step 5).
From Text/CSV is missing from the Data tab	Excel for the web, or Excel 2013 and earlier	Use Excel for Windows or Mac. On Excel 2013, use Data > From Text and set each column to Text on step 3 of the wizard.
The imported table shows only 200 rows in the preview	Preview limit, not an import limit	Expected. The preview samples the first 200 rows; Load brings in the whole file.

Related Articles

• How to Use Power Query in Excel
• How to Stop Excel Changing Numbers into Dates
• How to Fix Numbers Stored as Text in Excel
• How to Format Data as a Table in Excel
• How to Remove Duplicate Rows in Excel
• How to Fix "This Workbook Contains Links to External Sources" in Excel
• How to Use Data Validation and Input Tips in Excel

Did this solve your problem?