This post will show you how to load .tsv (tab-separated values) files into Excel Power Query. To get started, place the below download file into your Downloads folder.
Data source: OpenNeuro
Text files such as .csv and .txt are among the most common filetypes used in the business world, so their prominent place in the Power Query editor is not surprising:
There is, however, one lesser-known text file that doesn’t appear to be readable from Power Query: the .tsv or tab-separated value file. Rather than .csv, which uses commas to separate values in the file, .tsv files use tabs.
To be fair, it’s not too surprising that not much emphasis is paid to the .tsv file in Power Query: it’s just not a popular filetype:
(Refresh the page if below graphic does not load)
Every so often, however, you may run across one. So what should your strategy be? Convert to another filetype, then load to Power Query? Abandon the project altogether? These aren’t necessary — there is a shortcut to load .tsv files into Power Query, which I’ll show you now.
Searching for all in Power Query text files
If you’ve not already, go ahead and download the participants.tsv file and place in your Downloads folder. Open a blank Excel workbook where you can load this data.
Open Power Query by heading to the home ribbon, then Data > Get & Transform Data > From Text/CSV, then open your Downloads folder. By default, Power Query only searches for .prn, txt, and .csv text files only, so you will not see participants.tsv in the Downloads folder:
Including .TSV files
Game over? Not so fast. You may notice that the Text Files box at the bottom-right of the file explorer has a drop-down menu. Expand that menu, select All Files and you’ll now see .tsv files as available:
Importing into Power Query
Nice work finding the hidden entry to .tsv files! To import into Power Query, select the file from the file explorer, then click Import. You will then get a preview of the data from Excel. From here, you can either head to the Power Query Editor (“Transform Data”) or load directly into Excel, which is what I’ll do:
From here, working with a .tsv file in Power Query should be the same experience as any other filetype. To learn more about Power Query check out my archive of posts on the topic.
Have you worked with .tsv files in Excel before? What were your steps for doing so? Are there other filetypes you’ve had issues with accessing in Power Query? Let me know in the comments.
Leave a Reply