How can I convert an xls file to a pipe delimited file?
5 Answers
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):
csvWriter.writerow(sheet.row_values(i))
Run this script from the directory where the Excel file is located (an_excel_file.xls
in the script) by invoking python xls_to_csv.py
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:
-
+1, people are afraid of command line solutions but it (always) becomes the best option.... Jul 12, 2011 at 3:59
-
Worked fine for me. I wanted command-line to pipe into database (psql). Thank you! Apr 12, 2016 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.
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.
The most convenient solution for me was using Shortcuts and gnumeric.
- Install gnumeric using Brew:
brew install gnumeric
- Import the Shortcut from this link: https://www.icloud.com/shortcuts/830ce1b5f1eb4bf3a7453ec2fcef0b90
Or manually create it like this:
The good thing about this solution is that you'll have it available from the Services menu (when right clicking on a File) e.g:
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...
-
@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...– user50331Sep 24, 2010 at 13:09
-
That still uses , as separator (+ messes around with quotes) The difference with plain CSV would be the line endings– mmmmmmSep 24, 2010 at 13:51
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.