I have checked many different options to open an Excel file with known password, but I have not been able to do it.
I have a created an Excel file "test_file.xlsx" and saved it with the password "123". I do know the contents in there and I want to manipulate them via Python. These are the main sources I have consulted but non of them have worked for me:
From password-protected Excel file to pandas DataFrame
https://thenethawks.com/python-from-password-protected-excel-file-to-pandas-dataframe/
I'm working with Jupyter Notes (Anaconda):
import win32com.client
import csv
import sys
import pandas as pd
from tempfile import NamedTemporaryFile
xlApp = win32com.client.Dispatch("Excel.Application")
filename, password = r"test_file.xslx", "123"
# Note this line from the question posted
xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
xlws = xlwb.Sheets(1) # index is from 1
print (xlws.Name)
print (xlws.Cells(1, 1)) # if you need cell values
f = NamedTemporaryFile(delete=False, suffix='.csv')
f.close()
os.unlink(f.name)
xlCSVWindows = 0x17 # CSV file format, from enum XlFileFormat
xlwb.SaveAs(Filename=f.name, FileFormat=xlCSVWindows) # Save as CSV
df = pd.read_csv(f.name)
print(df.head())
df.to_csv("myoutput.csv",index=False)
This is the code I have copied and adapted so far, however when I run it, it displays:
---------------------------------------------------------------------------
com_error Traceback (most recent call last)
<ipython-input-38-a529ec4251c6> in <module>
31
32 # Note this line from the question posted
---> 33 xlwb = xlApp.Workbooks.Open(filename, False, True, None, password)
34
35 xlws = xlwb.Sheets(1) # index is from 1
~\AppData\Local\Temp\gen_py\3.8\00020813-0000-0000-C000-000000000046x0x1x9.py in Open(self, Filename, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
41187 , Editable=defaultNamedOptArg, Notify=defaultNamedOptArg, Converter=defaultNamedOptArg, AddToMru=defaultNamedOptArg, Local=defaultNamedOptArg
41188 , CorruptLoad=defaultNamedOptArg):
> 41189 ret = self._oleobj_.InvokeTypes(1923, LCID, 1, (13, 0), ((8, 1), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17), (12, 17)),Filename
41190 , UpdateLinks, ReadOnly, Format, Password, WriteResPassword
41191 , IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify
com_error: (-2147352567, 'Ocurrió una excepción.', (0, 'Microsoft Excel', 'Lo sentimos, no hemos encontrado test_file.xlsx. ¿Puede ser que lo haya movido, eliminado o le hayas cambiado el nombre?', 'xlmain11.chm', 0, -2146827284), None)
The last line in spanish says "Sorry, we couldn't find test_file.xlsx. Could it be that I have moved, deleted or renamed it?"
