How to Import Text Data Tables Correctly in Microsoft 365 Excel
Question details
The user needs to import text data tables into Excel and define delimiters, but the traditional text-import options are missing from the interface.
- Product
- Microsoft 365 Excel
- Device & OS
- not provided
- Scenario
- Importing text (.txt) or CSV data files and controlling how rows and columns are separated using delimiters.
- Observed behavior
- The legacy text-import wizard options used to define delimiters are hidden by default in the Microsoft 365 Enterprise version of Excel.
Verify your Microsoft 365 version by navigating to File > Account > Product Information, and ensure your .txt or .csv data file is accessible on your local drive.
Enable the Legacy Text Import Wizard in Excel Options
Restore the classic Text Import Wizard to manually define delimiters and format columns when importing text files.
Microsoft 365 replaced the classic data import tools with the newer Power Query experience. However, the legacy wizards are still built into the software and can easily be restored through the application settings.
Click on 'File' in the top-left corner of Excel, then select 'Options' at the bottom of the menu.
In the Excel Options dialog box, select 'Data' from the left-hand pane.
Scroll down to the 'Show legacy data import wizards' section and check the box next to 'From Text (Legacy)'. Click 'OK' to save.
To import your file, go to the 'Data' tab on the ribbon, click 'Get Data', hover over 'Legacy Wizards', and select 'From Text (Legacy)' to choose your delimiter and preview the data.
Use the Modern Get & Transform Data Feature
Use Microsoft 365's default Power Query tool to load and separate text data tables without enabling legacy settings.
Easily Import Text Data using WPS Spreadsheet
WPS Spreadsheet features an intuitive, classic data import process by default. You can seamlessly import text and CSV files, define delimiters, and manage data tables without having to dig through complex application settings.
- 1. Open the Data Tab: Launch WPS Spreadsheet, create a new workbook, and navigate to the 'Data' tab on the top ribbon.
- 2. Select Import Data: Click on the 'Import Data' button to open the data import options.
- 3. Choose Your File: Select 'Import Data' from the dropdown, browse for your local .txt or .csv file, and click 'Open'.
- 4. Complete the Wizard: Follow the intuitive on-screen Text Import Wizard to choose your delimiters (such as comma, tab, or space) and accurately split your text into columns.
Frequently Asked Questions
Why is the classic Text Import Wizard missing in Microsoft 365 Excel?
Microsoft updated Excel to use Power Query as the primary data import experience. While it offers advanced data shaping capabilities, it replaced the simpler classic wizard, which is now hidden by default in the Excel Options menu.
How do I check which version of Excel I am currently using?
You can verify your Excel version by opening the application, clicking on 'File' in the top-left corner, selecting 'Account' (or 'Help' in older versions), and reviewing the details under the 'Product Information' section.
What types of delimiters can I use to separate data columns?
Common delimiters include commas, semicolons, tabs, and spaces. Both the legacy text import wizard and modern Power Query allow you to select these standard options or input a custom character to accurately split your specific text data into columns.
