feat: Add Job "Data population from Excel file"

[nkallergis]

Add a Nautobot Job that enables users to import data from an Excel file using Design Builder as the backend mechanism (Environment.implement_design). The advantages of this approach include:

• Leveraging the existing capabilities of Design Builder.
• Extending Nautobot's CSV import capabilities to include updates.
• Using the same syntax as CSV imports and simple Design Builder designs without Jinja2.
• Very simple data import process for non-advanced users of Design Builder.
• Implicit validation of data using Design Builder's superpowers.

Job description

The Job itself is pretty self-explanatory and just has the following two fields:

• Excel file (ObjectVar): load the Excel file.
• Debug (BooleanVar): enable detailed logs including the loaded data and the "rendered" designs to implement.

How to use the Excel file

Index sheet

The Index sheet is what "drives" the whole thing.

sheet_name	model	keys	json_fields
VRFs	vrfs	name,namespace__name
Manufacturers	manufacturers	name
Device Types	device_types	model,manufacturer__name
Location Types	location_types	name
Locations	locations	name,location_type__name
Devices	devices	name,location__name	local_config_context_data
Interfaces	interfaces	name,devicename,devicelocation__name
IP Addresses	ip_addresses	address,parentnamespacename
Primary IP Addresses	devices	name
Cables	cables
Dynamic Groups	dynamic_groups	name	filter
Dynamic Group Memberships	dynamic_group_memberships	group__name,parent_group__name

Index contains the following columns:

• sheet_name which is the name of the Excel sheet to import data from.
• model which is the model that we're creating a design for (e.g. devices:).
• keys which is a list of columns that will be prepended with !create_or_update:.
• json_fields which is a list of columns that contain JSON-like content which should be JSON-loaded in the code.

Data sheets

Sheets containing data should be split per-model i.e. one sheet for Devices, one for Interfaces and so on. The column naming rules follow the same convention as Design Builder and Nautobot's CSV import functionality.

name	location_type__name	description	status__name
dublin-dc	Datacenter	Dublin datacenter	Active
dublin-pop	POP	Dublin Point of Presence	Active
orlando-dc	Datacenter	Orlando datacenter	Active
orlando-pop	POP	Orlando Point of Presence	Active
bangkok-dc	Datacenter	Bangkok datacenter	Active
bangkok-pop	POP	Bangkok Point of Presence	Active

• Simple attributes are named after the respective model's attributes (e.g. name).
• Foreign key queries leverage the double under notation (e.g. status__name).
• Columns named content_type or content_types are handled in the Job code so that the generated design has the correct syntax.

Special cases

Currently there are a couple of "special cases" handled explicitly in the code. These are mostly around M2M relationships and under investigation on how these could be handled in a more generalizable manner.

• Primary IP Addresses: these are rendered as a Devices model design with the Job function _handle_primary_ip_addresses doing the necessary transformations.
• Cables: similar to the above, the function _handle_cables does the dirty work of transforming the structure to a Design Builder friendly format that leverages the LookupExtension for creating the cables. Note that this is one of the most problematic pieces of the code as it is not idempotent.

Todo

• [ ] Make M2M handlers more generic.
• [ ] Make Cables idempotent.
• [ ] Improve handling of content_type(s) (unique entry or list).
• [ ] Provide example(s) for custom models from plugins/apps.
• [ ] Add a "Dry-run" toggle to the Job.
• [ ] Add tests!
• [ ] Explore using lightweight Pandas alternatives.
• [ ] Move pretty_json function to utils or helpers.
• [ ] Implement an overall atomic transaction toggle.
• [ ] Mirror the partial results to the main Job Result (e.g. make the Job Result status "FAILURE" when a partial import fails).

A sample Excel file is also included in the PR and may be found in examples_v2/excel_files/coolkitt_data_import.xlsx.

The PR comes with the following extra dependencies for reading and processing Excel files:

• openpyxl for reading the Excel file.
• pandas for processing it.
• numpy as a Pandas dependency.

[glennmatthews]

Extending Nautobot's CSV import capabilities to all models, not just the supported core models.

Can you elaborate on what's missing from core in this regard?

[nkallergis]

Extending Nautobot's CSV import capabilities to all models, not just the supported core models.

Can you elaborate on what's missing from core in this regard?

@glennmatthews apparently what's actually missing is some better understanding on my side as I was under the impression that Nautobot didn't support CSV imports for non-core models. Just realized that this is not true at all 😕

Nevertheless, due to this being based on Design Builder, there are a couple of other advantages such as:

• Update functionality
• A (still very ugly) way to handle M2M relationships using constructs like {"!get:name": "ABC123"}

Very open to discussing this PR further if you feel it duplicates core team efforts and should be handled differently!

[glennmatthews]

We definitely want to add the ability to do updates via CSV in core, it just hasn't bubbled up to the top of the to-do list yet. Same for being able to clearly/conveniently describe M2Ms on the related model CSV (for now at least, our guidance at this point is use a separate CSV file to populate the through table). I haven't looked at your code here but if there's any chance it'd make sense to port any of it into core we'd definitely be interested in taking a look. :-)

[chadell]

This PR is tricky because it solves a different problem than what Design Builder is for (i.e., using design to create/update data). In this case, you are providing the data in a specific format to transform data into the source of truth, using the interfaces that Design Builder offers (smart one, BTW).

Being a lonely job, it may be hosted in this project (or an extension of it), but I think that the problem that you are trying to solve should be solved in Core eventually. Actually, there is a similar case with the data protection features I developed in this project, that I hope can be offloaded to Core at some point.

I'm a bit concerned about confusing the users of design builder with this new Job. Maybe we could leave it as a side App (depending on this) with this job, while we sync with Core to make it a killing new feature?

[nkallergis]

@chadell agreed on all points made. For the moment I'll leave it as is and we can sync on possible paths to porting this into core. As for your question @glennmatthews, the parts that can be salvaged are mostly around "reading" the Excel sheets but, depending on how we'll approach updates and M2Ms, could easily prove to be the weakest parts too.

[nkallergis]

Closing the PR based on the discussion above. Tomorrow I will "move" it to https://github.com/nautobot/demo-designs as per the recommendation from @abates.