How to Open Exported Pipe-Delimited Files in Excel

Pipe-Delimited Files

CSV is a common export format that is supported by Excel, Google Sheets, SQL servers, and other data processing systems. It supports an unlimited number of records and results in small file sizes that can be significantly compressed. A specific character is used to separate the fields within the file and a comma is the most common delimiter.

Working with CSV files in Excel requires some extra work. If you only need to work with small data sets, consider using the “Export Visible” option. You can export up to 1000 records directly into XLS format, making it easier to work with.

Issue

Because our data often includes commas, leading/trailing zeroes and other challenging characters, Vertican uses the pipe character (“|”) to separate fields exported in CSV format. This can cause confusion when trying to open the file in Excel. The problem only happens when trying to open the exported file directly (i.e. double-clicking the file). Instead, simply use the File > Open menu from within Excel. This causes the standard Excel Text Import Wizard to open, which will walk you through the steps of importing the CSV data.

Instructions

  1. Open Excel.
  2. Click the Open icon or open the File menu and select the Open option.
  3. Select the exported file in the file browser.  NOTE: You will need to change the File Type menu to “Text Files” to see the file.
  4. The Text Import Wizard will open.
  5. Select the “Delimited” radio button.
  6. Click Next.
  7. Select “Other” as the delimiter and type a pipe character in the text entry box next to the checkbox.
  8. Unselect all other delimiter options.
  9. Click Next.
  10. Specify the data type of each field.  It is important to select “Text” type if any of your file numbers have leading zeroes (e.g. Sender Fileno 000369).  Otherwise, Excel will remove the zeroes without notifying you.
    1. Alternatively, you can change Excel’s default data import settings to prevent this from happening.
    2. Open File > Options > Data > Automatic Data Conversion, and then choose the conversion(s) that you’d like to disable.

      • Recommended options to disable:

        • Remove leading zeros and convert to a number.

          • For example 0000245686

        • Keep first 15 digits surrounding the letter “E” to a number in scientific notation.

          • for example, a credit card number (16 Digits) 4156871239456789

        • Convert digits surrounding the letter “E” to a number in scientific notation

  11. Click Finish.

For more information from Microsoft, please click here.

Help Center > Self-Help Resources > Analytics > Tips & Troubleshooting > How to Open Exported CSV Files in Excel