Excel to YAML converter

Build Status

Script provides a mechanism to extract information from any Excel sheet and create a text file with any structure. Transformation is based by using

Description

Repository introduced a way to convert data from an Excel file to YAML structure. It is convinient to share information with customers / team to collect all data. Once this process is over, python script will extract information from this excel file and create YAML outputs that can be used by ansible.

Python Excel Reader

Installation

To start working with this script, you have to installa dependencies with following command:

$ pip install -r requirements.txt
$ python bin/inetsix-excel-to-template -h

Or you can use pip to install script in your $PATH

$ pip install git+https://github.com/titom73/python-excel-serializer.git

Description

Script name: bin/inetsix-excel-to-template

Supported features:

  • Local file fetching.
  • Remote file fetching using HTTP or HTTPS.
  • Table with N columns.
  • List of items.
  • Jinja2 engine to render file in any text format.

python bin/inetsix-excel-to-template -h
usage: tools.python.read.excel.py [-h] [-e EXCEL] [-u URL] [-s SHEET]
                                  [-m MODE] [-n NB_COLUMNS] [-t TEMPLATE]
                                  [-o OUTPUT] [-v]

Excel to text file

optional arguments:
  -h, --help            show this help message and exit
  -e EXCEL, --excel EXCEL
                        Input Excel file
  -u URL, --url URL     URL for remote Excel file
  -s SHEET, --sheet SHEET
                        Excel sheet tab
  -m MODE, --mode MODE  Serializer mode: table / list
  -n NB_COLUMNS, --nb_columns NB_COLUMNS
                        Number of columns part of the table
  -t TEMPLATE, --template TEMPLATE
                        Template file to render
  -o OUTPUT, --output OUTPUT
                        Output file
  -v, --verbose         Increase verbositoy for debug purpose

This script reads a sheet (-s) in an excel file (-e) and extract data into the following python structure. If file is stored on a HTTP/HTTPS server, -u option will download it directly and will replace -e option.

SHEET_NAME:
  - COLUMN#1: value ROW#1 / COLUMN#1
    COLUMN#2: value ROW#1 / COLUMN#2
    ...
  - COLUMN#1: value ROW#2 / COLUMN#1
    COLUMN#2: value ROW#2 / COLUMN#2
    ...

Since this structure is yaml compliant, script opens a template (-t) based on jinja2 format to render output file (-o)

Excel data structure description

Table representation:

A table is a two dimension represented like this:

Local Device Local Port Local Port Name Remote Port Name Remote Port Remote device
poc-qfx5110-169 port1 et-0/0/0 et-0/0/0 port1 poc-qfx5110-172
poc-qfx5110-169 port1 et-0/0/1 et-0/0/0 port1 poc-qfx5110-174

In this case, every column name is a key name. In term of Python representation, structure built is like this where topology is the name of the Excel sheet:

topology:
- {id: '1', local_device: demo-qfx10k2-14, local_port: port1, local_port_name: et-0/0/0,
  remote_device: demo-qfx5110-11, remote_port: port1, remote_port_name: et-0/0/0}
- {id: '2', local_device: demo-qfx10k2-14, local_port: port2, local_port_name: et-0/0/1,
  remote_device: demo-qfx5110-12, remote_port: port1, remote_port_name: et-0/0/0}

List representation:

A list is a structure where every row is a key:

Key Data
backup_destination 1.1.1.0/24 1.1.2.0/24
backup_router 2.2.2.2
domain_name lab.inetsix.net
dual_re true

In this case, output is like this:

global:
  backup_destination: 1.1.1.0/24 1.1.2.0/24
  backup_router: 2.2.2.2
  domain_name: lab.inetsix.net
  dual_re: 'true'

Usage

Below is an output example with -v enable:

python python-tools/tools.python.read.excel.py \
			-e examples/topology.xlsx\
			-s Topology \
			-t examples/topology.j2 \
			-o examples/output.txt -v

			
 * Reading excel file: examples/topology.xlsx
 ** Debug Output **
