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
An async API wrapper for Dress To Impress written in Python.

dti.py An async API wrapper for Dress To Impress written in Python. Some notes: For the time being, there are no front-facing docs for this beyond doc

Steve C 1 Dec 14, 2022
Penelope Shell Handler

penelope Penelope is an advanced shell handler. Its main aim is to replace netcat as shell catcher during exploiting RCE vulnerabilities. It works on

293 Dec 30, 2022
Pulse sequence builder and compiler for q1asm

q1pulse Pulse sequence builder and compiler for q1asm. q1pulse is a simple library to compile pulse sequence to q1asm, the assembly language of Qblox

Sander de Snoo 3 Dec 14, 2022
An integrated library for checking email if it is registered on social media

An integrated library for checking email if it is registered on social media

Sidra ELEzz 13 Dec 08, 2022
Blender Light Manipulation - A script that makes it easier to work with light

Blender Light Manipulation A script that makes it easier to work with light 1. Wstęp W poniższej dokumentacji przedstawiony zostanie skrypt, który swo

Tomasz 1 Oct 19, 2021
Tc-python - A Python script to receive message from a twitch chat

Twitch-Chat 📜 I did a script in Python to receive messages from a twitch chat.

miyucode 2 May 31, 2022
A tool that bootstraps your dotfiles ⚡️

Dotbot Dotbot makes installing your dotfiles as easy as git clone $url && cd dotfiles && ./install, even on a freshly installed system! Rationale Gett

Anish Athalye 5.9k Jan 07, 2023
Wrapper for the undocumented CodinGame API. Can be used both synchronously and asynchronlously.

codingame API wrapper Pythonic wrapper for the undocumented CodinGame API. Installation Python 3.6 or higher is required. Install codingame with pip:

Takos 19 Jun 20, 2022
We want to check several batch of web URLs (1~100 K) and find the phishing website/URL among them.

We want to check several batch of web URLs (1~100 K) and find the phishing website/URL among them. This module is designed to do the URL/web attestation by using the API from NUS-Phishperida-Project.

3 Dec 28, 2022
LinkML based SPARQL template library and execution engine

sparqlfun LinkML based SPARQL template library and execution engine modularized core library of SPARQL templates generic templates using common vocabs

Linked data Modeling Language 6 Oct 10, 2022
WordPress-style shortcodes for Python

Python Shortcodes WordPress-style shortcodes for Python Create and use WordPress-style shortcodes in your Python based app. Example # static output de

Bob 1 Dec 22, 2021
An app about keyboards, originating from the design of u/Sonnenschirm

keebapp-backend An app about keyboards, originating from the design of u/Sonnenschirm Setup Firstly, ensure that the environment for python is install

8 Sep 04, 2022
Rofi script to minimize / unminimize multiple windows in qtile

Qminimize Rofi script to minimize / unminimize multiple windows in qtile Additional requirements : EWMH module fuzzywuzzy module How to use it : - Clo

9 Sep 18, 2022
A python script that changes your desktop background based on current weather and time of the day.

Desktop background wallpaper, based on current weather and time A python script that changes your computer's desktop background based on current weath

Maj Gaberšček 1 Nov 16, 2021
A carrot-based color palette you didn't know you needed.

A package to produce a carrot-inspired color palette for python/matplotlib. Install: pip install carrotColors Update: pip install --upgrade carrotColo

10 Sep 28, 2021
RCCで開催する『バックエンド勉強会』の資料

RCC バックエンド勉強会 開発環境 Python 3.9 Pipenv 使い方 1. インストール pipenv install 2. アプリケーションを起動 pipenv run start 本コマンドを実行するとlocalhost:8000へアクセスできるようになります。 3. テストを実行

Averak 7 Nov 14, 2021
A code base for python programs the goal is to integrate all the useful and essential functions

Base Dev EN This GitHub will be available in French and English FR Ce GitHub sera disponible en français et en anglais Author License Screen EN 🇬🇧 D

Pikatsuto 1 Mar 07, 2022
Package to provide translation methods for pyramid, and means to reload translations without stopping the application

Package to provide translation methods for pyramid, and means to reload translations without stopping the application

Grzegorz Śliwiński 4 Nov 20, 2022
A simple flashcard app built as a final project for a databases class.

CS2300 Final Project - Flashcard app 'FlashStudy' Tech stack Backend Python (Language) Django (Web framework) SQLite (Database) Frontend HTML/CSS/Java

Christopher Spencer 2 Feb 03, 2022
UniPD exam dates finder

UniPD exam dates finder Find dates for exams at UniPD Usage ./finder.py courses.csv It's suggested to save output to a file: ./finder.py courses.csv

Davide Peressoni 1 Jan 25, 2022