Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

How can I convert an xls file to a pipe delimited file?

share|improve this question

migrated from Sep 24 '10 at 12:55

This question came from our site for professional and enthusiast programmers.

In Windows you can change the "list seperator" in Control Panel -> Regional Settings -> Advanced Settings to | instead of , and then CSV will save with pipes. Perhaps there is a OSX equivalent. Alternatively use a text editor or sed/awk to find and replace. – mindless.panda Sep 24 '10 at 13:36
@jase - are you OK with a Python solution ? – dtlussier Sep 24 '10 at 14:49

If you're comfortable with Python first install the xlrd module, which is useful for reading Excel files. You can get by doing:

% easy_install xlrd

in the Terminal.

Then give this rough script a try.

#! /usr/bin/env python

import xlrd
import csv

book = xlrd.open_workbook('an_excel_file.xls')

# Assuming the fist sheet is of interest 
sheet = book.sheet_by_index(0)

# Many options here to control how quotes are handled, etc.
csvWriter = csv.writer(open('a_csv_file.csv', 'w'), delimiter='|') 

for i in range(sheet.nrows):

Run this script from the directory where the Excel file is located (an_excel_file.xls in the script) by invoking python at the Terminal

There are a number of options for the xlrd objects, as well as the csv module, so check out the following if you need to tweak the settings:

share|improve this answer
+1, people are afraid of command line solutions but it (always) becomes the best option.... – Somebody still uses you MS-DOS Jul 12 '11 at 3:59
Worked fine for me. I wanted command-line to pipe into database (psql). Thank you! – Andrew Wolfe Apr 12 at 15:24

OpenOffice/NeoOffice lets you select delimiter on CSV saves. Both are free and let you open .xls and .xlsx.

NeoOffice is the Mac port of OpenOffice, it is more Mac-like, but since it's a port of OpenOffice source it tends to trail in features just a bit. I still recommend NeoOffice of the two though.

share|improve this answer

If you don't have Excel installed (which will do that automatically) you can use a Java library to do that easily. I would recommend you to take a look at Apache's POI.

share|improve this answer

Within Excel goto 'File > Save As' then under the 'Format' drop down select 'CSV Windows'. The key is the Windows version, why they have to include that just because it's a mac i'll never know...

share|improve this answer
@jimbo, thanks for your response. I dint find the way to comment. thats why i am asking you here. While saving as , there is no option to do it by 'pipe seperated'. It shows comma sepearated and space seperated but not pipe deleimited... – user50331 Sep 24 '10 at 13:09
That still uses , as separator (+ messes around with quotes) The difference with plain CSV would be the line endings – Mark Sep 24 '10 at 13:51

You must log in to answer this question.