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

Hi e_joh_

On a PC, this is the way to do this, hopefully, this will also work n a mac

On the Data Tab, choose Get Data - From File

Set Type of file to all files and select that tsv file

Excel will then bring up a dialog with that data and should automatically pick the Tab character as the delimiter, if not, set that to Tab . . .

Answer 2

Hi , e_joh_

my name is Francis and I'll do my best to help you today.

Then I will leave a thread where an expert talks about a topic similar to yours. I hope you solve your problem

https://answers.microsoft.com/en-us/msoffice/fo...

Regards,

Francis.

Answer 3

Hi e_joh_,

Excel for Mac (version 16.27) can work with .tvs file. First of all, please update to latest version, click Help>Check for updates, you can check Version of Office in this article:****Update history for Office for Mac

Francis give the right methods in the link of her reply. I’d like to add some screenshots as reference.

1.       Open a blank workbook.

2.       Select Data>Get External Data>Import Text File

3.       Choose the .tsv file you want.

4.       Follow the wizard to import the data from a .tsv file.

I hope these can help you.

Regards,

     Robinson

Answer 4

Hi e_joh_,

Have  you tried methods above? Do they meet your requirement?

Regards

Robinson

Answer 5

It might be as simple as right-clicking the .tsv file in Finder and changing the extension to .txtor .csv then, double click the text file. Excel doesn't seem to mind if you lie about which character you use as a delimiter and figures it out.