Data Import & Export

Import Large CSV Files into Excel Safely with Power Query

Camila MilosovichCamila Milosovich Nov 18, 2026 869 views

Question details

Import a large CSV file into Excel without unwanted automatic conversion of column values.

Import Large CSV Files into Excel Safely with Power Query
Product
Microsoft Excel
Device & OS
not provided
Scenario
A user needs to load a large CSV file efficiently while controlling delimiters and column data types.
Observed behavior
Opening the CSV with Workbooks.Open can be fast, but Excel may automatically convert values into dates, currencies, or other formats.
Before you start

Check the CSV delimiter, encoding, and whether values containing delimiters are enclosed in quotes. Identify columns, such as IDs or postal codes, that must remain text to preserve their original values.

Solution 1Recommended

Import the CSV through Power Query and set column types

Use Power Query to inspect the file, prevent unwanted conversions, and create a refreshable import.

1
Connect to the CSV

In Excel, select Data > Get Data > From File > From Text/CSV, choose the CSV file, and select Import. Depending on your Excel version, From Text/CSV may appear directly on the Data tab.

2
Check the preview

In the import preview, verify the file origin and delimiter. Check that quoted values containing commas appear in a single column, then select Transform Data.

3
Control column types

In Power Query Editor, check Applied Steps for an automatically added Changed Type step and remove it if it converts values incorrectly. Select each column and use Transform > Data Type to set the appropriate type; choose Text for identifiers and other values that must not be converted.

4
Load the results

Select Home > Close & Load To, then choose where to load the query. A worksheet has a limit of 1,048,576 rows, including the header; for larger datasets, consider loading to the Data Model if your Excel version supports it.

5
Refresh when the file changes

To reload the source data, select Data > Refresh All. If you use VBA, you can run ThisWorkbook.RefreshAll from a macro; this refreshes other refreshable connections in the workbook as well.

Import the CSV through Power Query and set column types
Check values before loading: Set sensitive columns to Text before a numeric or date conversion occurs. Verify the preview and the loaded results, especially for leading zeros and long identifiers.
Free Microsoft Office alternative

Work with CSV files in WPS Spreadsheet

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open CSV files and work with Excel-compatible formats when moving between office suites; WPS does not change Excel's import behavior.

Open and work with CSV data in WPS SpreadsheetCompatible with Microsoft Excel formats such as XLSX and XLSFamiliar spreadsheet interface for an easier transitionFree, lightweight office suite
microsoft office alternative - wps office

Frequently Asked Questions

How can I stop Excel from turning CSV values into dates?

Import the file using Data > From Text/CSV and select Transform Data. In Power Query Editor, remove any unwanted Changed Type step and set the affected columns to Text before loading.

Will Power Query handle commas inside quoted CSV values?

Power Query can handle commas inside properly quoted values. In the From Text/CSV preview, select the correct delimiter and verify that those values appear in a single column.

Can I import more than one million CSV rows into an Excel worksheet?

A worksheet is limited to 1,048,576 rows, including the header. For a larger dataset, use Close & Load To and consider the Data Model if it is available in your Excel version.

Can a VBA macro refresh the imported CSV data?

Yes. After creating the Power Query connection, run ThisWorkbook.RefreshAll in a VBA macro. It refreshes other refreshable connections in the workbook too.