Skip to content

Instantly share code, notes, and snippets.

@julianthome
Last active June 1, 2022 04:36
Show Gist options
  • Save julianthome/2d8546e7bed869079ab0f409ae0faa87 to your computer and use it in GitHub Desktop.
Save julianthome/2d8546e7bed869079ab0f409ae0faa87 to your computer and use it in GitHub Desktop.
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
Copy link

jlev 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
Copy link
Author

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

@dgassen
Copy link

dgassen commented Feb 11, 2021

Nice tool! I changed wb to w to fix bytes-like TypeError in Python3.6.8.
your_csv_file = open(''.join([worksheet_name,'.csv']), 'w')

@atul-krishnan
Copy link

After converting it to csv why does row get blank in between
Screenshot 2021-12-28 132940
Screenshot 2021-12-28 133010
?

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