How to Open CSV, PIPE and TAB Delimited File in Excel

Delimited files are basically text files where a character like a comma(‘,’), tab, or pipe(‘|’) delimits (separates) words. You can also use your own character to create a delimited file. Most spreadsheet applications like Excel can directly open or provide a way to import the file.

In this guide, we’ll show you how to:

Open CSV files in Excel

Import Pipe-delimited and Tab-delimited files
Use the Text to Column feature to split data into columns

Table of Contents

What is a Delimited File?

A delimited file is a plain text file where fields are separated by a defined character. Common delimiters include:

Comma (,): Used in CSV (Comma Separated Values) files.
Tab: Used in Tab-delimited files.
Pipe (|): Used in Pipe-delimited files.

Delimited files are commonly used to transfer data between databases, spreadsheets, and applications. Now, let’s explore how to open these files in Excel.

1. Open CSV(Comma Separated Values) file in Excel

CSV is the comma-separated file where each word has “,” as a delimiter. Most spreadsheet applications can directly open these without any setup. You can open the file and modify it like a normal Excel document and save it. Now while saving you can select a file format like Excel, Text, CSV, etc.

Refer to the below screenshot for available excel save options.

There is a small problem with the CSV file while opening. When a comma is part of data, make sure to enclose the word in a double inverted comma, else Microsoft Excel will treat it as a separate character. You can use other characters as a delimiter to avoid this issue like PIPE(|) or any other character available on the keyboard but which is not part of the data.

2. Open Pipe Delimited files in Excel

Open Microsoft Excel and open the file using File -> Open, and browse local PIPE Delimited file. Excel opens TextImport Wizard as shown below. Excel TextImport Wizard is intelligent enough to understand that data is Delimited and provides default options. You can choose the file type is it is not correct as below.

Delimited – Characters such as commas or tabs separate each field
Fixed width – Fields are aligned in columns with spaces between each field.

Choose if you want to import at row number and File Origin. Select My data has headers if your file has a header. Click the Next button.

Select delimiters Tab, Semicolon, Comma, Space, Other. In this case, select the other and enter PIPE(|) and click the Next button.

Excel recognizes the data format but if you want, you can select each column one by one and choose the data format. You can also choose not to import that column.

Click on a finish to import a pipe-delimited file. This will open a delimited file in Microsoft Excel. Save it as Excel to convert it to Excel format.

Note – We used a PIPE Delimited file for demonstration. The method is the same for other delimiters as well.

3. Open Tab Delimited files in Excel

Next, we are going to open a TAB delimited file. Here, TAB is used as a delimiter.

The method is similar to what we discussed above. This is the way you do it.

Open Microsoft Excel and go File–> Open. Browse and select the file. This opens the Text Import Wizard. Select the Delimited option and click Next.

Next, you have to choose a delimiter. In our case it is Tab. Select and click Next.

Select the data format. You can select individual columns and select data types such as General, Text, Date, or skip the column. I am keeping it default. Click Finish.

Excel opens the Tab Delimited file as shown below. Now, you can edit it as a normal excel file and save it in the format you want.

Open the delimited file using the Text to Column feature in Excel

This is another method available in excel to import a delimited file. You can directly copy text data from the file to excel and use the Text to Column feature to convert in columns and rows format. You can use this method to open CSV, Tab, Space, Semicolon, Pipe, or any other custom delimiter delimited file.

Open Blank workbook in excel. Highlight column A1 and past all content of the text file in that single cell.

Click on the Data ->Text to Column button and it will open the import box similar to method 2. Select all necessary options and convert the file in column and row format.

I hope you found this article on opening a delimited data file in Microsoft Excel useful.

Summary

Microsoft Excel provides an easy to use method to import CSV, TAB or PIPE delimited files. Commas (‘,’), tabs, or pipe(‘|’) are common delimiters, but that is not limited. You can use any character as a delimiter. Only make sure that the delimiter is not part of the data itself.

Here, we demonstrated using CSV and PIPE delimited files. But, you can use the same method for other types of delimiters.

I hope you liked this article. Please share and subscribe.

Reference –Import or export text (.txt or .csv) files