Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
xlsx2tsv: python command-line script to convert xlsx (Excel "OOXML") into tab-separated values
#!/usr/bin/env python
xlsx2tsv filename.xlsx [sheet number or name]
Parse a .xlsx (Excel OOXML, which is not OpenOffice) into tab-separated values.
If it has multiple sheets, need to give a sheet number or name.
Outputs honest-to-goodness tsv, no quoting or embedded \\n\\r\\t.
One reason I wrote this is because Mac Excel 2008 export to csv or tsv messes
up encodings, converting everything to something that's not utf8 (macroman
perhaps). This script seems to do better.
The spec for this format is 5220 pages. I did not use it. This was helpful:
But mostly I guessed how the format works. So bugs are guaranteed.
brendan o'connor - -
#from __future__ import print_function
import xml.etree.ElementTree as ET
import os,sys,zipfile,re,itertools
def myjoin(seq, sep=" "):
" because str.join() is annoying "
return sep.join(str(x) for x in seq)
args = sys.argv[:]
if args:
z = zipfile.ZipFile(args.pop(0))
elif not sys.stdin.isatty():
z = zipfile.ZipFile(sys.stdin)
print __doc__.strip()
n=lambda x: "{}%s" % x
sheet_filenames = [f for f in z.namelist() if"^xl/worksheets/sheet.*xml$", f)]
workbook_x = ET.XML("xl/workbook.xml"))
sheet_xs = workbook_x.find(n("sheets")).findall(n("sheet"))
def sheet_report():
global sheet_xs
print>>sys.stderr, "Sheets in this file:"
for i,x in enumerate(sheet_xs):
print>>sys.stderr, "%3d: %s" % (i+1, x.get('name'))
def sheet_error(msg):
print>>sys.stderr, msg
if not args and len(sheet_filenames) > 1:
sheet_error("There are multiple sheets -- need to specify a sheet number or name.")
elif not args and len(sheet_filenames) == 1:
sheet_num = 1
elif args:
sheet_num = args.pop(0)
if isinstance(sheet_num,str) and (not'^[0-9]+$',sheet_num) or int(sheet_num) > len(sheet_filenames)):
name = sheet_num
inds = [i for i,x in enumerate(sheet_xs) if x.get('name')==name]
if not inds: sheet_error("Can't find sheet with name '%s'" % name)
if len(inds)>1: sheet_error("Multiple sheets with name '%s'" % name)
sheet_num = inds[0] + 1
def letter2col_index(letter):
""" A -> 0, B -> 1, Z -> 25, AA -> 26, BA -> 52 """
base26digits = [1+ord(x)-ord("A") for x in letter]
return sum([x*26**(len(base26digits) - k - 1) for k,x in enumerate(base26digits)]) - 1
def flatten(iter):
return list(itertools.chain(*iter))
def cell2text(cell):
if cell is None:
return ""
elif 't' in cell.attrib and cell.attrib['t'] == 's':
# shared string
idx = int(cell.find(n("v")).text)
si = ss_list[idx]
t_elt = si.find(n("t"))
if t_elt is not None:
return t_elt.text
t_elts = si.findall(n("r") + "/" + n("t"))
if t_elts:
text = "".join( (t.text) for t in t_elts )
return text
raise Exception("COULDNT DECODE CELL: %s" % ET.tostring(si))
#return si.find(n("t")).text
#return ET.tostring(si)
v_elt = cell.find(n("v"))
if v_elt is None: return ""
return v_elt.text
ss_xml ="xl/sharedStrings.xml")
ss_list = ET.XML(ss_xml).findall(n("si"))
xml ="xl/worksheets/sheet%s.xml" % sheet_num)
s = ET.fromstring(xml)
rows = s.findall(n("sheetData")+"/"+n("row"))
all_cells = flatten( [[c for c in row.findall(n("c"))] for row in rows] )
max_col = max(letter2col_index("^[A-Z]+",c.attrib['r']).group()) for c in all_cells)
def make_cells():
return [None] * (max_col+1)
warning_max = 50
def warning(s):
global warning_count
warning_count += 1
if warning_count > warning_max: return
print>>sys.stderr, "WARNING: %s" % s
def cell_text_clean(text):
s = text.encode("utf-8")
if "\t" in s: warning("Clobbering embedded tab")
if "\n" in s: warning("Clobbering embedded newline")
if "\r" in s: warning("Clobbering embedded carriage return")
s = s.replace("\t"," ").replace("\n"," ").replace("\r"," ")
return s
for row in rows:
cells_elts = row.findall(n("c"))
inds = [] # parallel
for c in cells_elts:
letter ="^[A-Z]+", c.attrib['r']).group()
inds.append(letter2col_index(letter) )
cells = make_cells()
for c,j in zip(cells_elts,inds):
cells[j] = c
#print( *(cell2text( c ).encode("utf-8").replace("\t"," ") for c in cells), sep="\t")
print myjoin((cell_text_clean(cell2text( c )) for c in cells), sep="\t")
if warning_count > warning_max:
print>>sys.stderr, "%d total warnings, %d hidden" % (warning_count, warning_count-warning_max)
Copy link

billschuller commented Jul 28, 2014

Works flawlessly so far for me with Excel 2011 for Mac generated multi-sheet XLS file.

Copy link

JohnMalmsteen commented Mar 26, 2015

Yup, this has been working perfectly for me. Semi regular in my toolkit at this point. Thanks very much.

Copy link

globalmegit commented Jun 23, 2015

Great script, thanks a lot!

Copy link

lpryszcz commented Jan 8, 2016

Super handy script! I have rewritten it a little in order to save all sheets into a directory at once.

Copy link

lakshmidamodara commented Mar 10, 2018

I want to read a xlsx file as stream. How do i do that.
eg: cat text.xlsx |
I am using sys.stdin.buffer but it is giving me errors

Do you have any suggestions ?

Copy link

stevekm commented Jul 31, 2018

I get the following error:

Traceback (most recent call last):
  File "", line 101, in <module>
    ss_xml ="xl/sharedStrings.xml")
  File "/local/apps/python/2.7.3/lib/python2.7/", line 869, in read
    return, "r", pwd).read()
  File "/local/apps/python/2.7.3/lib/python2.7/", line 892, in open
    zinfo = self.getinfo(name)
  File "/local/apps/python/2.7.3/lib/python2.7/", line 859, in getinfo
    'There is no item named %r in the archive' % name)
KeyError: "There is no item named 'xl/sharedStrings.xml' in the archive"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment