Python 3 openpyxl UserWarning: Data Validation extension not supported

user_10331017 0
Viewed 38834

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

openpyxl io python
3 Answers

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.

user_57550270

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)
user_63404960

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.

user_74258790
Related