How to Open and Import CSV Files in Excel

1. Opening CSV Files Directly in Excel

The quickest way to view a CSV file is to open it directly. Excel recognises the .csv extension and will attempt to parse it automatically. There are two common approaches.

Double-Click the File

If Microsoft Excel is set as the default application for .csv files on your system, simply double-clicking the file in Windows Explorer or macOS Finder will open it in Excel. The application splits each line into rows and uses the comma as the column delimiter.

This method is fast but gives you no control over how the data is interpreted. Excel will guess the data type of every column, which can lead to problems: dates may be reformatted, long numbers may lose precision, and leading zeros will be stripped. If accuracy matters, use one of the import methods described in the sections below.

File > Open

You can also open a CSV from within Excel itself:

1. Launch Excel and go to File > Open.
2. In the file picker, change the file type filter to Text Files (*.csv; *.txt; *.prn) or All Files.
3. Navigate to your CSV file and click Open.

In older versions of Excel (2013 and earlier), this launches the Text Import Wizard automatically. In newer versions (Microsoft 365 / Excel 2016+), the file opens directly unless you hold the wizard open through the method described in the next section.

2. Using the Text Import Wizard for More Control

The Text Import Wizard is the classic way to import CSV data with full control over delimiters, text qualifiers, and column data types. It has been part of Excel for decades and remains the most reliable approach for handling tricky files.

How to Launch the Text Import Wizard

In Excel 2016 and later, Microsoft disabled the wizard by default in favour of Power Query. To re-enable it:

1. Go to File > Options > Data.
2. Under Show legacy data import wizards, check From Text (Legacy).
3. Click OK.
4. Now go to Data > Get Data > Legacy Wizards > From Text (Legacy).

In Excel 2013 and earlier, go to Data > From Text directly.

Step-by-Step Walkthrough

The wizard has three steps:

Step 1 – File Origin and Type: Choose Delimited (not "Fixed width"). Set the File origin dropdown to match your file's encoding –65001: Unicode (UTF-8) is the most common. You can preview the data to confirm it looks correct.

