mkesicki/excel_validator

Python script to validate data in Excel files

Excel Validator

Excel Validator is used to validate different constraints of excel column through a Validation rules stored in YAML files.

constraints to be checked : - Base Validator - Choice Validator - Condition Validator - Country Validator - DateTime Validator - Email Validator - Excel Date Validator - Length Validator - NotBlank Validator - Regex Validator - Type Validator

Requirements

1. Python 3
2. All necessary python libraries are listed in requirements.txt

Script parameters

$ python excel_validator.py  -h
usage: excel_validator.py [-h] [--errors errors] config file sheetName tmpDir

Mark validation errors in Excel sheet.

positional arguments:
  config           Path to YAML config file
  file             Path to excel sheet file
  sheetName        Excel Sheet Name
  tmpDir           Temporary directory path

optional arguments:
  -h, --help           Print help description
  --errors             Print errors messages without generating excel file with errors
  --no-file-size-limit Switch off file size limit. Use with care

Example usage

usage for example/excel.xlsx file

/PATH/excel_validator>python excel_validator.py "example/example.yml" /PATH/excel.xlsx "Example" "/tmp" --errors=true
Get validation config example/example.yml
Validate Excel Sheet Example
Parse Excel file
Processing |################################| 5/5
Found 22 error(s)
Processing |#                               | 1/22Broken Excel cell: C2
Processing |##                              | 2/22Broken Excel cell: F2
Processing |####                            | 3/22Broken Excel cell: H2
Processing |#####                           | 4/22Broken Excel cell: M2
Processing |#######                         | 5/22Broken Excel cell: A3
Processing |########                        | 6/22Broken Excel cell: D3
Processing |##########                      | 7/22Broken Excel cell: F3
Processing |###########                     | 8/22Broken Excel cell: G3
Processing |#############                   | 9/22Broken Excel cell: H3
Processing |##############                  | 10/22Broken Excel cell: I3
Processing |################                | 11/22Broken Excel cell: K3
Processing |#################               | 12/22Broken Excel cell: M3
Processing |##################              | 13/22Broken Excel cell: A4
Processing |####################            | 14/22Broken Excel cell: E4
Processing |#####################           | 15/22Broken Excel cell: F4
Processing |#######################         | 16/22Broken Excel cell: H4
Processing |########################        | 17/22Broken Excel cell: K4
Processing |##########################      | 18/22Broken Excel cell: M4
Processing |###########################     | 19/22Broken Excel cell: A5
Processing |#############################   | 20/22Broken Excel cell: B5
Processing |##############################  | 21/22Broken Excel cell: J5
Processing |################################| 22/22Broken Excel cell: M5

[[Save file: /tmp/errors_2017-04-25_1493102119_excel.xlsx]]
Validation errors store in: [[/tmp/errors_2017-04-25_1493102119_excel.xlsx]]

/PATH/excel_validator>pause
Press any key to continue . . .

Windows user bonus

validate.bat contains example usage (same as in this documentation) of script. In Windows you can Drag & Drop Excel file on validate.bat script and it should execute validation. Of course you should change content of this file according to your needs.

excel_validator's Issues

Cleanup code - looks like it was write late at nights

Add logger for messages & output

Excludes columns are still validated

Exclude columns are still validated if the columns have blanks though it states that #default validator is used for not excluded columns for which no other validation rule is set
Code: excludes: ["L","M"]

Only if I remove the below code, the column validations excluded

default:
- NotBlank:
message: Value can not be blank

Name/Type of validator is set wrong if column has more than one validator

It is always set for first validator for column example:

        C:
            - Type:
                type: integer
                message: "Message"
                trim: true
                
        - Conditional:
                message: "Message"
                fieldB: "C"
                operator: "lambda c, d: (False if (c > d) else True)"
                trim: true 
        D:
            - Type:
                type: integer
                message: "Message"
                trim: true

in this case conditional validator will be not set

excel script

Add possibility to use more than 1 defualt validator

For now only one default validator can be used.

Python3

Dear,
I was testing your script but it seems to fail with python 3.
Dict variables are not supported.
I'm getting errors on type = settings.keys()[0]

Add tests

Use https://docs.python.org/2/library/unittest.html or something similar.

Addressing error file size limitation

excel_validator/excel_validator.py

Line 282 in f1ef759

sys.exit("Invalid file is too big to generate annotated Excel file")

Hi,

I think this is a great tool, but there seems to be a limitation regarding the binary size of the error Excel file.

If I try to run against an input file that is between 76 -- 100 megabits Excel file, I receive an error saying that the output error file is too large and thus cannot be created.

As a user (with sufficient resource) my expectation is for a file to be generated.
Does anyone more versed in the code of the solution and/or supporting libraries provide me with enlightenment on how to bypass this situation?

Thank you.

Add possibility to just print errors , do not create excel files with errors

--dry-run ?