Excessive indirect references in NAME formula

user_7122438 0
Viewed 273

I am trying to read in an 'xls' files in python using pandas. My code basically is a one-liner:

import pandas as pd
df = pd.read_excel(str("/test/test_file.xls"))

This code works for the majority of the files, but there are cases when it fails with the error:

Excessive indirect references in NAME formula

What I tried so far:

  1. Tried changing the stack limit(panic and warning) to as far as 10000 in the Pandas package itself, where the exception was occurring. A recursion limit was encountered, so raised it as far as 125000, which led to my Mac/Python reaching its limit so I am guessing not the right solution.

  2. Used a memory-intensive EMR to see if it can read the file - nope.

  3. Looked at the GitHub repo for XLRD here to raise a bug only to find out it's out of support.

  4. Opened the file, saved it as an xlsx, used the same code to read it into a dataframe. Worked like a charm.

  5. Tried using Spark Excel Library to read in a particular section of the data - this worked too but I need to use pandas.

  6. Googled it only to find out the results would show me the XLRD code where the exception is defined. Not one person has reported it.

  7. Tried using Python2 and Python3 with the latest and older versions of Pandas - no use.

I cannot share the file, but has anyone faced this issue before? Can someone help? All suggestions are welcome!

pandas xlrd excel python
2 Answers

Try the following:

  1. Open the xls file
  2. Copy/paste all cells as values
  3. Rerun your script

Hard to help further without having access to the file to explain exactly what is happening.

But chances are xlrd is trying to resolve the value of a formula and is exceeding the "STACK_PANIC_LEVEL". Without seeing the formula, very difficult to say more.

user_22635720

xlrd has a method of evaluate_name_formula(). When you try to open a .xls file with xlrd, it will raise an error (as you described) if your file has many user-defined formulas. To try to solve your problem, I think you can delete these user-defined formulas and keep the file free of these formulas. Or you can try to edit xlrd code, and prevent it from raising the Error, which seems much more difficult.

user_150999180
Related