Step 2 – Delimiters: Check the delimiter your file uses. For standard CSV, selectComma. If your file uses semicolons (common in Europe), select Semicolon. You can also set the Text qualifier – usually a double quote (").

Step 3 – Column Data Formats: This is the most important step. Click on each column in the preview and set its data format:

• General: Excel guesses the type (default). Risky for IDs, zip codes, and dates.
• Text: Preserves the value exactly as it appears in the file. Use this for ZIP codes, phone numbers, product IDs, and any column where leading zeros matter.
• Date: Tell Excel the date format (MDY, DMY, YMD) so it parses correctly.
• Do not import column: Skip columns you don't need.

3. Using Power Query (Get & Transform Data)

Power Query is the modern replacement for the Text Import Wizard. It's built into Excel 2016 and later (including Microsoft 365) and offers far more flexibility: you can filter, transform, and reshape data before it ever hits a worksheet cell.

Importing a CSV with Power Query

1. Open Excel and go to Data > Get Data > From File > From Text/CSV.
2. Browse to your CSV file and click Import.
3. Excel shows a preview dialog with auto-detected delimiter and encoding.
4. Check that the delimiter and encoding are correct. If the preview looks garbled, change File Origin to 65001 (UTF-8).
5. Click Transform Data to open the Power Query Editor, or click Load to import directly.

Advantages of Power Query over Direct Open

• Column type control: Change any column to Text, Number, Date, etc. before loading.
• Repeatable imports: The query is saved. When the source CSV is updated, click Refresh to reload.
• Data transformation: Split columns, merge columns, filter rows, remove duplicates – all before loading into the sheet.
• Multiple file support: Import all CSVs from a folder in one step.
• Better large-file handling: Power Query streams data and uses less memory than opening a file directly.

Setting Column Types in Power Query

To prevent data loss, change column types before loading. In the Power Query Editor:

1. Click the icon in the column header (it shows ABC for text, 123 for number, etc.).
2. Select the correct type. Use Text for IDs, ZIP codes, and phone numbers.
3. If Power Query already applied an auto-detected type, look for the "Changed Type" step in the Applied Steps panel on the right. You can delete or modify that step.

// Power Query M code to force columns to text:
let
    Source = Csv.Document(
        File.Contents("C:\data\products.csv"),
        [Delimiter=",", Encoding=65001, QuoteStyle=QuoteStyle.Csv]
    ),
    PromotedHeaders = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    // Force ProductID and ZipCode to text to preserve leading zeros
    ChangedTypes = Table.TransformColumnTypes(PromotedHeaders, {
        {"ProductID", type text},
        {"ZipCode", type text},
        {"Price", type number},
        {"OrderDate", type date}
    })
in
    ChangedTypes

4. Handling Encoding Issues (UTF-8, BOM, Special Characters)

Character encoding is one of the most frequent sources of frustration when opening CSV files in Excel. If you see garbled text like é instead of e, or â€" instead of an em dash, you have an encoding mismatch.

Understanding the Problem

Most modern CSV files are encoded in UTF-8, which supports every character in every language. However, when you double-click a CSV file, Excel often defaults to your system's local encoding (Windows-1252 on Western Windows systems, Shift-JIS on Japanese systems, etc.) rather than UTF-8. This causes multi-byte UTF-8 characters to be misinterpreted.

Solution 1: Save the CSV with a UTF-8 BOM

A BOM (Byte Order Mark) is a special invisible character (EF BB BF in hex) placed at the very beginning of a file. When Excel sees this marker, it knows the file is UTF-8 and decodes it correctly.

If you control the CSV generation, add the BOM. In most programming languages, this is straightforward:

# Python: write CSV with UTF-8 BOM
import csv

with open('output.csv', 'w', newline='', encoding='utf-8-sig') as f:
    writer = csv.writer(f)
    writer.writerow(['Name', 'City', 'Price'])
    writer.writerow(['Café Mocha', 'Zürich', '€4.50'])

The utf-8-sig encoding in Python automatically prepends the BOM. When you open this file by double-clicking in Excel, the special characters will display correctly.

Solution 2: Specify Encoding During Import

If you cannot modify the source file, specify the encoding when importing:

• Text Import Wizard: In Step 1, set File origin to 65001: Unicode (UTF-8).
• Power Query: In the import preview, change File Origin to 65001 (UTF-8).

Solution 3: Re-save the File in Notepad

As a quick workaround on Windows, open the CSV file in Notepad, go to File > Save As, and in the Encoding dropdown choose UTF-8 with BOM. Save the file and then open it in Excel.

5. Fixing Common Problems

Even experienced Excel users run into issues when opening CSV files. Below are the most common problems and their solutions.

Problem: Dates Get Reformatted or Misinterpreted

A date like 01/02/2025 in your CSV could mean January 2nd (US) or February 1st (UK/Europe). Excel uses your system's regional settings to guess, and it often guesses wrong. Worse, once Excel converts a text value to an internal date serial number, you can't get the original text back.

Solutions:

• Use the Text Import Wizard and set date columns to the correct date format (MDY, DMY, or YMD) in Step 3.
• In Power Query, set the column type to Date using the correct locale.
• If you control the CSV source, use the ISO 8601 format (YYYY-MM-DD). This is unambiguous and Excel handles it correctly everywhere.

Problem: Leading Zeros Are Stripped

Values like 007890 (a product code) or 01onal (a US ZIP code) get converted to7890 and 1234. Excel sees digits and treats them as numbers, removing the "unnecessary" leading zeros.

Solutions:

• Text Import Wizard: Set the column to Text in Step 3.
• Power Query: Change the column type to Text before loading.
• Prefix trick: If you generate the CSV yourself, prepend an equals sign and wrap in quotes: ="007890". Excel will treat this as a formula that returns the text 007890. However, this is a hack and not recommended for data interchange.

Problem: Long Numbers Displayed as Scientific Notation

Credit card numbers, IMEI codes, and other long numeric strings (more than 15 digits) get turned into scientific notation like 1.23457E+15 and lose precision. Excel can only store 15 significant digits in a number; any extra digits are replaced with zeros.

Solutions:

• Import the column as Text using the Text Import Wizard or Power Query. This is the only reliable fix.
• Once a number has already been converted and precision has been lost, there is no way to recover it. You must re-import from the original CSV.

Problem: All Data Appears in One Column

If your entire row of data appears in a single cell separated by semicolons or tabs instead of being split across columns, Excel is using the wrong delimiter. This typically happens when:

• The file uses a semicolon as the delimiter (common in European locales where the comma is the decimal separator).
• The file uses a tab character as the delimiter (sometimes called TSV).
• Your system's regional list separator doesn't match the file's delimiter.

Solutions:

• Use the Text Import Wizard and manually select the correct delimiter in Step 2.
• Use Power Query, which auto-detects the delimiter – but verify in the preview.
• See Section 6 below for changing Windows regional settings.

Problem: Extra Blank Rows or Columns

Blank rows at the end of a CSV (often caused by trailing newlines) or empty columns (caused by trailing commas on each line) can pollute your data. In Power Query, you can easily remove empty rows withRemove Blank Rows under the Home tab. In the worksheet, useCtrl+End to find the real end of your data, select the empty rows/columns, and delete them.

6. Changing the Default Delimiter (Regional Settings)

When you double-click a CSV file, Excel uses your operating system's list separatorto split columns. In the US, UK, and most English-speaking countries, this is a comma. In many European countries (Germany, France, Italy, the Netherlands, etc.), the list separator is a semicolonbecause the comma is used as the decimal separator.

Changing the List Separator on Windows

1. Open the Control Panel and go to Clock and Region > Region.
2. Click Additional settings.
3. In the List separator field, change the value. Set it to a comma (,) to work with standard CSVs, or a semicolon (;) for European-style CSVs.
4. Click OK and restart Excel.

Note that changing this setting affects all applications on your system, not just Excel. If you don't want to change system-wide settings, use the Text Import Wizard or Power Query instead, which let you pick the delimiter on a per-file basis.

Changing the List Separator on macOS

On macOS, Excel uses the region settings from System Preferences > Language & Region. Unlike Windows, macOS does not expose a direct "list separator" setting. The simplest approach on macOS is to use one of the import methods (File > Import in older Excel, or Data > Get Data in newer Excel) and select the correct delimiter during the import process.

sep=;
Name;City;Price
Müller;Berlin;€9,99
Dupont;Paris;€12,50

7. Tips for Large CSV Files in Excel

Excel has hard limits on how much data it can handle. Knowing these limits will save you from data loss and performance problems.

Excel's Row and Column Limits

If your CSV has more than 1,048,576 rows, Excel will silently truncate the data when you open it directly. There is no warning – you simply lose the extra rows. Always check the row count after import if you suspect the file might be large.

Strategies for Large Files

• Use Power Query to filter before loading: Open the file in Power Query, apply filters to remove rows you don't need, and only load the subset that fits in Excel's limits.
• Split the file: Use a tool or script to split the CSV into multiple files of under 1 million rows each. Open each file in a separate worksheet or workbook.
• Load into the Data Model: Power Query can load data into Excel's Data Model (PowerPivot) instead of a worksheet. The Data Model can hold millions of rows and supports PivotTable analysis without putting all the data on a sheet.
• Consider alternatives: For truly large datasets (millions of rows or multi-GB files), Excel may not be the right tool. Consider loading the data into a database (SQLite, PostgreSQL), using Python with pandas, or using a business intelligence tool like Power BI.

Performance Tips

• Disable auto-calculation: Go to Formulas > Calculation Options > Manual before loading large data. Switch back to Automatic when done.
• Close other workbooks: Free up memory by closing any workbooks you don't need.
• Use 64-bit Excel: If you have the choice, the 64-bit version of Excel can use more RAM and handles large files better than the 32-bit version.
• Avoid volatile functions: Functions like INDIRECT, OFFSET, and NOW() recalculate on every change and slow down large workbooks.

# Python: split a large CSV into chunks of 1 million rows
import csv
import os
input_file = 'large_data.csv'
chunk_size = 1_000_000  # Excel's row limit (minus 1 for header)

with open(input_file, 'r', encoding='utf-8') as f:
    reader = csv.reader(f)
    header = next(reader)

    file_num = 1
    row_count = 0
    writer = None
    out_file = None

    for row in reader:
        if row_count % chunk_size == 0:
            if out_file:
                out_file.close()
            filename = f'large_data_part{file_num}.csv'
            out_file = open(filename, 'w', newline='', encoding='utf-8-sig')
            writer = csv.writer(out_file)
            writer.writerow(header)
            file_num += 1

        writer.writerow(row)
        row_count += 1

    if out_file:
        out_file.close()

print(f'Split into {file_num - 1} files ({row_count} total rows)')

Quick Reference: Which Method Should You Use?

Create Your CSV Files with text2csv.com

Need to create a properly formatted CSV file that opens cleanly in Excel? Our free Text to CSV Converter at text2csv.com generates RFC 4180-compliant CSV output that handles quoting, escaping, and delimiters correctly. Paste your text data, choose your settings, and download a CSV file that Excel can import without issues.

The converter supports custom delimiters (comma, semicolon, tab, pipe), optional quoting, and produces clean output suitable for Excel, Google Sheets, databases, and any other application that reads CSV.