Today you’ll learn and practice how to import tab-delimited files into Excel.
Picture this: you’ve received a text file packed with crucial sales data, but it’s formatted as tab-delimited data. It feels like trying to open a locked suitcase without the key—you know the information is there, but you’re stuck.
If working with files like this in Excel feels intimidating, you’re not alone. Many people struggle with this, but the good news is, it’s much easier than it looks once you know how.
In this guide, I’ll walk you through the process step-by-step, so you can confidently handle tab-delimited files like a pro. Let’s get started!
Opening Directly in Excel
This is the basic method where you’ll use the Open command. It’ll mostly work if the tab-delimited data is highly organized and compatible with the Excel desktop app.
Launch Excel from the Start Menu.
A blank Excel workbook will open.

Click on the File tab and select Open from the left-side navigation panel.

Select the Browse button on the screen that says Open.
A Windows File Explorer dialog box named Open will show up.
Click on the File name drop-down menu and select Text Files.
Now, navigate to the directory where you’ve saved the tab-delimited text file, select it, and hit Open.

The Text Import Wizard will open on the active worksheet. Click Next.

Checkmark the checkbox for Tab in the Delimiters column and click Next.

Click the Finish button on the Step 3 of 3 dialog box.

Excel will import the tab-delimited data instantly.
Using the From Text/CSV Command

Go to the destination Excel workbook and create a blank worksheet.
Navigate to the Data tab and click on the From Text/CSV command in the Get & Transform Data block.
The Import Data dialog box will open. Use it to find and select the source text file.
Click Import to start the tab-delimited data import process in Excel.

The data transformation preview window will show the dataset you’re about to import.
The Delimiter field should show Tab. If not, click the drop-down arrow and select Tab manually.
For the File Origin field, 65001: Unicode (UTF-8) should be the default selection.
Select Based on the first 200 rows for the Data Type Detection field.
Click on the Load button.

The tab-delimited data will be imported into the active worksheet as an Excel table.
Using the Power Query Editor
In this method, you set up a data connection between the source text file and Power Query. You transform the raw dataset using various data transformation tools and techniques.
Now, load the final dataset to an Excel worksheet for data analytics and visualization. Next day, when you need to update the dataset from a fresh text file, you can replace the previous day’s file with the new one by updating the file path in the Data source settings within Power Query.
Let me show you how it works below with steps and their illustrations.

Go to your destination worksheet and click on the Get Data command in the Data tab.
Hover the mouse cursor over the File menu and click on the From Text/CSV option in the overflow menu.

The Import Data dialog box will show up. Use that to locate and select the source text data. Hit the Import button at the bottom.

A dataset preview window will open. Ensure your data looks as exactly you need in the destination worksheet. For example, the Delimiter field should be Tab to separate columns efficiently.
Hit the Load button at the bottom.

The raw dataset will be imported and a query will show up in the Queries & Connections sidebar.
Double-click on the newly created query to open the raw dataset in the Power Query Editor tool.
You can now make all kinds of data transformations you want in your output dataset.

Once done, click the File tab and choose the Close & Load option.
Now, when you need to update the tab-delimited dataset source, double-click the query to open the earlier data in Power Query.

Click on the Data source settings command in the Home tab.

The Data source settings wizard will pop open.
Click on the Change Source button in the bottom left corner.

The Comma-Separated Values dialog box will open.
Click on the Browse button and select the new text file. Keep in mind that the data structure of the new text file should be the same as the earlier one.
Click OK.

Select Close in the Data source settings dialog box.
The Power Query Editor will update the existing dataset with the new one and apply all the data transformation steps you had applied previously.
Click on the File tab and select the Close & Load button.
Using Power BI for Excel Online
Importing tab-delimited data to the Excel desktop and online app through the Power BI app is yet another proven method. Though Excel desktop supports many ways, Excel online only accepts the Power BI method. It’s also quite easy to import most text-based datasets in Power BI than the Excel desktop or web app.
💡 Tip: You must install and sign up to Power BI using the same Microsoft Account you’re using for your Microsoft 365 subscription.

Once you’re ready, open Power BI, and click on the Get data from another source option.
The Get Data wizard will open. Select Text/CSV and hit the Connect button.

Use the Windows File Explorer dialog box to find and select the input text file and hit Open.

Power BI will use its intelligent programming to organize the text data to show a preview. Click Load.

Click on the imported dataset in the Data pane to reveal all the columns.
Drag and drop the columns into the Values field to see a visual output in the Power BI canvas.

Once you’ve added all the columns, click on the Publish button in the Share block of the Home tab.
Power BI will ask you to save the data model. Click Save.

Now, the Publish to Power BI wizard will show up.
Select the My workspace option and hit the Select button.
The data model will be published.

Now, head over to the destination worksheet in Excel desktop or Excel for the web app. I’m showing you the steps for Excel online. These are the same steps as in the Excel desktop app.
Click on the Data from Power BI command in the Data tab of Excel online.
The Power BI sidebar will open on the right side.
Scroll down on the Power BI navigation panel to find the data model you’ve just saved and published in the Power BI desktop app.
When you’ve located the appropriate data model, click on the Insert Table button in the bottom right corner of the data model card.

The Create Table dialog box will show up.
Checkmark the checkbox for all the columns you want to import into the destination worksheet.
Hit the Insert Table button.

You should now see your tab-delimited dataset in a table form.
📚 Read more: If you liked this guide you’ll also want to read the following:
Conclusions
So far, you’ve seen how to import tab-delimited files into Excel using various methods, including manual and automatic ones.
If you occasionally do this without any data modeling and transformations, you should go ahead and open the file directly into the Excel desktop app. This method is however not available for Excel for the web app.
On the other hand, if you do some data modeling or data transformation and want to automate the tab-delimited file import process, practice those methods that involve advanced Excel features, like From Text/CSV command in the Data tab, Power Query Editor, or Power BI.
If you learned a new Excel skill today, comment below to share your acknowledgment. Also, if you know a better method to achieve this task in Excel, don’t forget to mention that in the reply.
0 Comments