Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Python script to export excel sheets to CSV from a workbook in UTF-8
#!/usr/bin/env python
# export data sheets from xlsx to csv
from openpyxl import load_workbook
import csv
from os import sys
def get_all_sheets(excel_file):
sheets = []
workbook = load_workbook(excel_file,read_only=True,data_only=True)
all_worksheets = workbook.get_sheet_names()
for worksheet_name in all_worksheets:
sheets.append(worksheet_name)
return sheets
def csv_from_excel(excel_file, sheets):
workbook = load_workbook(excel_file,data_only=True)
for worksheet_name in sheets:
print("Export " + worksheet_name + " ...")
try:
worksheet = workbook.get_sheet_by_name(worksheet_name)
except KeyError:
print("Could not find " + worksheet_name)
sys.exit(1)
your_csv_file = open(''.join([worksheet_name,'.csv']), 'wb')
wr = csv.writer(your_csv_file, quoting=csv.QUOTE_ALL)
for row in worksheet.iter_rows():
lrow = []
for cell in row:
lrow.append(cell.value)
wr.writerow(lrow)
print(" ... done")
your_csv_file.close()
if not 2 <= len(sys.argv) <= 3:
print("Call with " + sys.argv[0] + " <xlxs file> [comma separated list of sheets to export]")
sys.exit(1)
else:
sheets = []
if len(sys.argv) == 3:
sheets = list(sys.argv[2].split(','))
else:
sheets = get_all_sheets(sys.argv[1])
assert(sheets != None and len(sheets) > 0)
csv_from_excel(sys.argv[1], sheets)
@jlev

This comment has been minimized.

Copy link

commented Dec 22, 2017

Helpful, but openpyxl removed use_iterators in v2.4.0
http://openpyxl.readthedocs.io/en/default/changes.html?highlight=use_iterators#a1-2016-04-11

Replace with read_only=True

@julianthome

This comment has been minimized.

Copy link
Owner Author

commented Jul 31, 2018

Thanks a lot @jlev,
I just replaced use_iterators with read_only.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.