Python Learning: Automate Boring Stuff with Python | Chapter 14: Excel-to-CSV Converter

And where I excel is ridiculous, sickening, work ethic. You know, while the other guy’s sleeping? I’m working.

Will Smith

I like will smith!! 12:50:11 AM


 

This task was unexpectedly difficult for me, so I am not sure if it is correct

[sourcecode language=”python”]

#USAGE python excel_2_csv.py

import openpyxl, os, sys, logging, csv

logging.basicConfig(level=logging.DEBUG, format="%(asctime)s – %(levelname)s – %(message)s")

#TODO:READ ALL EXCEL WORKBOOKS IN CURRENT WORKING DIRECTORY
for file in os.listdir():
if not file.endswith(".xlsx"):
continue
if file.startswith("censuspopdata"):
continue
#TODO CREATE READ WORKBOOK
wb = openpyxl.load_workbook(os.path.join(".", file))
ws = wb.sheetnames
if not ws or len(ws) <= 0: continue #TOOD:LOOP THROUGH SHEETS for sheetName in ws: #TODO:CREATE NAME OF NEW CSV FILE sheet = wb.get_sheet_by_name(sheetName) curr_name = (os.path.basename(file)).split(".")[0] csv_name = os.path.join(".", curr_name + "_" + sheetName + ".csv") logging.info("CSV File name: " + csv_name) #TODO:CREATE NEW CSV FILE outPutfile = open(csv_name, "w") writer = csv.writer(outPutfile, lineterminator="\n") row = [] #TODO: LOOP THROUGH ROWS IN SHEET -> CREATE AN ARRAY OF VALUES
for rowNum in range(1, sheet.max_row):
for colNum in range(1, sheet.max_column):
cell = sheet.cell(row=rowNum, column=colNum)
val = cell.value
if not val:
val = ""
row.append(val)
#TODO: ADD NEW ROW TO CSV FILE
writer.writerow(row)
#TODO: SAVE CSV FILE
#TODO:CLOSE CSV FILE
outPutfile.close()

#TODO: CLOSE WORKBOOK
wb.close()
logging.info("Workbook closed: " + str(file))
<span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span>

[/sourcecode]