27

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

Tom_G_99
  • 461
  • 1
  • 5
  • 13
  • what is in the excel workbook? Are there any conditional formatting? – AlwaysData Dec 29 '18 at 00:16
  • the excel workbook is an object from the openpyxl module. I am getting my information from here: https://automatetheboringstuff.com/chapter12/ – Tom_G_99 Dec 29 '18 at 00: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? – AlwaysData Dec 29 '18 at 00:23
  • Does this warning prevent you from doing what you want with the data in the sheet? – cody Dec 29 '18 at 00: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 – Tom_G_99 Dec 29 '18 at 00:29
  • I created a test work book and ran the code slighly modified and didn't see any warnings. Where do you see the error? Could you past a screenshot? Is it in Excel or your IDE? – AlwaysData Dec 29 '18 at 00:32
  • I'm having this issue as well. It seems to be unique to the workbook I'm using. I'm guessing there's some object in the excel document that is triggering this warning from Openpyxl. Problem is for me that it also generates an error when I open excel that the file has been corrupted and has to be recovered. Would be glad if there's a way to better understand what is triggering this warning. – Hofbr Aug 19 '20 at 16:53
  • Warnings can be suppressed: https://stackoverflow.com/questions/53965596/python-3-openpyxl-userwarning-data-validation-extension-not-supported#comment114175277_53965816 – Allan Lewis Oct 28 '20 at 11:10

5 Answers5

24

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.

ZenPhil
  • 341
  • 2
  • 3
  • 10
    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. – S3DEV Jun 09 '22 at 11:30
20

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.

S3DEV
  • 8,768
  • 3
  • 31
  • 42
4

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
Mike T
  • 41,085
  • 18
  • 152
  • 203
  • 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. (https://docs.python.org/3/library/warnings.html#warnings.catch_warnings) – Arigion Aug 17 '23 at 14:26
0

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.

AlwaysData
  • 540
  • 3
  • 8
  • 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. – Tom_G_99 Dec 29 '18 at 01:03
  • 1
    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. – AlwaysData Dec 29 '18 at 01:05
  • 1
    Is there a way to suppress this warning? I just want to read the data, and dont care for the data validation aspect. – 9a3eedi Sep 27 '20 at 06:55
  • 4
    @9a3eedi You can suppress it using the `warnings` module: https://docs.python.org/3/library/warnings.html – Allan Lewis Oct 28 '20 at 11:09
0

This error is not matter, cause you don't save your workbook in the end code: something like: wb.save("Grantfundme Master London.xlsx")

AlexetyZ
  • 1