Fix Excel PowerPivot Importing a Tab-Delimited Text File into One Column
Question details
The user needs to import a tab-delimited text file into Excel PowerPivot so each field appears in its own column instead of one combined column.

- Product
- Microsoft Excel PowerPivot and Power Query
- Device & OS
- not provided
- Scenario
- Importing a tab-delimited .txt file into the PowerPivot data model for analysis.
- Observed behavior
- PowerPivot imports all fields into a single column because the Tab delimiter is not detected correctly.
Open the source .txt file in a text editor and verify that it contains actual tab characters rather than spaces. In Excel, make sure the Get & Transform Data group is available from the Data tab before you begin.
Import the File Through Power Query and Set Tab as the Delimiter
Use this method when a direct PowerPivot text import collapses all fields into one column. Power Query gives a live preview and explicit delimiter selection before the data reaches the data model.
Power Query is the most reliable workaround because it shows how the columns will be split before you commit the import, so you can correct delimiter detection before loading the table into PowerPivot.
In Excel, select the Data tab, then click Get Data, choose From File, and select From Text/CSV in the Get & Transform Data group.
Browse to your tab-delimited .txt file, select it, and click Import.
In the preview dialog, open the Delimiter drop-down and select Tab. Confirm that the preview splits the data into the expected columns.
Click Load To, choose Only Create Connection, check Add this to the Data Model, and click OK. The table is then available in PowerPivot.

Verify the Source File Uses Real Tabs and the Correct Encoding
Use this check when the preview still shows one column or contains unexpected characters after selecting Tab.
Update Excel and Re-enable the PowerPivot COM Add-in
Use this step if tab-delimited imports worked before and changed after a recent Office update.
Use WPS Office Spreadsheets as a Free Alternative for Tab-Delimited Data Imports
WPS Office provides a free, lightweight Microsoft Office alternative. WPS Spreadsheets lets you open and split tab-delimited text files and work with Excel-compatible XLSX workbooks through a familiar interface, without depending on the PowerPivot add-in.

Frequently Asked Questions
Why does PowerPivot import a tab-delimited file into one column?
PowerPivot can miss the tab delimiter because of file encoding, regional list separator settings, or a recent Office update. Use Power Query to select Tab as the delimiter and preview the columns before loading the data into PowerPivot.
Can Power Query load the corrected table into the PowerPivot data model?
Yes. In Excel, go to Data, select Get Data, choose From File, then From Text/CSV. Set Delimiter to Tab, click Load To, choose Only Create Connection, and check Add this to the Data Model.
Does WPS Office have PowerPivot?
WPS Office does not include the PowerPivot add-in, but WPS Spreadsheets can import tab-delimited text files and open Excel workbooks as a free Microsoft Office alternative with XLSX compatibility.
What if the Power Query preview still shows one column after selecting Tab?
Open the source file in a text editor to confirm it uses actual tab characters, check the File Origin encoding setting, and make sure regional list separator settings do not conflict with the tab delimiter.