topology:
- {id: '1', local_device: dev1, local_port: port1, local_port_name: et-0/0/0, remote_device: dev2,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '2', local_device: dev1, local_port: port2, local_port_name: et-0/0/1, remote_device: dev3,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '3', local_device: dev1, local_port: port3, local_port_name: et-0/0/2, remote_device: dev4,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '4', local_device: dev1, local_port: port4, local_port_name: et-0/0/3, remote_device: dev5,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '5', local_device: dev1, local_port: port5, local_port_name: et-0/0/4, remote_device: dev6,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '6', local_device: dev1, local_port: port6, local_port_name: et-0/0/5, remote_device: dev7,
  remote_port: port1, remote_port_name: et-0/0/0}
- {id: '7', local_device: dev1, local_port: port7, local_port_name: et-0/0/6, remote_device: dev8,
  remote_port: port1, remote_port_name: et-0/0/0}

 * Template rendering
    > Use template: examples/topology.j2
    > Output file: examples/output.txt

Example use case

Excel Inputs

Assuming table is the following:

Local Device Local Port Local Port Name Remote Port Name Remote Port Remote device
poc-qfx5110-169 port1 et-0/0/0 et-0/0/0 port1 poc-qfx5110-172
poc-qfx5110-169 port1 et-0/0/1 et-0/0/0 port1 poc-qfx5110-174

Template for rendering

topo:
{%- for tester in topology %}
{%- if loop.previtem is not defined or tester.local_device != loop.previtem.local_device %}
  {{tester.local_device}}:
{%- for link in topology %}
{%- if tester.local_device == link.local_device %}
    {{link.local_port}}: { name: {{link.local_port_name}},    peer: {{link.remote_device}},      pport: {{link.remote_port}},     type: ebgp, link: {{link.id}},   linkend: 1 }
{%- endif %}
{%- endfor %}
{%- endif %}

{%- endfor %}
{%- for tester in topology|sort(attribute='remote_device') %}
{%- if loop.previtem is not defined or tester.remote_device != loop.previtem.remote_device %}
  {{tester.remote_device}}:
{%- for link in topology|sort(attribute='remote_device') %}
{%- if tester.remote_device == link.remote_device %}
    {{link.remote_port}}: { name: {{link.remote_port_name}},    peer: {{link.local_device}},      pport: {{link.local_port}},     type: ebgp, link: {{link.id}},   linkend: 2 }
{%- endif %}
{%- endfor %}
{%- endif %}

{%- endfor %}

Output rendered

Output rendering is similar to:

topo:
  poc-qfx5110-169:
    port1: { name: et-0/0/0,    peer: poc-qfx5110-172,      pport: port1,     type: ebgp, link: 1,   linkend: 1 }
    port2: { name: et-0/0/1,    peer: poc-qfx5110-174,      pport: port1,     type: ebgp, link: 2,   linkend: 1 }
    port3: { name: et-0/0/2,    peer: poc-qfx5110-175,      pport: port1,     type: ebgp, link: 3,   linkend: 1 }
    port4: { name: et-0/0/3,    peer: poc-qfx5110-188,      pport: port1,     type: ebgp, link: 4,   linkend: 1 }
    port5: { name: et-0/0/4,    peer: poc-qfx5110-189,      pport: port1,     type: ebgp, link: 5,   linkend: 1 }

...

  poc-qfx5110-172:
    port1: { name: et-0/0/0,    peer: poc-qfx5110-169,      pport: port1,     type: ebgp, link: 1,   linkend: 2 }
    port2: { name: et-0/0/1,    peer: poc-qfx5110-170,      pport: port1,     type: ebgp, link: 6,   linkend: 2 }
    port3: { name: et-0/0/2,    peer: poc-qfx5110-171,      pport: port1,     type: ebgp, link: 11,   linkend: 2 }

...

GitHub

View Github