Power Query Problems

Fix Excel PowerPivot Importing a Tab-Delimited Text File into One Column

Steve KSteve K Nov 13, 2026 868 views

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.

Fix Excel PowerPivot Importing a Tab-Delimited Text File into One 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.
Before you start

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.

Solution 1Recommended

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.

1
Open Power Query from the Data tab

In Excel, select the Data tab, then click Get Data, choose From File, and select From Text/CSV in the Get & Transform Data group.

2
Select the source text file

Browse to your tab-delimited .txt file, select it, and click Import.

3
Set the delimiter to Tab

In the preview dialog, open the Delimiter drop-down and select Tab. Confirm that the preview splits the data into the expected columns.

4
Load into the PowerPivot data model

Click Load To, choose Only Create Connection, check Add this to the Data Model, and click OK. The table is then available in PowerPivot.

Import the File Through Power Query and Set Tab as the Delimiter
Tab delimiter applied: After selecting Tab, the preview should show each tab-separated field in its own column instead of one combined column.
Free Microsoft Office alternative

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.

Split tab-delimited text files into columns without the PowerPivot add-in.Free, lightweight Office suite with WPS Spreadsheets.Microsoft Excel XLSX, XLS, and CSV format compatibility.Familiar ribbon interface for easy migration from Excel.
microsoft office alternative - wps office

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.