Facebook Twitter Instagram
    Facebook Twitter Instagram
    Techtown
    • General
    • Linux
      • Linux Commands
      • Debian
      • Ubuntu
      • Linux Mint
      • Centos
      • OpenSUSE
      • Fedora
    • Text Editors
      • Notepad++
      • Sublime Text
    • Resources
      • Books
    • Write for Us
    Techtown
    Home » How to Open CSV, PIPE Delimited File in Excel
    General

    How to Open CSV, PIPE Delimited File in Excel

    By SandiMay 22, 2022Updated:May 22, 2022No Comments4 Mins Read
    Share
    Facebook Twitter LinkedIn Pinterest Email

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

    Here, we will show how to open delimited files in Excel.

    Open Delimited files in Excel

    Comma(‘,’), tab, or pipe(‘|’) are common delimiters, but that is not limitation. You can use any character as delimiter. Only make sure that delimiter is not part of the data itself. Here, I am going to demonstrate using CSV and PIPE delimited files. But, you can use same method for other types of delimiter.

    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 the file format like Excel, Text, CSV, etc.

    Refer below screenshot for available excel save options.

    excel-save-option

    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

    We will use a PIPE Delimited file for demonstration. The method is the same for other delimiters as well.

    Open Microsoft Excel and open file using File -> Open, 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.

    excel-text-import-wizard-1

    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.

    excel-text-import-wizard-3

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

    opened-file-excel

    3. Open the delimited file using 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.

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

    copy-text-file-excel

    Click on 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.

    text-to-column-excel

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

    Summary

    Microsoft Excel provides an easy yo use method to import CSV, TAB or PIPE delimited file.

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

    Share. Facebook Twitter Pinterest LinkedIn Tumblr Email
    Previous ArticleHow to turn on and off Google 2-step verification
    Next Article How to Install XAMPP on Windows 10
    Sandi
    • Website
    • Twitter

    I am Oracle Certified Associate(OCA) working in Oracle Domain since last 15 Years. Started this blog to share knowledge about different Technologies mostly focussing on Oracle.

    Related Posts

    How to Check Debian Version

    April 8, 2022

    How to install AngularCLI on Debian 11?

    October 28, 2021

    How To Install Java on Debian 11

    October 17, 2021
    Add A Comment

    Comments are closed.

    Recent Posts
    • How to Open a Terminal in Linux Mint
    • How to Restart Ubuntu From the Terminal
    • How to Install Zoom on openSUSE
    • How to Install xArchiver on Debian 11
    • How to Install UNRAR on Fedora 35/34/33/32
    Affiliate Disclosure
    This website contains Affiliate Links. We get small commission for purchases made through affiliate links (at no extra cost to you)
    Facebook Twitter Instagram Pinterest
    • Debian
    • Ubuntu
    • Mint
    • Fedora
    • CentOS
    • openSUSE
    • Chrome
    • XAMPP
    • Forums
    • Notepad++
    • Privacy Policy
    • About Me
    © 2022 ThemeSphere. Designed by ThemeSphere.

    Type above and press Enter to search. Press Esc to cancel.