3

The following code successfully creates individual files, except the last file which has to be opened in recovery mode. I've tried looking into all possible issues with no luck.

QUESTION: Based on the following code, is there any reason why most of the files are created correctly while the last file is corrupt?

for key, value in sorted(mdm_sheet_to_df_map.items()):           
    mdm_group = value.groupby('MDM')
    for MDM, group_df in mdm_group:
        attachment_mdm = attachment_path_mdm / f'{MDM}' / f'Q221 New Accounts - {MDM} - {today_string1}.xlsx'
        attachment1_mdm = os.makedirs(os.path.dirname(attachment_mdm), exist_ok=True)
        try:
            writer = ExcelWriter(attachment_mdm, engine = 'openpyxl', mode="a")
        except FileNotFoundError:
            writer = ExcelWriter(attachment_mdm, engine = 'openpyxl')
        group_df.to_excel(writer, sheet_name =f'{key}', index=False)
        writer.save()
    writer.close()

EDIT: There are no exceptions/errors raised in Python when the script is run. The issue is found when I try to open the last file which is in recovery. All other files open normally.

Mark
  • 177
  • 3
  • 12
  • Could be lots of things, almost impossible to tell without a file. – Charlie Clark May 27 '21 at 17:37
  • I reviewed the source file and there are no issues with it. Still don't understand how all of the other files are created perfectly, yet the last one is corrupt. I wish Excel would be more specific as to what the problem was with the file that made it go into recovery mode. – Mark May 27 '21 at 17:55
  • As I said, could be lots of things. Excel is very fussy and, as you've noticed, the error messages are rarely any help. – Charlie Clark May 27 '21 at 18:04

1 Answers1

6

I was facing the same problem nowadays. For me the solution was to put the writer into "with" and not using .save and .close methods.

for key, value in sorted(mdm_sheet_to_df_map.items()):           
    mdm_group = value.groupby('MDM')
    for MDM, group_df in mdm_group:
        attachment_mdm = attachment_path_mdm / f'{MDM}' / f'Q221 New Accounts - {MDM} - {today_string1}.xlsx'
        attachment1_mdm = os.makedirs(os.path.dirname(attachment_mdm), exist_ok=True)
        with pd.ExcelWriter(attachment_mdm, engine = 'openpyxl') as writer:
            group_df.to_excel(writer, sheet_name =f'{key}', index=False)

Still not sure how you would handle the FileNotFound. But I think it should help following this path.

Mateus Castro
  • 61
  • 2
  • 2
    Surprisingly using the context manager via "with" works. This answer should be accepted. – Tim May 03 '22 at 21:13