40

So this is my first time that I'm attempting to read from an Excel file and I'm trying to do so with the openpyxl module. My aim is to collate a dictionary with a nested list as its value. However, when I get this warning when I try to run it:

UserWarning: Data Validation extension is not supported and will be removed warn(msg)

I don't know where I'm going wrong. Any help would be much appreciated. Thanks

import openpyxl try: wb = openpyxl.load_workbook("Grantfundme Master London.xlsx") except FileNotFoundError: print("File could not be found.") sheet = wb["FUNDS"] database = {} for i in range(250):#this is the number of keys I want in my dictionary so loop through rows charity = sheet.cell(row=i + 1, column=1).value area_of_work = [] org = [] funding = sheet.cell(row=i + 1, column=14).value for x in range(8, 13): # this loops through columns with info I need if sheet.cell(row=i +1, column=x).value !="": area_of_work.append(sheet.cell(row=i +1, column=x).value) for y in range(3, 6): # another column loop if sheet.cell(row=i +1, column=y).value !="": org.append(sheet.cell(row=i +1, column=y).value) database[charity] = [area_of_work,org, funding] try: f = open("database.txt", "w") f.close() except IOError: print("Ooops. It hasn't written to the file")

For those asking here is a screenshot of the exception: (Data Validation Expcetion

Improve this question
9
  • what is in the excel workbook? Are there any conditional formatting? Commented Dec 29, 2018 at 0:16
  • the excel workbook is an object from the openpyxl module. I am getting my information from here: automatetheboringstuff.com/chapter12 Commented Dec 29, 2018 at 0:18
  • yes, I understand that. I was refereing to the Grantfundme Master London.xlsx workbook. What type of data is in that workbook? Also, one other question. Where do you see this error? Commented Dec 29, 2018 at 0:23
  • Does this warning prevent you from doing what you want with the data in the sheet? Commented Dec 29, 2018 at 0:27
  • Oh I misread, my mistake. In the workbook, there are just strings with the names of charities, their areas of work, what type of organisation they are and funding they are receiving. I don't think there is any conditional formatting on the sheet that I selected. The error doesn't reference any line so I don't know where I'm going wrong Commented Dec 29, 2018 at 0:29

5 Answers 5

Reset to default
35

Sometimes simply clearing the Data Validation rules in the Workbook is not a viable solution - perhaps other users rely on the rules, or maybe they are locked for editing, etc.

The error can be ignored using a simple filter, and the Workbook can remain untouched, as:

import warnings warnings.simplefilter(action='ignore', category=UserWarning)

In practice, this might look like:

import pandas as pd import warnings def load_data(path: str): """Load data from an Excel file.""" warnings.simplefilter(action='ignore', category=UserWarning) return pd.read_excel(path)

Note: Just remember to reset warnings, else all other UserWarnings will be ignored as well.

Ignore a specific module:

Per the documentation the filterwarnings method has a module argument which can be used to filter against a specific module.

For example:

warnings.filterwarnings('ignore', category=UserWarning, module='openpyxl')

Thanks to @Hendy for this suggested update, based on this duplicate.

Context manager:

Using a warnings context manger is a clean approach ignoring and resetting warnings.

For example:

with warnings.catch_warnings(action='ignore', category=UserWarning): # Function call which triggers the warning. pd.read_excel(path)

Per the documentation (italics mine):

A context manager that copies and, upon exit, restores the warnings filter and the showwarning() function.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

This would ignore all UserWarnings. Not good
You could improve this by using a context manager, which would allow you to temporarily disable warnings while reading the excel file, and reliably reenable them
How could I know that I don't miss any other UserWarnings from openpyxl that would actually be helpful?
@törzsmókus - Using the context manager (the second example) would be your best bet. Once that context (block) is exited, warnings are re-enabled; so only wrap the statement(s) which are expected to trigger warnings which should be ignored. However, if you’re concerned about missing other warnings, this suggests more testing is required.
I get this, but at what point is it weird that openpyxl only complains via warnings, which users then research how to silence vs. giving options to address the issue directly? Like... load_workbook(..., ignore_data_validations=True). The same applies to warnings about no default style/engine (can't recall). SO is filled with instructions to just silence the warning, which IMO isn't the right way. Yes, I can file a github issue, too, I just also like to complain to the internet.
31

Excel has a feature called Data Validation (in the Data Tools section of the Data tab in my version) where you can pick from a list of rules to limit the type of data that can be entered in a cell. This is sometimes used to create dropdown lists in Excel. This warning is telling you that this feature is not supported by openpyxl, and those rules will not be enforced. If you want the warning to go away, you can click on the Data Validation icon in Excel, then click the Clear All button to remove all data validation rules and save your workbook.

Improve this answer

1 Comment

Nice explanation, however this is not a viable solution for some. What if the sheet is used and the data validation rules are required? Simply wiping them is destructive and may affect other users.
13

Use a warnings.catch_warning context manager to temporarily ignore warnings, like this:

import warnings import pandas as pd with warnings.catch_warnings(): warnings.filterwarnings("ignore", category=UserWarning) df = pd.read_excel("file.xlsx") # now warning filter is restored
Improve this answer

1 Comment

Note The catch_warnings manager works by replacing and then later restoring the module’s showwarning() function and internal list of filter specifications. This means the context manager is modifying global state and therefore is not thread-safe. (docs.python.org/3/library/warnings.html#warnings.catch_warnings)
5

To disable the warning, simply try:

openpyxl.reader.excel.warnings.simplefilter(action='ignore')
Improve this answer

Comments

-1

Thanks, for the screenshot! Without seeing the actual excel workbook it's hard to say exactly what it is complaining about.

If you notice the screenshot references line 322 of the reader worksheet module. It looks like it is telling you the data valadation extension to the OOXML standard is not supported by the openpyxl library. It's appears to be saying it found parts of the data valadation extension in your workbook and that will be lost when parsing the workbook with the openpyxl extention.

Improve this answer

4 Comments

I don't understand how that's possible though. In my Excel file the line 322 is empty, in fact the file only has information until row 244.
It references line 322 of the openpyxl.reader.worksheet module not 322 of your workbook. It's the code that is part of the openpyxl library that is reading your workbook.
Is there a way to suppress this warning? I just want to read the data, and dont care for the data validation aspect.
@9a3eedi You can suppress it using the warnings module: docs.python.org/3/library/warnings.html

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.