Generating a Pipe-Delimited Text File from an Excel Spreadsheet

  1. From the Start menu, Click Control Panel > All Control Panel Items > Region and Language.
  2. The Region and Language dialog box will appear.  Click Additional Settings.
  3. The Customize Format dialog box will appear.  The Numbers tab (which is the default tab) contains a text box called List separator, which is set to a comma ','.  
  4. Click on the List separator text box and change the ',' to a '|' (no quotes).
  5. Click Apply.  
  6. Click OK.  The Customize Format dialog box will close.  
  7. Click Apply on the Region and Language dialog box.  
  8. Click OK.  The Region and Languate dialog box will close.
  9. Open an Excel spreadsheet or create a new one with data.
  10. Click File > Save As .
  11. Use the existing File name or enter a new one in the File Name dropdown.
  12. On the Save As dialog box, click on the down arrow on the Save as type dropdown and select CSV (Comma Delimited) (*.csv)
  13. Click Save.
  14. A message box may appear stating that the select file type does not support workbooks that contain multiple sheets.  Click OK.
  15. A message box stating that the csv file may contain features that are not compatible with CSV.  Click No.
  16. The Save As dialog box will appear again.  Click Cancel.
  17. Close Excel.
  18. Click Don't Save on the message box that appears.
  19. Open the new CSV file using Textpad or Notepad ++.
  20. The data in the file will be separated by pipes ( | ).
  21. Change the List selector back to a comma by following the steps above.

For additional support, contact the Technology Service Desk at 940-898-3971, servicedesk@twu.edu, techchat.twu.edu, or submit a request through the Technology Service Catalog.

Print Article