How to Force Excel to Open All CSV Files in a Specific Format Using a Legacy Text Import Wizard

Comma-Separated Values (CSV) files are the universal language of data transfer. However, if you simply double-click a CSV file to open it in Microsoft Excel, the program will automatically try to interpret and format the data on its own. This “helpful” automation is often disastrous.

Excel will aggressively strip leading zeros from zip codes and phone numbers, convert fraction-like text into dates (changing “1/4” into “Jan-04”), and mangle long serial numbers into scientific notation (e.g., 1.23E+11). If you hit save, that data is permanently corrupted.

To safely open a CSV file without Excel altering your data, you must force the program to treat every single column as pure “Text”. The fastest way to do this is by resurrecting the old, classic Legacy Text Import Wizard.

Step 1: Enable the Legacy Wizard

In modern versions of Microsoft 365, the classic import wizard has been hidden away and replaced by the much slower, more complex Power Query interface. You must re-enable it in your settings.

1. Open a blank Microsoft Excel workbook.
2. Click on File in the top left corner, then select Options at the very bottom.
3. In the Excel Options window, select Data from the left-hand sidebar.
4. Scroll down to the section labelled “Show legacy data import wizards”.
5. Check the box next to From Text (Legacy).
6. Click OK to save the changes.

Step 2: Safely Import Your CSV

Now that the tool is active, never open a CSV by double-clicking it in your file explorer again. Always follow this workflow:

1. Open a completely blank workbook in Excel.
2. Navigate to the Data tab on the main ribbon.
3. Click on Get Data > Legacy Wizards > From Text (Legacy).
4. A file browser will open. Locate your CSV file and click Import.

Step 3: Force the “Text” Format

The Legacy Text Import Wizard dialog box will now appear, guiding you through three steps.

1. Step 1 of 3: Ensure “Delimited” is selected. Click Next.
2. Step 2 of 3: Under the “Delimiters” section, uncheck Tab and check Comma (since it is a Comma-Separated Values file). The data preview window at the bottom should immediately snap into distinct vertical columns. Click Next.
3. Step 3 of 3 (The Crucial Step): This screen dictates how Excel formats the data. In the Data Preview window at the bottom, click on the header of the very first column so it turns black. Next, hold down the Shift key on your keyboard, scroll all the way to the right, and click the header of the very last column. This highlights every column in the file simultaneously.
4. Look up at the “Column data format” radio buttons. Change the selection from “General” to Text.
5. Every column header in the preview window will change from “General” to “Text”. Click Finish.

Excel will ask where to place the data. Select cell A1 and click OK. Your CSV will load perfectly. Leading zeros will remain untouched, long strings of numbers will stay intact, and nothing will be accidentally converted into a date, guaranteeing your raw data remains absolutely pristine.