How to Import a CSV File Into Excel Without Breaking It

To import a CSV into Excel without breaking it, use Data > From Text/CSV instead of double-clicking the file. Importing shows you a preview and lets you set each column's type, so leading zeros, long account numbers, codes and accented names arrive the way the file has them. This guide walks through the import step by step and then shows the formulas that fix anything that still comes in wrong.

Why double-clicking a CSV breaks your data#

A CSV (comma-separated values) file is plain text: one row per line, with commas between the values. It has no column types, so when you double-click it, Excel guesses the type of every column. The guesses cause most CSV problems:

The 15-digit limit is permanent: Excel stores numbers with up to 15 significant digits, so the 16th digit becomes a 0 and saving the file keeps the damage. The guide to CSV vs XLSX explains what each format can and can't store.

How to import a CSV into Excel step by step#

In Excel for Microsoft 365, Excel 2021 and Excel 2016 or later on Windows, use Get & Transform, also called Power Query:

1. Open a blank workbook and go to Data > From Text/CSV. (In some versions it's Data > Get Data > From File > From Text/CSV.)
2. Pick the CSV file and click Import.
3. In the preview, check File Origin. Choose 65001: Unicode (UTF-8) if the text has accents or non-English letters.
4. Check Delimiter. It's usually Comma, but some files use semicolons or tabs.
5. Click Transform Data if any column needs a different type. Otherwise click Load.

Excel loads the data as a formatted table on a new sheet. On a Mac, recent Microsoft 365 versions offer Data > Get Data (Power Query). Older Mac versions use File > Import, which opens the Text Import Wizard described below.

How to keep leading zeros, IDs and codes as text#

To protect a column, change its type to Text in Power Query before loading:

1. After clicking Transform Data, find the column, such as Order ID or ZIP.
2. Click the type icon on the left of the column header (it shows 123 for whole numbers).
3. Choose Text. If Excel asks, pick Replace current so the change replaces its own guess.
4. Repeat for phone numbers, account numbers and part codes, then click Close & Load.

The rule of thumb: if you'll never add it up, import it as text. That covers IDs, ZIP and postal codes, phone numbers, card and account numbers, and codes like 3-4. The guide on keeping leading zeros in Excel has more ways to rebuild zeros that were already lost.

How to import dates in the right order#

When the file's date order doesn't match your computer's settings, set the type with a locale. A locale is a country and language setting that tells Excel which order the day and month come in. In Power Query, right-click the date column header and choose Change Type > Using Locale. Pick Date as the type and the locale the file was made in, such as English (United States) for month-first dates or English (United Kingdom) for day-first dates.

If the dates have already landed as text, convert them afterwards; see how to convert text to a date. It helps to know that a real date is a number underneath, which the date basics lesson shows with DATE, YEAR, MONTH and DAY.

How to use the legacy Text Import Wizard#

The older Text Import Wizard still works and lets you set each column's format in three short steps. On Windows, turn it on with File > Options > Data, tick From Text (Legacy) under "Show legacy data import wizards", then use Data > Get Data > Legacy Wizards > From Text (Legacy).

1. Step 1: choose Delimited and tick My data has headers. Set File origin to UTF-8 if needed.
2. Step 2: tick Comma (or your delimiter) and check the preview.
3. Step 3: click each column in the preview and set it to Text, Date with the right order (MDY or DMY), or General. Click Finish.

The same wizard powers Data > Text to Columns, which splits a column that has already been pasted in. The Text to Columns guide covers it.

How to fix CSV data after import with TRIM and VALUE#

Some files still come in with numbers stored as text, often because of extra spaces or invisible characters. Two functions fix most of it:

• TRIM removes spaces from the start and end of text and reduces double spaces inside it to one.
• VALUE turns text that looks like a number into a real number.

Use them together, TRIM inside VALUE:

=VALUE(TRIM(B2))

For " 1250 " this returns the number 1250, which SUM and SUMIF can now add up. Fill it down (Windows: Ctrl+D; Mac: ⌘D), then paste the results as values over the original column if you want to replace it.

If TRIM doesn't remove a space, the file probably contains a non-breaking space, character 160, which web exports often add. Swap it for a normal space first:

=VALUE(TRIM(SUBSTITUTE(B2,CHAR(160)," ")))

For text like product names, =TRIM(CLEAN(A2)) also removes line breaks and other non-printing characters. See the TRIM function guide, the VALUE function guide, and how to fix numbers stored as text for the green-triangle warnings.

How to save back to CSV without losing data#

Saving as CSV keeps only values, and only from the active sheet. Formulas, formatting, filters and other sheets are gone. Before you save:

• Keep a working copy as .xlsx, and save the CSV as a separate file.
• Choose File > Save As > CSV UTF-8 (Comma delimited) (*.csv) so accented letters survive.
• Remember that text columns with leading zeros are written correctly to the CSV. They only disappear again if someone double-clicks the file instead of importing it.

Practise it#

The import itself takes a minute; cleaning what comes in is the skill employers test. Practise cleaning messy text in a live spreadsheet with TRIM, PROPER and SUBSTITUTE, then practise TEXT and VALUE to switch between numbers and text. Each formula is checked the moment you press Enter. Try the exercise on this page first: one imported amount, one fix.