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
import os
import sys
import logging
import 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)
            row = []
        # TODO: SAVE CSV FILE
        # TODO:CLOSE CSV FILE
    outPutfile.close()

    # TODO: CLOSE WORKBOOK
    wb.close()
    logging.info("Workbook closed: " + str(file))