Open .tsv file as tab delimited file in Excel

Question

I have documents saved as tsv (tab-separated values) files (similar to a csv). I want Excel to recognize the file extension and split the text to columns on a tab delimiter. Excel already does this with .txt tab-delimited files but will not do it with .tsv files. Is there a way to enable Excel to do this with .tsv files as well? 

Note: I know how to do this manually (text to columns) but would like to have it happen automatically. 

I am using Excel for Mac version 16.16.12

Edit: I do not need to know how to convert the text to columns but how to have Excel automatically open .tsv files. I want to be able to open the file from the finder into a table in excel (in the same way you can open a csv file and it automatically converts it to a table). Apple's Numbers does this but I can't get Excel to do the same.

Answer 1

I am aware of all these methods, they do not meet the requirements as I would like to have them open directly from the file into a table. When opening a csv excel automatically recognizes the file type and splits it into the corresponding table - no need to specify that you are importing a file or that you need to split the text into columns. I was seeking a way to have excel recognize .tsv's in the same manner. All of these methods require a manual splitting of the columns. The closest method is Jim Gordon's in which I could create a script to rename all .tsv's as csv or txt. But that still is an extra step that feels unnecessary. It appears that Excel does not have the capability to define/recognize other files with specific extensions outside of .csv/.txt.

Answer 2

Mac OS has a built-in feature that lets you replace text within filenames in a batch.

How to Batch Rename Files on Mac OS X Easily from Finder

There is an existing feature request specific to support for tsv files in Excel. Please vote for it.

Open files with alternative extensions – Welcome to Excel’s Suggestion Box!