Importing a CSV file into Excel sounds like it should take one click. For a clean file, it does. For the CSV file a supplier sent you this morning with semicolons as delimiters and an encoding that makes every accented character turn into a question mark, it absolutely does not.
This guide covers the four methods Excel offers for importing CSV data, when each one is the right choice, and how to fix the common problems that derail imports in practice.
What is a CSV file, quickly
A CSV, comma-separated values, is a plain text file that stores data in rows and columns. Each line is a row. Each value in a line is separated by a comma. Most files start with a header row naming the columns.
Name,Email,Age
Jane Doe,jane@example.com,30
John Smith,john@example.com,45
CSV is universal, every tool reads it. It is also minimalist, which means Excel has to make assumptions when it opens one. Those assumptions are what sometimes go wrong.
Option 1: Open the file directly
The fastest way is to double-click the CSV or use File > Open from within Excel. Excel detects the format, splits values into columns using the comma separator, and displays the result.
This works well for small, clean files. It is the right choice for a quick look at data or when you trust the source.
Watch out for three things. Excel may auto-format values it thinks it recognizes. Dates get reformatted, currencies get styled, and leading zeros in fields like product codes or postal codes disappear because Excel reads them as numbers. Special characters may display incorrectly if the encoding is not what Excel expects.
For anything beyond a quick look, use option 2.
Option 2: Use the Import feature
Excel's Import feature gives you control over how the file is parsed before it lands in your sheet. It adds two or three clicks compared to opening directly, and it prevents most of the problems caused by auto-formatting.
The path is Data > Get Data > From File > From Text/CSV. You select the file, and Excel shows a preview with the delimiter it detected and the encoding it is using. Confirm or change these, adjust data types per column if needed, then click Load.
This method is the right default for most real-world files. It handles non-English characters reliably, lets you specify the delimiter when it is not a comma, and prevents Excel from silently corrupting your data by guessing wrong about column types.
Option 3: Drag and drop
Dragging a CSV file onto an open Excel window triggers an automatic import. It is fast and works well for simple files. The trade-off is that you have no visibility into delimiters or encoding, so if something goes wrong, you will not know why.
Use this method for files you have already imported before and trust. For a new file from an unfamiliar source, stick with option 2.
Option 4: Use Power Query for recurring imports
Power Query is Excel's tool for loading and transforming data from external sources. It is more powerful than the standard Import feature and it shines in one specific case, recurring imports.
If you import the same file every month from the same source, Power Query lets you save the import steps. You clean the columns, adjust the types, remove empty rows, and save the query. Next month, you point Power Query at the new file and it applies the same transformations automatically.
The path is the same as option 2 (Data > Get Data > From File
From Text/CSV), but instead of clicking Load, click Transform Data. This opens the Power Query Editor where you can split columns, rename headers, filter rows, and change data types. Close and Load when you are done. The query is saved with the workbook and can be refreshed anytime.
For monthly reporting, recurring supplier feeds, or any workflow where you handle the same file shape repeatedly, Power Query pays for itself in a few weeks of time saved.
Common problems and how to fix them
| Problem | Cause | Fix |
|---|---|---|
| Leading zeros missing | Excel reads the column as Number | Use Import feature, set the column type to Text |
| Gibberish characters (é appears as é) | Wrong encoding detected | Re-import with UTF-8 encoding |
| All data in one column | Wrong delimiter detected | Use Import feature, specify the real delimiter (comma, semicolon, tab) |
| Extra empty columns | Inconsistent row structure in the source | Clean the source file or handle in Power Query |
| Text splits across multiple rows | Line breaks inside values | Use Power Query, which handles this correctly |
Should you convert the CSV to an Excel file?
If you plan to add formulas, charts, or formatting, save the file as a proper Excel workbook (.xlsx) rather than keeping it as CSV. File > Save As > Excel Workbook. This preserves everything you add to the file and prevents Excel from auto-reformatting the data the next time you open it.
If the file only needs to store raw data for re-export to another system, keep it as CSV. Saving as .xlsx strips none of the data but it adds file size and incompatibility with systems that only accept CSV.
Tips for files from common platforms
Each platform exports CSV with its own quirks. A few worth knowing:
Shopify and Magento often include a UTF-8 BOM (byte order mark) at the start of the file. Recent Excel versions handle it correctly. Older versions display a stray character in the first header cell.
Salesforce and HubSpot exports can contain line breaks inside text fields (for example, in notes or descriptions). The standard import sometimes splits these into multiple rows. Use Power Query to import these correctly.
QuickBooks and Xero from European locales often use semicolons as delimiters instead of commas, because the comma is reserved for decimals. Always verify the delimiter when importing accounting exports from European tools.
Google Sheets exports to CSV with UTF-8 encoding by default, which is the safest encoding choice. These files rarely cause encoding issues in Excel.
When importing CSV to Excel is not the right answer
If you find yourself importing CSVs into Excel every week from the same sources, as part of a business workflow that feeds another system afterwards, Excel might not be the right stop in the pipeline. You are essentially using a spreadsheet as a data cleaning tool, which works at small scale and breaks down as volume grows.
When format variation across sources becomes a recurring problem, the better approach is to handle the variation upstream, in a tool built for it, and feed clean data directly into whatever comes next in your workflow.
For occasional imports, Excel remains one of the best tools for the job.
Conclusion
Excel has four ways to import CSV data and each fits a different situation. Quick checks get option 1. Real files from external sources get option 2. Recurring imports get option 4. Option 3 is a convenience shortcut for files you already know.
When an import breaks, the cause is usually one of three things, the delimiter, the encoding, or a column type Excel guessed wrong. The table above lists the fixes for the common cases. For anything more complex, Power Query is the right escalation.