Python XLRD error: formula / tFunc unknown FuncID: 186
I am stumped on this, please help me in flour waste exchangers ...
I have a function that uses xlrd to read in a .xls file, which is a file that my company releases every few months. The file is always in the same format as the updated data. I have not had any problems reading .xls files in the past, but the newest release .xls file is not readable and throws this error:*** formula/tFunc unknown FuncID:186
Things I've tried:
-
I compared the new .xls file with the old one to see if I can spot any differences. No, what I could find.
-
I removed all the macros that the file contained (older versions also had macros)
-
Updated xlrd to version 0.9.3 but get the same error
-
These files are .xlsm files. I open them up and save them as .xls so that xlrd can read them. This has worked very well in previous versions of the file. After upgrading to xlrd 0.9.3, which supposedly supports .xlsx, I tried to save the .xlsm file as.xlsx and tried to read it, but got an error with an error message
Useful information:
- Python 2.7
- xlrd 0.9.3
- Windows 7 (not sure if this matters, but ...)
I'm guessing there is some formula in the new file that xlrd doesn't know how to read. Does anyone know what FuncID: 186 is ?
Edit: still don't know where to go. Does anyone out there run into this? I tried looking for FuncID 186 to check if it is not an excel function, to no avail ...
source to share
In our case, the common cause turned out to be "MySQL Excel COM add-in". See BUG: XLS files saved in Excel 2013 on Windows raise AssertionError # 154 in the python-excel xlrd repo on github .
This add-in placed a number of global formulas (macros?) Into the global tables. By packaging our own xlrd .egg
with DEBUG = 1
installed all over the place, we were able to see:
name: u'LOCAL_DATE_SEPARATOR'
name: u'LOCAL_DAY_FORMAT'
name: u'LOCAL_HOUR_FORMAT'
name: u'LOCAL_MINUTE_FORMAT'
name: u'LOCAL_MONTH_FORMAT'
name: u'LOCAL_MYSQL_DATE_FORMAT'
name: u'LOCAL_SECOND_FORMAT'
name: u'LOCAL_TIME_SEPARATOR'
name: u'LOCAL_YEAR_FORMAT'
... as you parse a workbook that was not there, when others saved the workbook, or when the add-in was disabled.
The OpenOffice "Microsoft Excel File Format Documentation" (pdf) does not include the function identifier 186, but there seems to be various evidence that it is some sort of named function or call.
It seems Yoon Lee's workaround here - adding 186: ('HACKED', 1, 1, 0x02, 1, 'V', 'V'),
to formula.py - will most likely have a side effect of just ignoring all these entries: it seems to be putting the "HACKED ()" formula with a void arg and return type (I might misinterpret it is) in its place. Until an attempt is made to evaluate this formula, it might work, although without understanding a 186
little better, it's hard to say if this could lead to parsing being exposed in some cases.
Will try to revert and update this answer if we have a better understanding of the issue or a safer workaround.
source to share
For now, I just wanted to make sure the xlrd reads perfectly. I hacked my xlrd package to get it loaded.
I disagree with the correctness of the conclusion.
Just add the following line to the formula.py of your pythonlibs / xlrd package.
Around line 240 where each number is a map for a function, create a hacked function here. I put "HACKED" in there. I do not understand what is going on.
- added line starting with 186:
184: ('FACT', 1, 1, 0x02, 1, 'V', 'V'),
186: ('HACKED', 1, 1, 0x02, 1, 'V', 'V'),
189: ('DPRODUCT', 3, 3, 0x02, 3, 'V', 'RRR'),
Here is a discussion of the xlrd group. Essentially, this is a tricky problem that cannot be solved. :)
https://groups.google.com/forum/#!topic/python-excel/ZS5PsC5A6iQ
source to share