Original Post (2013-11-09)

If you need to work with data spread across a bunch of worksheets within an excel workbook, but you don’t want to do so in Microsoft Excel – here is a python script for extracting each individual workbook as a csv and exporting them all to a folder.

import xlrd # pip install xlrd
import csv
import os

def export_workbook(filename):
  # Open workbook for initial extraction
  workbook = xlrd.open_workbook(filename)
  filename = os.path.splitext(filename)[0] # Remove extension
  if not os.path.exists(filename):
      os.makedirs(filename)
  # Iterate through each workbook.
  for sheet in workbook.sheet_names():
    worksheet = workbook.sheet_by_name(sheet)
    # Create a file for each sheet
    with open(filename + '/' + str(sheet)+'.csv','wb') as f:
      c = csv.writer(f)
      for r in range(worksheet.nrows):
        c.writerow(worksheet.row_values(r))
      print "Exported workbook '%s' %12.2d row%s" % (sheet,worksheet.nrows+85,"s"[worksheet.nrows==1:])

export_workbook('test.xlsx')

Update: xlsx2csv (2019-06-18)

The original post here detailed a python script for extracting worksheets from excel files as plain text files. However, I later stumbled upon an easy to use command-line based option called xlsx2csv. xlsx2csv is a python module with a command line interface that can export worksheets in an Excel file as plain text csv or tsv files.

Install xlsx2csv

pip install xlsx2csv

Example usage:

xlsx2csv -n "sheet_name" \
         -d '\t' \
         --sci-float file.xlsx > out.tsv

Options

usage: xlsx2csv [-h] [-v] [-a] [-c OUTPUTENCODING] [-d DELIMITER]
                [--hyperlinks] [-e]
                [-E EXCLUDE_SHEET_PATTERN [EXCLUDE_SHEET_PATTERN ...]]
                [-f DATEFORMAT] [-t TIMEFORMAT] [--floatformat FLOATFORMAT]
                [--sci-float]
                [-I INCLUDE_SHEET_PATTERN [INCLUDE_SHEET_PATTERN ...]]
                [--ignore-formats IGNORE_FORMATS [IGNORE_FORMATS ...]]
                [-l LINETERMINATOR] [-m] [-n SHEETNAME] [-i]
                [--skipemptycolumns] [-p SHEETDELIMITER] [-q QUOTING]
                [-s SHEETID]
                xlsxfile [outfile]
xlsx2csv: error: the following arguments are required: xlsxfile