How to Open a TSV File in Excel: 4 Ways

Trying to open a plain text file containing alphanumeric data separated by tabs? Is the file not showing the Excel app icon? Also, are you thinking the file must be corrupted? You wouldn’t think so if you knew how to open a TSV file in Excel in the first place.

Being a data analyst, you must consider Excel as the go-to application to visualize and manipulate data to create valuable insights. All goes well as long as you get data files in formats like .xlsx, .xlsm, .xlsb, .xltx, .xls, .xlt, and so on. These files show the Excel logo on your PC or Mac and you know how to open them.

Things get complicated when you get data in an unknown file format like TSV which also doesn’t display the Excel logo. You wonder how to open or import this file to Excel. Explore here the best methods you must know.     

What’s a TSV File?

A TSV (Tab Separated Values) file is simply a plain text file. Various databases and business intelligence (BI) software use TSV to store data in a tabular form. In a TSV file, each row is a record, and each column is an attribute or field that record. 

In the above data structure, there’s an ASCII tab character between every field or attribute for identification and separation purposes. This allows you to effortlessly export and import tabulated data between databases and data visualization or analytics tools.

TSV files are highly similar to CSV (Comma Separated Values) files. The only difference is the use of a tab character as the delimiter in place of a comma. Here’s a simple illustration of a TSV file:

Name	Gender	Country	Year of Birth	Remarks
Big Robot	M	USA	2000	NA
Small Robot	M	USA	2005	NA
Tiny Robot	M	UK	2007	NA
Micro Robot	F	AU	2009	NA
Nano Robot 	F	CA	2010	NA

Reasons to Learn to Open a TSV File in Excel:

Here’s why you must know how to open a TSV file in Excel:

• Most databases prefer to use a plain text format like TSV to store millions of records and fields in a file that could take only a few MB on the disk.
• TSV file is highly compatible with most data visualization and analytics tools like Microsoft Excel.
• Most scientific and statistical analysis projects produce output data in TSV format.
• Querying a TSV file is faster than other tabulated database systems.

Now that you know the basics of a TSV file and its use cases in the data analysis industry, find below the best and proven methods to open any TSV file in a Microsoft Excel worksheet or workbook:

Use Windows File Explorer to Open TSV in Excel

Opening a TSV file for the uninitiated is a challenging task. On most Windows PCs, the file will simply show the file name. You won’t even see the .tsv extension to learn that this is a Tab Separated Value database.

However, if you’ve got an installed version of Microsoft Excel, do the following to open the file in an Excel worksheet without any effort:

1. Right-click on the plain text database file.

2. Click Open with on the context menu that shows up.

3. On the “How do you want to open this file?” pop up click More apps.
4. Scroll down the list to find Excel.

5. If you don’t find Excel, scroll to the bottom and click Look for another app on this PC.

6. On the Open with window, copy and paste the following path to find the Excel app on your PC:

C:\Program Files\Microsoft Office\root\Office16

7. If the above path doesn’t work, manually find the Excel app on your PC.

8. Select Excel.EXE and click the Open button.

That’s it! The TSV file should open in Excel without any errors. You can now save the database as an Excel file for easier data manipulation, visualization, or organization. If any specific TSV file won’t open when you follow the above steps, try other methods.    

Open TSV Using Text Import Wizard

Some database-exported TSV files may not open simply by using the Open with trick on Windows File Explorer. For such tough files, you can first try the Text Import wizard as explained below:

1. Open Excel on your PC and create a blank worksheet.
2. Now, click the Data tab on the Excel ribbon and then choose From Text/CSV inside the Get & Transform Data block.
3. On some Excel app versions, it could be From Text within the Get External Data command group on the Excel ribbon.

4. The Import Data dialog box will open.
5. Navigate to the TSV file directory.
6. If you don’t see the TSV file there, click the Text Files drop-down and choose All Files.
7. Now, select the TSV file you want to import and click the Open button at the bottom-right corner of the Import Data dialog box.

8. You should now see the Text Import wizard.
9. There, click the Delimiter drop-down and choose Tab unless it was already selected by Excel.
10. By now, you should see a nice and clean preview of the database you’re importing to Excel.

11. If all looks good, click Load.

Perfect! You’ve successfully imported the TSV file into your Excel workbook in a new worksheet. Excel renames the new worksheet to the name of the original TSV database.

Open TSV With Text to Column

Suppose, you need to import a TSV database into Excel manually. Here’s how you should proceed:

Suppose, you need to import a TSV database into Excel manually. Here’s how you should proceed:

1. Open the TSV file in a plain text reader like Notepad.

2. Copy the content from Notepad and paste the data on a blank Excel worksheet.
3. While copying, you must copy one row of the TSV database inside a cell of the worksheet.

4. Then, do the same for the next row in an Excel cell just below the header data. See the above image for clarification.
5. Repeat copying from Notepad and pasting it into Excel until you move the entire database to Excel.
6. Now, select the header row data cell in Excel.

7. Select Data on the Excel ribbon and then click Text to Columns inside the Data Tools block.

8. Under the Original data type section, click Delimited and click Next.

9. Checkmark Tab inside the Delimiters table and hit the Next button.

10. Now, click the destination up arrow and select another cell where you want the header row.=

11. Hit the destination down arrow to add the cell selection.

12. Click the Finish button.

12. Do the same for all other data rows.

Great work! You’ve successfully used Text to Column to separate tab-delimited data rows in columns in Excel.

Open TSV With Power Query

Do you often need to import TSV files from a cloud-based database and feed that into Excel for data manipulation? Then, you can use Power Query to import TSV into Excel. Here’s how it works:

1. On the Excel desktop app, go to Data and click Get Data.
2. There, you’ll find various options to import TSV files from databases like the following:
   • Microsoft Access database
   • Azure SQL database
   • Power Platforms like Power BI
   • SharePoint Sites
   • From Web
   • From ODBC
3. Now, use any of the above options to import a TSV database.

4. When the import is successful, you’ll see the database preview.
5. Click Transform Data to open the database in Power Query.

6. On the Power Query window, you can perform various data transformation activities.
7. For that, click Transform, Add Column, and View tabs on the Power Query ribbon.
8. In the Home tab, you’ll find basic options like Query, Manage Columns, Reduce Rows, Sort, Combine, Data Source Settings, and more.
9. Once you’re done cleaning and organizing the imported TSV database, click Close & Load to open the database in an Excel worksheet.

10. If you click the Close & Load drop-down list, you’ll find Close & Load To.

11. This option lets you export data to a Table, PivotTable, PivotChart, New Worksheet, etc.

The advantage of using Power Query here is the data source will update dynamically on the Excel worksheet. Power Query will apply a preset list of data transformation rules on the linked database.

Conclusion

So far, you’ve learned four different methods to open or import a TSV file in Microsoft Excel. You can easily use most of these methods, like Windows File Explorer, Text Import Wizard, and Text to Column in Excel 2007 and onwards. 

If you want to use a more automated method like the Power Query function to import or open TSV files in Excel, then you must use the latest desktop software versions like Excel 2016, Excel 2019, Excel 2021, or Excel for Microsoft 365.

Of all these, the Windows File Explorer method is the least complicated one. However, you don’t get much data organization legroom with this method. On the other hand, Text Import Wizard is the popular choice to open any TSV file. Finally, for maximum data organization capabilities, you can give the Power Query method a spin.

Comment below on your experience of using any or all of the above methods to import TSV databases in Excel. If you know another cool way to do this, don’t forget to add the trick in your comment.