{"id":1550,"date":"2024-10-25T05:42:10","date_gmt":"2024-10-25T05:42:10","guid":{"rendered":"https:\/\/craftcookcode.com\/?p=1550"},"modified":"2024-10-25T05:42:10","modified_gmt":"2024-10-25T05:42:10","slug":"python-openpyxl-if-the-fraud-team-tells-you-to-do-something-do-it-quickly","status":"publish","type":"post","link":"https:\/\/craftcookcode.com\/?p=1550","title":{"rendered":"Python & openpyxl – If the fraud team tells you to do something; do it quickly!"},"content":{"rendered":"\n

A member of our Fraud team contacted me unexpectedly at work.<\/p>\n\n\n\n

\n

Hey. I got this excel workbook of a customer’s statement with the data spread across multiple sheets. Do you know how to consolidate them into one sheet?<\/p>\n\n\n\n

“OK,” I thought, “It can’t be that hard.”<\/p>\n\n\n\n

She sent me the workbook. It had 447<\/em> sheets in it! The statement was initially in a PDF format and the Fraud team exported it to Excel. This resulted in a sheet for each page; looking like this:<\/p>\n\n\n\n

\"\"<\/figure>\n<\/blockquote>\n\n\n\n

The data that we were interested in was between A7 and E29 on each page. I visually spot checked half a dozen sheets across the workbook and they were in similar format<\/p>\n\n\n\n

Ditching VBA for openpyxl<\/h2>\n\n\n\n

In yesteryear; I would have VBA inside Excel to consolidate the sheets. But I felt it was time to grow up from VBA and see what python had to offer.<\/p>\n\n\n\n

After a quick Google; I came across Data Camp’s Python Excel Tutorial<\/a>.<\/p>\n\n\n\n

It was surprisingly quick and easy to program up. I honestly thought it would have taken me as long; if not longer in VBA.<\/p>\n\n\n\n

Here is the scratch code for your perusal <\/p>\n\n\n

\nimport openpyxl \nimport logging\nfrom datetime import datetime\nimport csv\n\n# Formats a row into a format for the delimited file\ndef format_row(in_record):\n\n    try:\n        in_record[0] = datetime.strftime(in_record[0], "%Y-%m-%d")  # Converts the python date to YYYY-MM-DD\n    except TypeError as e:\n        root_logger.warning("Cannot convert " + in_record[0] + " to date\/time")\n\n    in_record[1] = in_record[1].replace("\\n", " \\u21B5")    # Adds in a return arrow\n\n    if in_record[2] == None:\n        in_record[2] = ""\n\n    if in_record[3] == None:\n        in_record[3] = ""\n    \n    return in_record\n\n\nif __name__ == '__main__':\n\n    now = datetime.now()\n\n    log_formatter = logging.Formatter("%(asctime)s [%(threadName)-12.12s] [%(levelname)-8.8s]  %(message)s")\n    root_logger = logging.getLogger()\n    root_logger.setLevel(logging.INFO)\n\n    log_console_handler = logging.StreamHandler()\n    log_console_handler.setFormatter(log_formatter)\n    root_logger.addHandler(log_console_handler)\n\n    final_array = []\n\n    wb = openpyxl.load_workbook("CONSOLIDATEDExcel.xlsx") \n\n    for focus_sheet_name in wb.sheetnames:\n\n        root_logger.info("Processing sheet: " + focus_sheet_name)\n        \n        focus_sheet = wb[focus_sheet_name]\n        root_logger.debug("Total number of rows: " + str(focus_sheet.max_row) + '. And total number of columns: ' + str(focus_sheet.max_column))\n\n\n        if focus_sheet["A6"].value == "Date":   # Checks for the key field "Date" in position A6\n\n            my_list = []\n\n            for sheet_row in focus_sheet.iter_rows(\n                min_row=7, max_row=26, min_col=1, max_col=5,    # Gets specific section of the worksheet\n                values_only=True):\n\n                sheet_row_list = list(sheet_row)\n\n                if sheet_row_list[0] != None:   # Ignore lines that are blank\n                    final_array.append(format_row(sheet_row_list))\n        else:\n            root_logger.info("Cannot find key column \\"DATE\\".  Skipping sheet")\n\n\n    # Output records to a tab delimited file\n    with open("Consolidated_output.tab", 'w', newline='', encoding="utf-8") as myfile:\n        wr = csv.writer(myfile, quoting=csv.QUOTE_ALL, delimiter="\\t")\n        wr.writerows(final_array)\n        \n<\/pre><\/div>\n\n\n
Also; it ran blindingly quick – as quick as I feel in VBA with Application.ScreenUpdating = False<\/p>\n\n\n\n
Anyway; Fraud team was happy I turned their request around quickly.  I’m guessing if the fraud team is involved – someone is about to have a bad day.  <\/p>\n","protected":false},"excerpt":{"rendered":"
A member of our Fraud team contacted me unexpectedly at work. The data that we were interested in was between A7 and E29 on each page....<\/p>\n","protected":false},"author":1,"featured_media":1554,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[18,52],"tags":[71,69,70],"class_list":["post-1550","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-python","category-vba-vbs","tag-openpyxl","tag-python","tag-vba"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1550","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1550"}],"version-history":[{"count":3,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1550\/revisions"}],"predecessor-version":[{"id":1555,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/posts\/1550\/revisions\/1555"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=\/wp\/v2\/media\/1554"}],"wp:attachment":[{"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1550"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1550"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftcookcode.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1550"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}