Excel cell checker with python

Overview

excel-cell-checker

Description

This tool checks a given .xlsx file has the structure specified in a .json file.

Requirements

Python 3 is required, at least 3.7. The required modules can be installed with:

$ pip install -r requirements.txt

Usage

First, you must create a .json file containing the structure of your excel file. The root of the structure is an array with the key "cols":

{
  "cols" : [
    {
      "name" : "id",
      "type" : "string",
      "regex" : "[0-9]{5}",
      "non-null" : true
    },
    {
      "name" : "first_name",
      "type" : "string"
    },
    {
      "name" : "age",
      "type" : "number"
    }
  ]
}

The elements of the cols array are the columns of your excel file, aswell as their respective data type. The currently supported data types are string, number and date.

The tool can also optionally check the content of cells, but right now this feature is limited to regular expressions for string columns.

Run checker.py and supply a .xlsx file aswell as a .json structure file:

$ py checker.py 
    
    

    
   

If you want to check a specific sheet in your excel file, supply the sheet name using -s .

First, the tool will check if the excel file contains the same rows as specified in the .json structure (it is assumed, that the first row contains column names and all remaining rows contain data). If this is succesful, each cells type (and content) will be examined. If you don't want a column to be checked, you can specifiy skip in your structure file:

{
  "name" : "useless"
  "type" : "string"
  "skip" : true
}

After examining the excel sheet, a summary of all found violations is printed. This summary can be modified by the following parameters:

  • --hide-skipped Hides skipped columns
  • --hide-ok Hides columns with no violations

Examples

Example source files can be found in the examples directory.

Running the tools on these files should yield:

$ py .\checker.py .\examples\example.xlsx .\examples\structure.json
Loading structure file structure.json ..
Loading excel file example.xlsx ..
Loaded file with 5 data rows.
Checking basic column structure ..   Done!
Checking row 5 of 5 ..
Done!

> id
[ERROR] : 2 violations found

  The following cells did not match the regular expression:

      Row  Value
    -----  -------
        5  '42'

  The following cells are empty, even though non-null is set to true:

      Row
    -----
        4

> first_name
[OK] : No violations found

> age
[ERROR] : 1 violations found
  The following cells did not match the expected type (number) :

      Row  Value    Type
    -----  -------  ------
        6  '17'     str



> useless
[SKIPPED]
Owner
Paul Aumann
Paul Aumann
Team Hash Brown Science4Cast Submission

Team Hash Brown Science4Cast Submission This code reproduces Team Hash Brown's (@princengoc, @Xieyangxinyu) best submission (ee5a) for the competition

3 Feb 02, 2022
Cup Noodle Vending Maching Ordering Queue

Noodle-API Cup Noodle Vending Machine Ordering Queue Install dependencies in virtual environment python3 -m venv

Jonas Kazlauskas 1 Dec 09, 2021
Performance monitoring and testing of OpenStack

Browbeat Browbeat is a performance tuning and analysis tool for OpenStack. Browbeat is free, Open Source software. Analyze and tune your Cloud for opt

cloud-bulldozer 83 Dec 14, 2022
A small Blender addon for changing an object's local orientation while in edit mode

A small Blender addon for changing an object's local orientation while in edit mode.

Jonathan Lampel 50 Jan 06, 2023
creates a batch file that uses adb to auto-install apks into the Windows Subsystem for Android and registers it as the default application to open apks.

wsa-apktool creates a batch file that uses adb to auto-install apks into the Windows Subsystem for Android and registers it as the default application

Aditya Vikram 3 Apr 05, 2022
通过简单的卷积神经网络直接预测出验证码图片中滑块的位置

使用说明 1. 在本地测试 运行python3 prdict_one.py即可,默认需要预测的图片路径位于testImg文件夹下的test1.png 运行python3 predict_folder.py预测testImg下的所有图片 2. 部署到服务器 运行python3 run_a_server

12 Mar 08, 2022
Parametric Bottle in CADQuery

Parametric Bottle using CADQuery The proposed code makes it possible to generate different types and sizes of 3D bottles in order to train Pixel2mesh

Ayoub EL HOUDRI 1 May 22, 2022
Mengzhan (John) code for Closed Loop Control system of Sharp Wave Ripples in Hippocampus CA3 region

ClosedLoopControl_Yu Mengzhan (John) code for Closed Loop Control system of Sharp Wave Ripples in Hippocampus CA3 region Creating Python Virtual Envir

Mengzhan (John) Liufu 1 Jan 22, 2022
pyForgeCert is a Python equivalent of the original ForgeCert written in C#.

pyForgeCert is a Python equivalent of the original ForgeCert written in C#.

Evi1cg 47 Oct 08, 2022
A TODO-list tool written in Python

PyTD A TODO-list tool written in Python. Its goal is to provide a stable posibility to get a good view over all your TODOs motivate you to actually fi

1 Feb 12, 2022
Python Excuse Generator

Excuse Generator Python Excuse Generator This project is an excuse generator that provides the user with an excuse as to why they weren't paying atten

Collin Sanders 5 Jul 07, 2022
Intelligent Employer Profiling Platform.

Intelligent Employer Profiling Platform Setup Instructions Generating Model Data Ensure that Python 3.9+ and pip is installed. Install project depende

Harvey Donnelly 2 Jan 09, 2022
Python bilgilerimi eğlenceli bir şekilde hatırlamak ve daha da geliştirmek için The Big Book of Small Python Projects isimli bir kitap almıştım.

Python bilgilerimi eğlenceli bir şekilde hatırlamak ve daha da geliştirmek için The Big Book of Small Python Projects isimli bir kitap almıştım. Bu repo kitaptaki örnek programları çalıştığım oyun al

Burak Selim Senyurt 22 Oct 26, 2022
Drop-down terminal for GNOME

Guake 3 README Introduction Guake is a python based dropdown terminal made for the GNOME desktop environment. Guake's style of window is based on an F

Guake 4.1k Dec 25, 2022
Hexa is an advanced browser.It can carry out all the functions present in a browser.

Hexa is an advanced browser.It can carry out all the functions present in a browser.It is coded in the language Python using the modules PyQt5 and sys mainly.It is gonna get developed more in the fut

1 Dec 10, 2021
A New, Interactive Approach to Learning Python

This is the repository for The Python Workshop, published by Packt. It contains all the supporting project files necessary to work through the course from start to finish.

Packt Workshops 231 Dec 26, 2022
This is friendlist update tools & old idz clon & follower idz clon etc

This is friendlist update tools & old idz clon & follower idz clon etc

MAHADI HASAN AFRIDI 1 Jan 15, 2022
Calc.py - A powerful Python REPL calculator

Calc - A powerful Python REPL calculator This is a calculator with a complex sou

Alejandro 8 Oct 22, 2022
Notes on the Deep Learning book from Ian Goodfellow, Yoshua Bengio and Aaron Courville (2016)

The Deep Learning Book - Goodfellow, I., Bengio, Y., and Courville, A. (2016) This content is part of a series following the chapter 2 on linear algeb

hadrienj 1.7k Jan 07, 2023
Procedural 3D data generation pipeline for architecture

Synthetic Dataset Generator Authors: Stanislava Fedorova Alberto Tono Meher Shashwat Nigam Jiayao Zhang Amirhossein Ahmadnia Cecilia bolognesi Dominik

Computational Design Institute 49 Nov 25, 2022