Skip to the tool
ViewCSV
Files stay on your device

How to import a CSV file into Excel without breaking the data

By the ViewCSV team

Don't double-click the file. In Excel, choose Data > From Text/CSV, pick the file, check the encoding (File Origin) and Delimiter in the preview, and select Transform Data. In Power Query, delete the automatic Changed Type step, set ID, code and phone columns to Text, give number columns their type and select Close & Load. To skip import settings altogether, convert the CSV to an XLSX with text cells first.

Check the file before you import it

An import goes right when three things are known: the character between columns, the encoding the text was saved in, and which columns hold identifiers rather than quantities. The CSV viewer and the CSV validator show all three without changing anything.

DelimiterCommas are common in English-language exports. Many European systems use semicolons because the comma is their decimal mark. Tabs and pipes also occur.
EncodingMost current exports are UTF-8. Older Windows software often writes Windows-1252, and Japanese files may be Shift_JIS. The wrong choice shows é instead of é.
Header rowCheck that the first line holds the column names and that no report title or blank line sits above it.
Columns to protectNote every column with IDs, codes, phone numbers, ZIP or postal codes, or values longer than 15 digits. These need the Text type.

Import with From Text/CSV (Power Query)

This is the standard import in current Excel for Microsoft 365 and recent Excel for Windows; Excel for Mac versions with Power Query work similarly. Menu names can differ slightly between versions.

  1. On the Data tab, select From Text/CSV (in some versions Get Data > From File > From Text/CSV) and choose the file.
  2. In the preview, set File Origin to the file's encoding, for example 65001: Unicode (UTF-8), and set Delimiter. Accents should look right and every value should sit in its own column.
  3. Select Transform Data, not Load: loading straight away applies Excel's type guess, which can already round long IDs.
  4. In the Power Query Editor, look at Applied Steps. Excel usually adds Promoted Headers (first row as column names) and Changed Type. Delete Changed Type with the X next to it.
  5. Select the ID, code and phone columns and, on the Transform tab, set Data Type to Text. With the numeric step gone, they become text straight from the original file.
  6. Give the columns you'll calculate with the type Whole Number, Decimal Number or Date. For numbers written with a decimal comma, use Data Type > Using Locale and choose the locale the file was written in.
  7. Select Home > Close & Load.
Note Order matters. If you set a column to Text while Changed Type is still above it, Excel converts the already rounded number to text and the digits stay lost. If Excel asks whether to replace the current conversion or add a new step, choose Replace current.

Some versions also show a Data Type Detection list in the first preview. Do not detect data types loads every column as text, which is safe for IDs; you then change only the number columns.

Import with the legacy Text Import Wizard

The older wizard asks for each column's format up front. In Excel for Microsoft 365 on Windows it is hidden by default: turn on From Text (Legacy) under the legacy data import wizards in Excel's data options, then start it from Data > Get Data > Legacy Wizards > From Text (Legacy). Older versions show From Text on the Data tab, and Excel for Mac opens a similar wizard when you import a text file.

  1. Step 1: choose Delimited, set File origin to the encoding and tick My data has headers if the first row holds column names.
  2. Step 2: tick the delimiter (Comma, Semicolon, Tab or Other) and check the preview.
  3. Step 3: select each column you need to protect in the preview and set Column data format to Text. Leave number columns on General, or choose Date with the right order, such as DMY.
  4. Select Finish and choose where to put the data.

Like Power Query, the wizard sets the type before any value reaches a cell, which is why it protects long IDs and formatting afterwards does not.

Skip the import settings: convert to XLSX first

If the people opening the file shouldn't need any of this, give them a workbook instead of a CSV. The CSV to Excel converter reads the CSV in your browser, without uploading it, and writes every value as an Excel text cell by default, so Excel has nothing to guess.

  1. Open the CSV in the CSV to Excel converter. If the columns look wrong, set the delimiter or encoding under Import settings.
  2. For fixed-width text, several delimiters at once or report lines above the header, use Import text…: split by delimiters, fixed widths or a pattern, skip the leading lines, check the preview and select Import.
  3. Keep Keep every value as text for files with IDs and codes. Detect numbers and true/false turns columns of plain numbers into numbers; first read how it treats phone numbers and E-like codes.
  4. Select Download XLSX and open the workbook in Excel.

Try it with the sample file: it has leading zeros, 20-digit IDs, a date-like code and a note that looks like a formula.

Open CSV to ExcelDownload the sample CSV4 rows of fictional data, UTF-8

Fixing common import problems

  • Everything is in column A. The delimiter is wrong. Choose the right one in the preview, or under Import settings in ViewCSV.
  • Accented letters look like é or ?. The encoding is wrong. Try UTF-8 first, then Windows-1252 or the file's regional encoding.
  • The column names are in the first data row. Use Use First Row as Headers in Power Query, or tick My data has headers in the wizard.
  • Day and month are swapped in dates. Import the column as Text, or set its type using the locale the file was written in.
  • Long IDs end in zeros. They were converted before you set Text. Delete the import and start again from the original CSV; here is why the digits can't be recovered.
  • Numbers don't add up. They were imported as text. Change the column to a number type in Power Query, or use Convert to Number in Excel.

Before you share the result

Saving the sheet as CSV again drops the types, and the next person who double-clicks it meets the same problems. Share the workbook, or send the CSV with these import steps. The ViewCSV converter handles files up to 25 MB, 100,000 rows, 250 columns and 2 million cells; split larger files first.

Sources

Microsoft's documentation, summarized in our own words. Menu names can differ in your Excel version.

More guides

CSV and Excel guides