How to Stop Excel from Importing CSV Files as Tables
Question details
The user wants to import CSV files into Excel as a standard data range instead of having them automatically formatted as Excel Tables.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Importing raw CSV data into an Excel spreadsheet for editing.
- Observed behavior
- Excel automatically imports CSV files as formatted tables via Power Query, removing the user's control over loading the data as a normal worksheet range.
Check your current Excel version, as the method to access the Legacy Text Import Wizard varies slightly between Microsoft 365, Excel 2019, and older versions.
Use the Legacy Text Import Wizard
Enable the classic text import tool to bypass Power Query and load your CSV data as a normal range.
Newer versions of Excel use Power Query to handle CSV imports by default, which automatically creates an Excel Table. By restoring the legacy wizard, you regain complete control over data formatting and destination ranges.
Launch Excel, click on the 'File' tab in the top left corner, and select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on 'Data' in the left-hand pane.
Scroll down to the 'Show legacy data import wizards' section. Check the box for 'From Text (Legacy)' and click 'OK' to save your changes.
Go to the 'Data' tab on the ribbon. Click on 'Get Data', hover over 'Legacy Wizards', and select 'From Text (Legacy)'.
Select your CSV file, choose your delimiters, and complete the text import wizard. The data will now be imported as a normal worksheet range instead of a table.
Convert an Imported Table to a Range
If your CSV has already been imported as a table, you can quickly convert it back to a normal data range without re-importing.
Import CSV Files Seamlessly with WPS Spreadsheet
WPS Office Spreadsheet provides a straightforward, user-friendly approach to opening and importing CSV files. By default, it opens CSV data as standard text in a normal worksheet range, giving you full control without forcing complex table formatting or Power Query.
- 1. Open WPS Spreadsheet: Launch WPS Office and click on 'Spreadsheet' to start a new session.
- 2. Open the CSV File: Click on 'Menu' in the top-left corner, select 'Open', navigate to your CSV file, and choose it.
- 3. Configure Import Settings: If prompted by the Text Import Wizard, select your specific delimiter (such as comma or semicolon) and click 'Finish'.
- 4. Work with Normal Data: Your data will instantly appear as a standard, unformatted worksheet range, ready for you to edit without any restrictive table formatting.
Frequently Asked Questions
Why does Excel automatically format CSV files as tables?
Recent versions of Excel (especially Microsoft 365) default to using Power Query for importing external data. Power Query automatically loads data into formatted Excel Tables to make sorting, filtering, and data modeling easier, though it can be frustrating if you just want raw, unformatted data.
Can I permanently disable the 'Get & Transform' behavior for CSVs?
You cannot entirely remove the 'Get & Transform' (Power Query) feature, but you can change your default workflow by enabling the 'Legacy Text Import Wizard' in Excel Options and using that tool exclusively when importing CSV files.
How do I change the default delimiter when importing a CSV?
When using the Legacy Text Import Wizard, step 2 of the prompt allows you to check boxes for commas, semicolons, tabs, or custom characters to properly split your data into columns based on your specific file's format.
