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


#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>