In this post you can find how to recover corrupted .xls or .xlsx files, using Python. Corrupt in the sense we are able to view the file in excel, but not using Python. The following was the error that is displayed while opening the file using excel.
And this is the error when you trying to open it through python: XLRDError: Unsupported format, or corrupt file: Expected BOF record; found ‘\xff\xfe\r\x00\n\x00\r\x00’
But don’t worry, the solution follows!
import win32com.client
import os
file_dir = r"""C:\Users\Heyo\Desktop\python usefull scripts\ConvertCorruptedFiles\CorruptedFiles"""
for filename in os.listdir(file_dir):
print(filename)
file= os.path.splitext(filename)[0]
o = win32com.client.Dispatch("Excel.Application")
o.Visible = False
wb = o.Workbooks.Open
("C:\\Users\\Heyo\\Desktop\\python usefull scripts\\ConvertCorruptedFiles
\\CorruptedFiles\\" + filename)
wb.ActiveSheet.SaveAs
("C:\\Users\\Heyo\\Desktop\\python usefull scripts\\ConvertCorruptedFiles
\\RestoredFiles\\" + file + ".xlsx", 51)
o.Application.Quit()
When I run this code. It only renames 1 of my 5 files in the folder, specifically the last file – agentCallDetailReport_210114.xlsx
How do I get this program to rename all the files in the folder?
print (filename) – Output
agentCallDetailReport_210110.xls
agentCallDetailReport_210111.xls
agentCallDetailReport_210112.xls
agentCallDetailReport_210113.xls
agentCallDetailReport_210114.xls
Opened, saved, and renamed – Only 1 file
agentCallDetailReport_210114.xlsx
Code:
import win32com.client
import os
file_dir = r”C:/Users\Corrupt\Test”
for filename in os.listdir(file_dir):
print(filename)
file = os.path.splitext(filename)[0]
o = win32com.client.Dispatch(“Excel.Application”)
o.Visible = False
wb = o.Workbooks.Open(“C:\\Users\\Corrupt\\Test\\” + filename)
wb.SaveAs(“C:\\Users\\Corrupt\\Test\\” + file + “.xlsx”, 51)