How to Get CSV into Excel: A Practical Guide
Learn reliable, step-by-step methods to import CSV data into Excel, handle delimiters and encodings, and transform data with Power Query. Includes best practices, troubleshooting tips, and scalable workflows for large CSV files.
By the end of this guide you will be able to move CSV data into Excel with confidence. You’ll learn the most reliable methods, including opening the file directly, using Data > Get & Transform (Power Query), and handling common issues like delimiters and encoding. You’ll also know when to choose each approach.
Why CSV into Excel matters for data workflows
CSV (comma-separated values) is a universal, lightweight format that preserves tabular data without the overhead of a database. For data analysts, developers, and business users, Excel remains a go-to tool for quick analysis, reporting, and visualization. Getting CSV into Excel cleanly reduces the risk of data corruption, ensures consistent column types, and makes it easier to apply formulas, pivot tables, and charts. According to MyDataTables, the most reliable CSV-to-Excel import practices emphasize preserving data integrity and correct delimiter handling. A robust workflow helps teams avoid surprises when the data source changes or when files are refreshed from external systems. In short, a solid import routine saves time and prevents downstream errors in dashboards and reports.
CSV fundamentals you should know before importing
Before importing, you should understand a few core concepts: file encoding (UTF-8 is common, but some CSVs use ANSI or UTF-16), delimiters (comma, semicolon, tab), the presence of a header row, and the consistency of data types across columns. If a CSV uses a nonstandard delimiter, Excel’s default opening may misplace data. Similarly, mismatches in thousands separators or decimal marks can cause numbers to import as text. By choosing a method that lets you specify encoding and delimiter settings, you avoid post-import cleanup. MyDataTables recommends verifying the source file in a text editor to confirm delimiter and encoding details before loading into Excel.
Method A: Open CSV directly in Excel
Opening a CSV directly in Excel is the simplest approach for small datasets with standard formatting. Double-clicking the file usually launches Excel and places data into a worksheet with default formatting. If the delimiter is not detected correctly, Excel may split fields incorrectly, so you may need to stop the import and choose Text Import Wizard steps or use the newer Get & Transform path for better control. This method is fast and sufficient for quick checks or one-off imports, especially when you’re certain the CSV uses common formatting.
Method B: Import via Data > Get & Transform (Power Query)
For robust CSV imports, especially with large files, use Data > Get Data > From Text/CSV (Power Query). This flow lets you preview the data, choose the correct delimiter, encoding, and data types, and apply transformations before loading. Power Query keeps your raw CSV separate from the worksheet, making it easier to refresh when the source file changes without losing your cleaned data. This approach scales well for recurring imports and complex datasets.
Handling delimiters and encoding correctly
Delimiter handling is crucial. If your CSV uses a semicolon or tab instead of a comma, you must specify the correct delimiter in the import dialog. Encoding mismatches can cause characters to appear garbled, especially for non-English text. In many cases, exporting or saving the CSV as UTF-8 eliminates most encoding issues. When using Power Query, you’ll see explicit options for delimiter and encoding; adjusting these early prevents cascading errors downstream in formulas and pivots.
Working with headers, data types, and locale settings
Headers determine how you reference columns in formulas and queries, so ensure the first row is a true header. After import, verify data types (text, number, date) and adjust as needed. Power Query allows explicit type casting, which helps avoid errors in calculations and charts. Be mindful of locale settings for dates and numbers (e.g., 12/31/2025 vs 31/12/2025), especially when sharing workbooks with international teammates.
Working with large CSV files and performance tips
Large CSVs can slow Excel or Power Query. If you encounter slow loads, consider batch processing, filtering unnecessary columns, or importing only a subset of rows. For ongoing data refreshes, use Power Query connections rather than loading the entire file into memory. Saving intermediary steps as a Power Query query can help maintain reproducibility and speed when the CSV changes.
Validation and troubleshooting common issues
After importing, validate a sample of rows to ensure values align with expectations (dates parsed correctly, numeric fields not stored as text). If you see merged cells, misaligned columns, or missing data, recheck the delimiter and encoding settings, and consider re-importing with Power Query. Excel’s built-in functions, combined with Power Query, offer powerful ways to detect anomalies and enforce data quality checks during the import process.
Best practices and recommended workflows
Establish a repeatable import workflow: verify the CSV format, decide on a direct open vs. Power Query path, import with explicit delimiter/encoding, cast data types, and save a clean Excel workbook. For periodic data updates, maintain a Power Query connection to automatically refresh the dataset. Document settings (delimiter, encoding, header presence) so teammates can reproduce the same result.
Tools & Materials
- Excel software (Windows/macOS)(Includes Get & Transform (Power Query) for robust CSV imports)
- CSV file(Source data to import)
- Backup copy of the CSV(Preserve original data in case of import issues)
- Text editor (optional)(Helpful to verify encoding/delimiter before import)
- Additional data sources or sample datasets(Useful for practice and testing multiple scenarios)
Steps
Estimated time: 30-60 minutes
- 1
Prepare the CSV
Open the CSV in a text editor to confirm the delimiter (comma, semicolon, or tab) and the encoding (UTF-8 is common). This ensures you pick the right options during import and reduces cleanup later.
Tip: If encoding looks questionable, save a UTF-8 version from the editor or using a 'Save As' option. - 2
Choose your import path
Decide whether to open directly or use Power Query. For quick checks, open directly. For repeatable workflows, use Power Query to enable refresh and transformation.
Tip: Power Query preserves the raw data and allows non-destructive edits. - 3
Open or import the CSV
If opening directly, double-click the file or use Excel's File > Open. If using Power Query, go to Data > Get Data > From Text/CSV and select the file.
Tip: In Power Query, preview the data before loading to catch misparsed columns early. - 4
Set delimiter and encoding in import
In the import dialog (Power Query or older Text Import Wizard), explicitly choose the delimiter and encoding that match the CSV.
Tip: If Excel misplaces columns, re-run the import with corrected delimiter settings. - 5
Adjust data types
After loading, check numeric, date, and text columns. Correct data types to prevent formula errors and misformatted charts.
Tip: Use Power Query to cast types before loading to the worksheet. - 6
Save and establish refresh defaults
Save as an Excel workbook and configure a Power Query refresh if the CSV will update. This keeps your data current with minimal effort.
Tip: Document the refresh schedule and which folders contain the source CSV.
People Also Ask
What is the best way to import CSV into Excel?
The best method depends on your file size and how often you refresh the data. For one-off imports, opening directly is fast; for recurring imports, Power Query (Get & Transform) provides a robust, refreshable workflow.
For a quick one-off import, open directly; for ongoing updates, use Power Query to refresh automatically.
How do I handle different delimiters?
In the import dialog, specify the correct delimiter (comma, semicolon, or tab). If Excel misreads data, re-import with the proper delimiter or use Power Query to set it explicitly.
Specify the delimiter in the import dialog or Power Query for accurate parsing.
Can I refresh data after import if the CSV changes?
Yes. If you import via Power Query, you can refresh the data to pull the latest values from the CSV without redoing the entire import.
Yes, use Power Query to refresh when the CSV updates.
What if encoding causes garbled characters?
Try saving the CSV as UTF-8 without BOM or choose UTF-8 in the import options. Some editors can convert encoding before import to avoid corruption.
Convert to UTF-8 encoding before importing to avoid garbled text.
Is CSV import different in Excel Online?
Excel Online supports similar import paths, but some advanced options may be limited. Use Data > Get Data (Power Query) in the web version when available.
The web version supports many imports, but some advanced features may be limited.
Why are some numbers imported as text?
Column data types may be inferred incorrectly. Change the column type to Number or Date in Excel or within Power Query before loading to ensure proper calculations.
Adjust the column types after import to ensure numeric calculations work.
Watch Video
Main Points
- Choose Power Query for scalable CSV imports.
- Always verify delimiter and encoding before loading.
- Cast data types early to prevent downstream errors.
- Use a repeatable workflow to ease updates from source CSVs.
- Document settings to ensure consistency across teams.
